Forum Discussion

Copper Contributor

Apr 09, 2018

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...

Formulas & Functions

Need Help

Braden Dauer

Copper Contributor

Apr 10, 2018

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.

IngeborgHawighorst

MVP

Apr 10, 2018

So, does the suggestion with the rounding work?

Braden Dauer
Copper Contributor
Apr 10, 2018
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.