Forum Discussion
Lookup Value not being passed correctly for Match() Function
Hello,
What exact values are in R2 and J2?
This looks like a floating point issue. In the Switch() function, you serve the numbers with 2 decimals. The result of the division may end up in a number that has a tiny difference.
In such cases, you may want to round the result of the division to 2 decimal places.
=MATCH(round(R2/J2,2), S2:AM2, 0)
Now you can argue that SWITH() should not find a value if there is no perfect match, either. I don't know if Switch uses the same algorithm to find a match. It looks like it is less sensitive to the floating point issue than MATCH().
Thank you so much for you reply. In my formula, R2 and J2 are references to cells that also contain functions.
R2=SUM(S2:AM2) where the cells in the array contain VLOOKUP() functions that pull from other tabs in the workbook. In one of the cases that is not working, the displayed value of R2=302.1 (Note: if I replace the sum function in this cell with the value 302.1 my Match function works)
J2=21-COUNTIF(S2:AM2,"TBD")-COUNTIF(S2:AM2,"NONE")-COUNTIF(S2:AM2,"N/A") which in the case where R2=302.1, J2=15
R2/J2= 20.14
Background: What I am trying to do is determine whether or not the average value in the array S:AM actually appears as an exact match in the array. So R2/J2, in most cases, will be the average value within the array.
So, does the suggestion with the rounding work?
Actually...it does! Surprisingly. I was doubtful that it would work because result of the division operation has a finite number of digits in base-10 and so I would not have though it would require rounding. Thank you reminding me about the floating point issue. This also explains why the function was working for some cases and not others, since the issue is with the binary representation and not the base-10 value.
