VLOOKUP, XLOOKUP, INDEX/MATCH Issue Finding Exact Match Among Calculated Values

 My coworker came across an issue with Exact Match when using VLOOKUP, and I found the same thing happens for XLOOKUP and INDEX/MATCH.

 VLOOKUP will not match the result of a formula correctly.  If you open up a new Excel sheet, here is how to replicate it:

1. In cell A1, type 20.2.
2. In cell B1, type =A1/101. The result should be 0.2 exactly.
3. In cell C1, type =VLOOKUP(0.2, B1, 1, 0). The formula cannot find 0.2.

Why is this the case? It will find 0.3 if you adjust the numbers:

1. In cell A2, type 30.3.
2. In cell B2, type =A2/101. The result should be 0.3 exactly.
3. In cell C2, type =VLOOKUP(0.3, B2, 1, 0). The formula WILL find 0.3.

This is also the case for XLOOKUP using =XLOOKUP(0.2, B1, B1, , 0).

This is also the case for INDEX/MATCH using =INDEX(B1, MATCH(0.2, B1, 0)).

Everything is formatted as a number. Formatting isn't the issue. Neither is sorting numerically (especially since my example above is only one value).

I attached a PDF (I couldn't attach an Excel file) of a larger example showing that these formulas consistently work sometimes. I can enter in the data values manually as a Plan B (as I show on the right side of the example spreadsheet), but that doesn't answer why these look-ups don't always work as intended with my Plan A. Any ideas?

VLOOKUP, XLOOKUP, INDEX-MATCH Issue.pdf

Thanks!