## Index/Match function connected to the Ceiling/Floor function

## Index/Match function connected to the Ceiling/Floor function

(OP)

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.

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.

## RE: Index/Match function connected to the Ceiling/Floor function

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,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Index/Match function connected to the Ceiling/Floor function

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!

## RE: Index/Match function connected to the Ceiling/Floor function

COPY the cell.

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

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Index/Match function connected to the Ceiling/Floor function

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!

## RE: Index/Match function connected to the Ceiling/Floor function

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

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}