lookup

[les]

I can used 'Lookup' to retrieve a value that matches
another but how do I use it to look up the nearest value
to the one stated?

The outcome of a formula may no be an exact match to the
value in lookup e.g. the 2 columns in lookup may be
1,2,3,4 etc in A and 10,20,30,40 etc in B but if the
lookup figure is 1.8 no value will be return as there is
no exact match.

any help would be much appreciated

Les

 

[Jazzer]

Hi Les,

You can use VLOOKUP, that returns a value, even though it doesen't fin
an exact match. In you case using this:

=VLOOKUP(1.8,A1:B4,2,TRUE)

returns 10, which is match for the previous item in the list (1 => 10)
As it says in the Excel help:

"If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it use
the largest value that is less than or equal to lookup_value. "

In this case the lookup_range (A1:A4) should be shorted ascending.

- Asse