Similar - "Unique Function" on version Excel 2019 and below

jmw_87  wrote:  `` I assumed manipulating the cell in some way (probably doesn't have to exactly be the deleting thing I did) fixed it``.

In fact, I suspect that it would be sufficient to select the cell, press f2, then just press Enter, without any "manipulation" at all.

IMHO, the problem sounds more like a binary arithmetic anomaly than a problem with invisible characters.

You can confirm by entering formulas of the form =ISNUMBER(A1) for each of the values that you are trying to match.

To demonstrate the binary arithmetic problem, enter 5528.32 into A1 and -84.22 into A2.  Then enter the following formulas:

A3: =A1+A2

A4: =ISNUMBER(MATCH(ROUND(A3, 2), A3, 0))

Note that A4 returns FALSE(!).

Then copy A3 and paste-value back into A3.  A4 still returns FALSE.

Finally, select A3, press f2, then press Enter.  Now, A4 returns TRUE.

Explanation....  With the original formula in A3, A4 returns FALSE because the value in A3 is infinitesimally less than the value that Excel displays, even if we display 11 or more decimal places (15 significant digits).

The problem is corrected when we press f2, then Enter because we are replacing the calculated result with the (binary approximation of the) value that is displayed in the Formula Bar.

-----

Bottom line:  I believe the solution is to coerce the Pivot Table to round the results of calculations.

Unfortunately, I cannot help you with that because I know nothing about PTs.

But if you attach an example Excel file, I'm sure that someone who knows PTs can help you.

-----

IMHO, that is better than working around the problem with a clever trick with (X)MATCH.

But if that's what you want to do, I think a more consistent solution would be a formula of the form:

MATCH(ROUND(X1,2), ROUND(D1:D119,2), 0)

That might have to be array-entered (press ctrl+shift+Enter instead of just Enter) in some versions of Excel.

Round to the number of decimal places that you expect to be accurate, not to an arbitrary number.  2 decimal places seems appropriate for "accounting numbers".