VLOOKUP next greatest value?

I would like to find the SMALLEST value in an array that
is GREATER than lookup_value.

However VLOOKUP returns the LARGEST value in an array that
is LESS than or equal to lookup_value.

Any one know how to do this?

TIA

One way

=INDEX(B1:B100,MATCH(SMALL(A1:A100,COUNTIF(A1:A100 ,""&C1)+1),A1:A100,0))

where a vlookup (which won't work of course) would look like

=VLOOKUP(A1:B100,2,TRUE)

--

Regards,

Peo Sjoblom


"bostitch" wrote in message
...
I would like to find the SMALLEST value in an array that
is GREATER than lookup_value.

However VLOOKUP returns the LARGEST value in an array that
is LESS than or equal to lookup_value.

Any one know how to do this?

TIA

Hi:

Try:

=INDEX(Lookup_Range,MATCH(VLOOKUP(Lookup_Value,Loo kup_Range,1),Lookup_Range,
0)+1)

Regards,

Vasant.

"bostitch" wrote in message
...
I would like to find the SMALLEST value in an array that
is GREATER than lookup_value.

However VLOOKUP returns the LARGEST value in an array that
is LESS than or equal to lookup_value.

Any one know how to do this?

TIA

"bostitch" wrote...
I would like to find the SMALLEST value in an array that
is GREATER than lookup_value.

However VLOOKUP returns the LARGEST value in an array that
is LESS than or equal to lookup_value.

Since VLOOKUP only does this when the first column in the table range is sorted
in ascending order and you're using nonexact matching, replace

=VLOOKUP(v,t,n)

with

=INDEX(t,MATCH(v,INDEX(t,0,1))+1,n)

*DON'T* use 0 as 3rd argument to MATCH - you could get a lot of #N/A errors when
there's no exact match for v in the first column of t.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.