Forum Discussion
Lookup Value not being passed correctly for Match() Function
For some reason the following function is not working for certain values of R2/J2:
Example: R2/J2=32.48
=MATCH(R2/J2, S2:AM2, 0) Returns N/A
Yet
=MATCH(SWITCH(R2/J2, 32.48, 32.48, R2/J2), S2:AM2, 0) returns 1
Basically Match is not accepting the lookup value if the value is returned by R2/J2 but it does accept it if the exact same value is returned by a conditional function like SWITCH()
Any help would be much appreciated.
Thanks
4 Replies
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?
