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

You may need to use Match with Index to get what you want.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Ralph" wrote:

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

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


On Jan 14, 10:56*pm, Ralph wrote:
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