Index/Match function connected to the Ceiling/Floor function

[MiguelPenaWSE]

Hi there!

I am having an issue with the Index/Match function connected to the Ceiling/Floor function - I was wondering if you could provide some guidance.

I could email you the SpreadSheet or I can try and explain it.

Here is the simple explanation;

(1) For these values, everything work.

A24 = 0.14 ------##(input)
A23 = FLOOR(A23,0.1) = 0.10
A25 = CEILING(A23,0.1) = 0.20

B23 = INDEX(ARRAY,MATCH(A22,ARRAY,0)) = 0.6
B25 = INDEX(ARRAY,MATCH(A24,ARRAY,0)) = 0.7
B24 = INTERPOLATION BETWEEN B22&B24 WITH RESPECT TO A24 = 0.64

(2) when A24 is a number <0.3999 & >0.30001 - I get an error.

A24 = 0.38 (or any other number >0.30001 and <0.3999) ------##(input)
A23 = FLOOR(A23,0.1) = 0.30
A25 = CEILING(A23,0.1) = 0.40

B23 = INDEX(ARRAY,MATCH(A22,ARRAY,0)) = #N/A
B25 = INDEX(ARRAY,MATCH(A24,ARRAY,0)) = 0.8
B24 = INTERPOLATION BETWEEN B22&B24 WITH RESPECT TO A24 = #N/A

B23 gives me an error (#N/A) only when 0.3001<A23<0.3999 - This happens when "A23" is formulated with the FLOOR function.

However, if I manually change "A23" to 0.3 - then "B23" yields a valid result.
Keep in mind that this only happens when 0.3001<A23<0.3999 - At any other range, the functions work just fine.

Please let me know if you have any insight on this.
I think it might be a bug but I am not quite sure.

Thank you.

 

[SkipVought]

Hi,

The 0 in your MATCH() function means that it wants an EXACT match. You need a 1 or -1 depending how your lookup data is sorted.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MiguelPenaWSE]

Skip,
Actually I want excel to give me the EXACT match.

Please, if you don't mind, refer to the attached image for the following explanation;
"E17" = 0.3
"F17" = 0.7
The values from "F15" to "F19" are the data values in the array.

"A24" = 0.38
"A23" = FLOOR(A24,0.1) = 0.3
"A25" = CEILING(A24,0.1) = 0.4

"B23" = INDEX (F15&F19 , MATCH (A23 , E15&E19 , 0) )

If I have this way, excel should give me the EXACT match which in this case would be 0.70 - not #N/A

Thanks!

 

[SkipVought]

Is the value EXACTLY 0.3?

COPY the cell.

PASTE SPECIAL VALUES to an empty cell to observe the exact value.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MiguelPenaWSE]

Skip,

Thank you very much for your help - I did what you suggested and it worked!
I assigned a (-1) value instead of (0) and it was able to extract the accurate number.

I also did what you suggested with the COPY / PASTE SPECIAL -
the values that was "pasted" was a "0.3" - which is what was expected.
I am still not sure why it wouldn't work with EXACT MATCH.

It could be a bug. Like I said before, with any other number rather than "0.3", the functions were perfectly.

Thanks a lot!

 

[SkipVought]

Is the lookup value 0.3 or "0.3"?

Is the value in the lookup table 0.3 or "0.3"?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]