CODEWITHSUNDEEP
excelexcel-formulamatch
Kalev Maricq
Kalev Maricq

Reputation: 617

Match function not matching number from formula

In excel, the match function is failing to find a match in a range that contains it.

Since we can't attach workbooks, create the MRE with the following steps:

  1. A1=.99
  2. B1=1-A1
  3. C1=MATCH(.01,B1:B2,0)

For me, C1 is #NA. Does it show that for you? If so, why? If not, can you think of why it would for me?

Tried:

  1. Making new workbook
  2. Testing if B1=.01 -> TRUE
  3. Hardcoding B1=.01 -> Match works
  4. Matching B1 to itself -> works
  5. Matching B1 to a cell containing hardcoded .01 -> fails
  6. Googling -> Tons of people that don't know that 0=exact match or forgot to check for whitespace in strings.
  7. Changing A1 -> Very interestingly, fails for .9, .8, and .7, but works for .6 (finding .4).

Upvotes: 0

Views: 169

Answers (1)

palako
palako

Reputation: 3490

Computers operate in base 2, not base 10, so when doing floating point arithmetic, there are some numbers that can not be represented, even if they seem perfectly fine in base 10. You are seeing 0.01, but Excel has actually calculated 0.0100000000000000000000x or something along those lines.

If you want to make your example work, try setting B1 to

=ROUND(1-A1,2)

Alternatively, if you know you just need two digits of decimal precision (assumed since you are matching to 0.01), you can just avoid the floating point all together by:

=(100-(100*A1))/100

Upvotes: 3

Related Questions