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" wrote in message
...
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<MA X(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.