Vlookup in Excel

I am using Vlookup and using lookup_values that do not have an exact match.
Vlookup automatically uses the next largest value that is less than
lookup_value. I want to use the next largest value that is greater than
lookup_value.

ex: vlookup(A1, C20-Q39, B2)
It ouputs K32, but I would like K33.

--
HeinzPickle

One way

=INDEX(C20:Q39,MATCH(SMALL(C20:C39,COUNTIF(C20:C39,"<"&A1)+1),C20:C39,0),B2)

assuming B2 holds the index number for the columns in your example it would
hold 9 to get to column K

--

Regards,

Peo Sjoblom




"Heinzpickle" <[email protected]> wrote in message
news:[email protected]...
> I am using Vlookup and using lookup_values that do not have an exact
match.
> Vlookup automatically uses the next largest value that is less than
> lookup_value. I want to use the next largest value that is greater than
> lookup_value.
>
> ex: vlookup(A1, C20-Q39, B2)
> It ouputs K32, but I would like K33.
>
> --
> HeinzPickle

Assuming that the lookup area/table is sorted in ascending order on its
MatchRange (i.e., on its first column)...

=INDEX(ReturnRange,MATCH(A1,MatchRange,1)+IF(A1<MAX(MatchRange),LOOKUP(A1,MatchRange)<>A1),0)

In fact, you also need a decision rule in case A1 < MIN(MatchRange)

Note also that the foregoing uses MAX which presupposes a numeric
MatchRange.

Heinzpickle wrote:
> I am using Vlookup and using lookup_values that do not have an exact match.
> Vlookup automatically uses the next largest value that is less than
> lookup_value. I want to use the next largest value that is greater than
> lookup_value.
>
> ex: vlookup(A1, C20-Q39, B2)
> It ouputs K32, but I would like K33.
>