VLOOKUP for Numbers

users
#1

Oops, sent this to the original sender, not the list:

Please clarify:

I haven't had any luck figuring out a method to compare each element of a list of numbers to each element of a differently sized list of numbers. What I want would bo something very much like VLOOKUP, but instead of matching strings, it would compare a number using an inequality and then return a value from another column along the row where a match was found.

This is confusing. You say you're looking for an inequality, then return a match from another column along the row where the match was found. Are you looking for matches or not? Do you mean return a value along the row where a match WASN'T found (how would you identify the row where a match isn't found, since that could be all of the rows in the search column)? Or are you saying if VLOOKUP finds some mathematical derivation of the value in the source column (say, the square root), then return the value of the row of THAT match?

What VLOOKUP does is return a value from another column where there IS a match. VLOOKUP works on strings or numbers. Where there IS a match with a matching column, it returns the value from the same row of a separate column in the lookup array. If there's no match, it returns a blank or N/A. If what you want is to identify the places where a match ISN'T found, couldn't you then use an IF statement to test if vlookup returns a value and then return a blank where there's a match and a value (say, 1) if the vlookup doesn't find a value?

Note that vlookup works on different sized lists - the column with numbers that you want to look for in another column doesn't need to be the same length as the matching column. Also, keep in mind that you'll usually need to reference the array list absolutely (use $ before column name and row number). So if you're matching numbers in column A that is 100 numbers long, the lookup array from B1 to C50 would need to be specified as $B$1:$C$50. Otherwise the array range gets adjusted for each row down the search column.

If you want a value from another column returned where there ISN'T a match, you'll have to specify how the spreadsheet would identify that value. A small spreadsheet might help if you show us what you'd LIKE to see returned. But make sure it's pretty small so we can see any patterns easily.

#2

I agree that the original question is a little confusing. What everybody seems to have forgotten in their replies, though, is that if the lookup list is sorted then a non-match will be treated as if it matched the next lower value (for example, looking up 6 in a list of odd numbers will return the value from the row containing 5). If you need to know that this is not an exact match, the value in that row can be compared to the search value.

#3

I don't think anyone has had the chance to forget anything: you have yet to tell us exactly what you are trying to do. All we know is that you want the matching function VLOOKUP() to identify an entry that somehow does not match. There will generally be more than one such, of course, but you have given no idea of which of these - or how many of them - you wish to identify.

If we get to know what you are hoping to achieve, I'm sure there will be people willing to help. I look forward to that!

Brian Barker

#4

OK. Take two. Since VLOOKUP for numbers with an inequality is not meaningful, what I precisely hope to do is this. I hope that the preformatted columns survive the mailer software.

Give the following columns:

A B C D
--- --- --- ---
1.1 eqn 3 0.10
1.3 6 0.12
1.5 10 0.15
1.7 18 0.18
2 30 0.21
2.2 50 0.25
2.7 80 0.30
3.2 120 0.35
3.7 180 0.40
4.3
4.8
5.3
6.4
7.4
8.4

Compare cells in column A such that:

   0 > A1 <= C1
   C1 > A1 <= C2
   C2 > A1 <= C3
   ...

is true. Then return the value of D for the appropriate row. Repeat for all values of A. When done, the length of column B will be equal to the length of column A. B will contain values of column D. Both C and A will always be sorted. (Although it would be nice to be able to do this with A being random numbers.)

It seems like I need an array equation, but the array A is not equal in size to the array in C. Simple string matching of VLOOKUP is inadequate to the task at hand.

Thanks in advance,
Jason C. Wells

#5

OK. Take two. Since VLOOKUP for numbers with an inequality is not meaningful, ...

It will do what you require, I think.

... what I precisely hope to do is this. I hope that the preformatted columns survive the mailer software.

Give the following columns:

A B C D
--- --- --- ---
1.1 eqn 3 0.10
1.3 6 0.12
1.5 10 0.15
1.7 18 0.18
2 30 0.21
2.2 50 0.25
2.7 80 0.30
3.2 120 0.35
3.7 180 0.40
4.3
4.8
5.3
6.4
7.4
8.4

Compare cells in column A such that:

  0 > A1 <= C1
  C1 > A1 <= C2
  C2 > A1 <= C3
  ...

I'm hoping you mean:
   0 < A1 <= C1
   C1 < A1 <= C2
   C2 < A1 <= C3

is true. Then return the value of D for the appropriate row. Repeat for all values of A. When done, the length of column B will be equal to the length of column A. B will contain values of column D. Both C and A will always be sorted. (Although it would be nice to be able to do this with A being random numbers.)

It seems like I need an array equation, but the array A is not equal in size to the array in C. Simple string matching of VLOOKUP is inadequate to the task at hand.

You are still talking of strings with respect to VLOOKUP(), but it will cope with your numbers too! And it will do your job ...

You have missed the important value zero from your column C, so please insert it in C1, moving the other values down. *Do not* move the values in column D, so that the first value, 0.10, is now against zero in column C, 0.12 is against 3, and so on. (It seems that you don't need the maximum value 180 now in C10, in fact.)

In B1, enter:
=VLOOKUP(A1;C$1:D$9;2)
and fill down column B.

Voilà!

This even works with column A values not being sorted, as you have asked. All that is required is that column C should be sorted - and that is no problem, of course.

I trust this helps.

Brian Barker

#6

That works well. I'll check it against real data today. The details of the numerical comparison are hidden from me with that equation but I'll read up on it more now that I now I'm going in the right direction.

I must have had some error a couple days ago because I tried VLOOKUP and couldn't get it to work. Thanks much.

Regards,
Jason C. Wells

#7

I'm sure you'll be able to analyse the formula =VLOOKUP(A1;C$1:D$9;2) - but here goes.

The array C1 to D9 contains your limiting values for the classes and the corresponding values you need to retrieve. This is indicated as a single parameter in the function reference as C1:D9. When we fill the resulting formula down column B, this array reference would be automatically modified to reference different rows; since you don't want this, you need to lock the row numbers, which you do by preceding them with the $ character. This gives C$1:D$9 as the second parameter.

The VLOOKUP() reference takes the value of its first parameter (A1, A2, and so on) and searches for it in the first column (C) of the above array. The third parameter, 2, indicates that the value you want to retrieve is from the second column of the array - column D. The absence of the optional fourth parameter indicates that column C is sorted in numerical order and makes the function select the value next below when there is no exact match.

Brian Barker

#8

Thanks Brian. I have use VLOOKUP but until your example had not realised the implications and usefulness of "select the value next below when there is no exact match"
Steve

#9

I too hadn't realized the value of the last parameter being 1 (meaning it takes the next number below). I had trouble getting the formula to work until I realized I had pasted the array from the email and some of the numbers were formatted as text, others not, so I was getting almost all #N/A. Then I fixed it and it works. Thanks Brian. Excellent little exercise for me to learn more about VLOOKUP.

Carl

#10

That brings up another interesting point. When pasting material in from elsewhere, it is very often useful to use Edit | Paste Special... (or Ctrl+Shift+V) instead of ordinary Paste and then to select "Unformatted text". The Text Import window then gives you various ways to control how the material is imported. In the current case, ticking Space and "Merge delimiters" will produce something close to what is needed. Where necessary, you can even modify the individual column types at the bottom of the Text Import window to control how values are interpreted.

But you probably knew that ...

Brian Barker