Try this array formula** :

A1 = lookup value

C1:D12 = lookup table

=INDEX(D1:D12,MATCH(MIN(ABS(C1:C12-A1)),ABS(C1:C12-A1),0))

This will return the first instance of the closest value. For example, if
the lookup value is 7 and the lookup table contains 5 and 9, the absolute
difference is the same but 5 is listed first so the match will be with 5.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Craig" wrote in message
...
Hi...

In the Vlookup approximate match situation, (range lookup argument is
true)
the function returns a value on the same row to the closest matching value
in
a lookup table that is less than or equal to the lookup value...is there
anyway to change it so it returns a value on the same row to the closest
matching value in to the lookup value even though the closest value may
be
higher than the lookup value?
thank you!

Craig