Finding the last approximate Match with negative values

[tatuuuuuu]

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

 

[jasonb75]

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.