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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,637
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,129,689
Messages
5,637,837
Members
416,985
Latest member
mrindira

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