# Finding the last approximate Match with negative values

#### tatuuuuuu

##### New Member
Hey,

I've been struggling trying to find the last approximate match from a set of negative values with the formula =LOOKUP(2;(1/(D6:D32=F22));D6:D32). It keeps returning the #N/A error "A value is not available to the formula or function".

In cell F22 I have the value -3 and in D6:D32 the following set of numbers. I am able to find the FIRST approximate match (-3,8) with the index match function =INDEX(D6:D32;MATCH(F22;D6:D32;1)). However when I want to find the LAST approximate match (-2,7) with the LOOKUP-function I am not successful. Any help on this would be appreciated.

-5,2
-3,8
-1,8
-0,7
-0,5
-0,3
-0,2
0,0
-0,2
-0,2
-0,2
-0,2
-0,2
-0,2
-0,4
-0,7
-1,1
-1,5
-2,2
-2,7
-3,5
-4,3
-5,4
-6,5
-8,2
-9,0
-10,3

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### jasonb75

##### Well-known Member
It keeps returning the #N/A error "A value is not available to the formula or function".
That's because you're looking for an exact match. You need to use >= or <= for an approximate match, although it is not clear from your post what exactly is needed.

Why is the expected result -2,7 instead of -3,5 (which is closer to the criteria than the expected -3,8 from the first match)?

With this type of formula you can only get the closest match which is either <= or >= to the criteria, not the closest either way. If that is what is actually required then the formulas become more complex.

Replies
2
Views
127
Replies
0
Views
254
Replies
3
Views
320
Replies
0
Views
201
Replies
9
Views
587

1,130,218
Messages
5,640,952
Members
417,182
Latest member
mgcorreia

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back