vlookup - finding the next value that is GREATER than the lookup value?

  • Thread starter Thread starter Harold Good
  • Start date Start date
H

Harold Good

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
 
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(C1,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
 
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 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))
Click to expand...
....

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)
 
Also...

=INDEX($B$1:$B$10,MATCH(C1,$A$1:$A$10,1)+(LOOKUP(C1,$A$1:$A$10)<>C1))

Harlan said:
Bernie Deitrick wrote...


...

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)
Click to expand...

--

[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.
 
Thanks, I'll give this a try too.

Harold


Harlan Grove said:
Bernie Deitrick wrote...
...

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)
Click to expand...
 
Thanks to you all for your great help,

Harold


Aladin Akyurek said:
Also...

=INDEX($B$1:$B$10,MATCH(C1,$A$1:$A$10,1)+(LOOKUP(C1,$A$1:$A$10)<>C1))

Harlan said:
Bernie Deitrick wrote...


...

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)
Click to expand...

--

[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.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vlookup - find an alternative approximate match 4
Vlookup match greater than lookup value 4
lookup finding the next value that is GREATER 2
vlookup and match first value greater than 4
Finding value using greater than, Vlookup, and nested If 2
How do I get correct results when LOOKUP with calculated numbers 2
Finding Cell above Using Vlookup 2
lookup number greater than 2

Back
Top