Finding the last approximate Match with negative values

tatuuuuuu

New Member
Joined
Mar 22, 2021
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top