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
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