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

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

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

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)

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.

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)

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.