INDEX MATCH MATCH function returning "N/A" Error on some cells in a column but not others

[CAM29]

Hello,
I am trying to use the INDEX MATCH MATCH function to return values from a table for different combinations of inputs. I am also having to use MROUND to get the input values to match the table, and this seems to be causing a #N/A error.

The formula I had was as follows: =INDEX( [tablearray] , MATCH( MROUND( [input1] , 0.05), [lefthandcolumnoftable]) , MATCH( MROUND( [input2] , 0.05), [toprowoftable] ) )

I have also broken out each step to simplify the issue and it definitely looks like MROUND is throwing it off - the formula works fine when the input values already match the table values exactly.

Can anyone give me a way round this problem?

 

[FatBoyClam]

One of your matches is not finding the searched for value (whatever your MROUND returns), this will result in the #N/A error.

to prevent MATCH returning an error you need to either
a) ensure you're providing a lookup value which will be located in the lookup range, and provide the 3rd parameter to MATCH as 0
b) provide the 3rd parameter to MATCH as either 1 or -1 and ensure the data in the lookup range is sorted appropriately.

If you don't provide the 3rd parameter, Excel behaves as though you'd provided a value of 1, and returns the position of the highest value less than or equal to the lookup value, but the lookup range must be sorted in ascending order. If the values are not in ascending order, odd results will occurr.

 

[joeu2004]

Try ROUND(MROUND( [input1] , 0.05), 2) .

You are probably encountering an anomaly of 64-bit binary floating-point arithmetic, namely: the binary result of MROUND(..., 0.05) does not always equal the equivalent constant, or at least the binary values in "lefthandcolumnoftable".

Even the ROUND(..., 2) suggestion might not work if the values in "lefthandcolumnoftable" are calculated, not constants. You might also need to apply ROUND(..., 2) to those calculations.

The following demonstrates the problem.

Book1
	A	B
1	874.15
2	874.15	A2: =MROUND(A1,0.05)
3	#N/A	A3: =MATCH(A1,A2,0)
4	#N/A	A4: =MATCH(A1,A2,1)
5	True	A5: =A2=A1
6	0.00E+00	A6: =A2-A1
7	False	A7: =A2-A1=0
8	1.14E-13	A8: =A2-A1-0
Sheet1

Even though the constant in A1 is already rounded to a multiple of 0.05, MROUND in A2 returns a different binary value because 0.05 cannot be represented exactly in 64-bit binary floating-point, which is what Excel uses to represent numerical values internally.

Consequently, the exact match in A3 fails.

In this case, the approximate match in A4 also fails, demonstrating that the binary value in A1 is infinitesimally less than the binary value in A2. That is not always case. So with some other examples, the approximate match might succeed, even though the exact match fails.

The formulas in A5 and A6 demonstrate some misleading foibles of Excel. The equal operator ("=") compares the left and right operands rounded to 15 significant digits, not their binary values. And under some arbitrary conditions, if the last operation of a formula is subtraction with a result that is "close enough" to zero, Excel substitutes the exact arithmetic result (A8) with exact zero.

The formulas in A7 and A8 demonstrate methods for "working around" the arbitary choices that Excel makes, showing the exact results of the binary comparison and the binary arithmetic.