Finding Cell above Using Vlookup
 

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

Finding Cell above Using Vlookup
 

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

Finding Cell above Using Vlookup
 

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