Finding Cell above Using Vlookup

[Ralph]

Hi I am using vlookup to find a value based on a value that usually is not in
the lookup table. Vlookup always finds the next higher value but sometimes I
want the value in the lookup table that immediatley lower as it is closer to
the search value

The vlookup formula looks like this:

VLOOKUP(29.2522,Sal_Tables!$B$3:$D$7,NFPC!S2,TRUE)

the Values in the table:
27.5341
28.3499
29.2073
30.0927
31.4109

vlookup returns 30.0927 but I want 29.2073 as it is closer to the search
value and in about 2200 instances I need to coompare the table value found
and the table value immediately above it.

Thanks

 

[Barb Reinhardt]

You may need to use Match with Index to get what you want.

 

[Pete_UK]

Assuming those values are in column A, then this formula:

=INDEX(A:A,MATCH(29.2522,A:A))

returns 29.2073 (as does your VLOOKUP), whereas this one:

=INDEX(A:A,MATCH(29.2522,A:A)-1)

returns 28.3499 (from the row above), and this:

=INDEX(A:A,MATCH(29.2522,A:A)+1)

returns 30.0927 (from the row below)

Hope this helps.

Pete