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

Thanks to you all for your great help,

Harold


"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Also...
>
> =INDEX($B$1:$B$10,MATCH(C1,$A$1:$A$10,1)+(LOOKUP(C1,$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.

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


"Harold Good" <[email protected]> wrote in message news:OJrg%[email protected]...
> 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, I'll give this a try too.

Harold


"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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 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
news:%23$Is4%[email protected]...
> 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
>
>
> "Harold Good" <[email protected]> wrote in message
> news:OJrg%[email protected]...
>> 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
>>
>
>

Also...

=INDEX($B$1:$B$10,MATCH(C1,$A$1:$A$10,1)+(LOOKUP(C1,$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.

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" <[email protected]> wrote in message
news:[email protected]...
> Also...
>
> =INDEX($B$1:$B$10,MATCH(C1,$A$1:$A$10,1)+(LOOKUP(C1,$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" <[email protected]> wrote in message
news:[email protected]...
> 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)
>

Also...

=INDEX($B$1:$B$10,MATCH(C1,$A$1:$A$10,1)+(LOOKUP(C1,$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.

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 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
news:%23$Is4%[email protected]...
> 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
>
>
> "Harold Good" <[email protected]> wrote in message
> news:OJrg%[email protected]...
>> 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


"Harold Good" <[email protected]> wrote in message news:OJrg%[email protected]...
> 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
>

Also...

=INDEX($B$1:$B$10,MATCH(C1,$A$1:$A$10,1)+(LOOKUP(C1,$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.

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 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
news:%23$Is4%[email protected]...
> 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
>
>
> "Harold Good" <[email protected]> wrote in message
> news:OJrg%[email protected]...
>> 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, I'll give this a try too.

Harold


"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> 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)
>

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


"Harold Good" <[email protected]> wrote in message news:OJrg%[email protected]...
> 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 to you all for your great help,

Harold


"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Also...
>
> =INDEX($B$1:$B$10,MATCH(C1,$A$1:$A$10,1)+(LOOKUP(C1,$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.

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