Home |
Search |
Today's Posts |
#1
|
|||
|
|||
vlookup - finding the next value that is GREATER than the lookup value?
Hi,
Is there any way that I can get vlookup or an equivalent to use the next value that is GREATER than the lookup value? I'm not sure why it assumes we always want the value that is LESS than the lookup value. If not, any suggested workarounds? Thanks, Harold |
#2
|
|||
|
|||
Harold,
Assuming your table is sorted in ascending order based on its first column, then you could use this: =IF(NOT(ISERROR(MATCH(C1,A1:A10,FALSE))),VLOOKUP(C 1,A1:B10,2),VLOOKUP(INDEX(A1:A10,MATCH(VLOOKUP(C1, A1:B10,1),A1:A10)+1),A1:B10,2)) with your table in A1:B10, and the value you want to base the lookup on in cell C1, to return the value from the second column of your table. HTH, Bernie MS Excel MVP "Harold Good" wrote in message ... Hi, Is there any way that I can get vlookup or an equivalent to use the next value that is GREATER than the lookup value? I'm not sure why it assumes we always want the value that is LESS than the lookup value. If not, any suggested workarounds? Thanks, Harold |
#3
|
|||
|
|||
Thanks Bernie, that's really cool how that worked! Now I need to study it
and figure out what it's doing. I sure appreciate your help! Harold "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Harold, Assuming your table is sorted in ascending order based on its first column, then you could use this: =IF(NOT(ISERROR(MATCH(C1,A1:A10,FALSE))),VLOOKUP(C 1,A1:B10,2),VLOOKUP(INDEX(A1:A10,MATCH(VLOOKUP(C1, A1:B10,1),A1:A10)+1),A1:B10,2)) with your table in A1:B10, and the value you want to base the lookup on in cell C1, to return the value from the second column of your table. HTH, Bernie MS Excel MVP "Harold Good" wrote in message ... Hi, Is there any way that I can get vlookup or an equivalent to use the next value that is GREATER than the lookup value? I'm not sure why it assumes we always want the value that is LESS than the lookup value. If not, any suggested workarounds? Thanks, Harold |
#4
|
|||
|
|||
Bernie Deitrick wrote...
Assuming your table is sorted in ascending order based on its first column, then you could use this: =IF(NOT(ISERROR(MATCH(C1,A1:A10,FALSE))),VLOOKUP( C1,A1:B10,2), VLOOKUP(INDEX(A1:A10,MATCH(VLOOKUP(C1,A1:B10,1),A 1:A10)+1), A1:B10,2)) .... You could, but it's awfully redundant. Looks like OP wants approximate matching but in the reverse sense, i.e., match the smallest value in the 1st column of the lookup table that's equal to or greater than the value sought. If the OP is looking for a simple VLOOKUP replacement, then sort the lookup table by the 1st column in *DESCENDING* order and use the formula =INDEX(A1:B10,MATCH(C1,A1:A10,-1),2) If the lookup table needs to be sorted by 1st column in ascending order for display, it still doesn't require such redundancy. =INDEX(A1:B10,COUNTIF(A1:A10,"<"&C1)+1,2) Next, since VLOOKUP returns a #N/A when the value sought is less than the minimum value in the 1st column of the lookup table, symmetry would imply that the OP's formula should return #N/A when the value sought it greater than the largest value in the 1st column of the lookup table. If such functionality should be provided, the 1st formula above does so. The second formula would need to be changed to =IF(C1<=MAX(A1:A10),INDEX(A1:B10,COUNTIF(A1:A10,"< "&C1)+1,2), #N/A) |
#5
|
|||
|
|||
Also...
=INDEX($B$1:$B$10,MATCH(C1,$A$1:$A$10,1)+(LOOKUP(C 1,$A$1:$A$10)<C1)) Harlan Grove wrote: Bernie Deitrick wrote... Assuming your table is sorted in ascending order based on its first column, then you could use this: =IF(NOT(ISERROR(MATCH(C1,A1:A10,FALSE))),VLOOKUP (C1,A1:B10,2), VLOOKUP(INDEX(A1:A10,MATCH(VLOOKUP(C1,A1:B10,1), A1:A10)+1), A1:B10,2)) ... You could, but it's awfully redundant. Looks like OP wants approximate matching but in the reverse sense, i.e., match the smallest value in the 1st column of the lookup table that's equal to or greater than the value sought. If the OP is looking for a simple VLOOKUP replacement, then sort the lookup table by the 1st column in *DESCENDING* order and use the formula =INDEX(A1:B10,MATCH(C1,A1:A10,-1),2) If the lookup table needs to be sorted by 1st column in ascending order for display, it still doesn't require such redundancy. =INDEX(A1:B10,COUNTIF(A1:A10,"<"&C1)+1,2) Next, since VLOOKUP returns a #N/A when the value sought is less than the minimum value in the 1st column of the lookup table, symmetry would imply that the OP's formula should return #N/A when the value sought it greater than the largest value in the 1st column of the lookup table. If such functionality should be provided, the 1st formula above does so. The second formula would need to be changed to =IF(C1<=MAX(A1:A10),INDEX(A1:B10,COUNTIF(A1:A10,"< "&C1)+1,2), #N/A) -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
#6
|
|||
|
|||
Thanks, I'll give this a try too.
Harold "Harlan Grove" wrote in message oups.com... Bernie Deitrick wrote... Assuming your table is sorted in ascending order based on its first column, then you could use this: =IF(NOT(ISERROR(MATCH(C1,A1:A10,FALSE))),VLOOKUP (C1,A1:B10,2), VLOOKUP(INDEX(A1:A10,MATCH(VLOOKUP(C1,A1:B10,1), A1:A10)+1), A1:B10,2)) ... You could, but it's awfully redundant. Looks like OP wants approximate matching but in the reverse sense, i.e., match the smallest value in the 1st column of the lookup table that's equal to or greater than the value sought. If the OP is looking for a simple VLOOKUP replacement, then sort the lookup table by the 1st column in *DESCENDING* order and use the formula =INDEX(A1:B10,MATCH(C1,A1:A10,-1),2) If the lookup table needs to be sorted by 1st column in ascending order for display, it still doesn't require such redundancy. =INDEX(A1:B10,COUNTIF(A1:A10,"<"&C1)+1,2) Next, since VLOOKUP returns a #N/A when the value sought is less than the minimum value in the 1st column of the lookup table, symmetry would imply that the OP's formula should return #N/A when the value sought it greater than the largest value in the 1st column of the lookup table. If such functionality should be provided, the 1st formula above does so. The second formula would need to be changed to =IF(C1<=MAX(A1:A10),INDEX(A1:B10,COUNTIF(A1:A10,"< "&C1)+1,2), #N/A) |
#7
|
|||
|
|||
Thanks to you all for your great help,
Harold "Aladin Akyurek" wrote in message ... Also... =INDEX($B$1:$B$10,MATCH(C1,$A$1:$A$10,1)+(LOOKUP(C 1,$A$1:$A$10)<C1)) Harlan Grove wrote: Bernie Deitrick wrote... Assuming your table is sorted in ascending order based on its first column, then you could use this: =IF(NOT(ISERROR(MATCH(C1,A1:A10,FALSE))),VLOOKU P(C1,A1:B10,2), VLOOKUP(INDEX(A1:A10,MATCH(VLOOKUP(C1,A1:B10,1) ,A1:A10)+1), A1:B10,2)) ... You could, but it's awfully redundant. Looks like OP wants approximate matching but in the reverse sense, i.e., match the smallest value in the 1st column of the lookup table that's equal to or greater than the value sought. If the OP is looking for a simple VLOOKUP replacement, then sort the lookup table by the 1st column in *DESCENDING* order and use the formula =INDEX(A1:B10,MATCH(C1,A1:A10,-1),2) If the lookup table needs to be sorted by 1st column in ascending order for display, it still doesn't require such redundancy. =INDEX(A1:B10,COUNTIF(A1:A10,"<"&C1)+1,2) Next, since VLOOKUP returns a #N/A when the value sought is less than the minimum value in the 1st column of the lookup table, symmetry would imply that the OP's formula should return #N/A when the value sought it greater than the largest value in the 1st column of the lookup table. If such functionality should be provided, the 1st formula above does so. The second formula would need to be changed to =IF(C1<=MAX(A1:A10),INDEX(A1:B10,COUNTIF(A1:A10,"< "&C1)+1,2), #N/A) -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Vlookup but also equal to and greater than? | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |