SOLVED

MATCH function not working, even when I can see value in the list

Copper Contributor

Hello,

 

I am using the MATCH function to search a list of numbers for a specific number (exact match, or "0" in the conditions). Each individual number in the list has been pulled from a PivotTable to create the list. 90% of the time, matching values in the list will return what I want, but there are some spotty "#N/A" errors.

 

The list I am searching through is formatted as accounting numbers and the value I am searching for is just a normal number (again, it's working for most numbers, so I don't think the data type is the issue). As far as I can tell, there are no hidden characters. I tried using the TRIM function on both the list values and the search value to see if they had spaces I didn't see, but the errors remained.

 

Something I did just to mess around was copy the list and put it back where it was by pasting as values, getting rid of the connections to the PivotTable. Then, with the list's numbers hard-coded, I would find the number I was searching for in the list, click into it, and press backspace a few times in front of the number, as if I was deleting leading spaces. Once I did this, the match function worked, so I assumed manipulating the cell in some way (probably doesn't have to exactly be the deleting thing I did) fixed it. However, I can't do this every time there is an "#N/A" error, or else I'd have to do it 119 times.

 

If anyone has an idea for how to fix this glitch in the MATCH function, you'll be saving me SO much time and I will be very grateful.

 

Thanks

5 Replies

@jmw_87 

In general it's better to clean source data for the PivotTable. CLEAN() could help but not necessary, depends on which non-printable characters are in the text. 

As variant and since you are on 365 you may use

=XMATCH("*" & number, range & "", 2)

 

best response confirmed by jmw_87 (Copper Contributor)
Solution

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

 

Thank you so much for your answer. As far as I know, I cannot manipulate the file with the PivotTables since it is a client's file, so anything I do has to be done through this Excel template I'm working with that references that. That being said, the MATCH formula solved my problem. I had a field that counted the number of errors, and once I entered the formula, it went from 119 to 0. Thank you, once again!
Thank you for your response! I looked into the XMATCH function from your answer, which I've never used before. It looks like it will be useful for another project I have, so thank you, once again!

@jmw_87 , good luck with XMATCH(), glad this discussion helped

1 best response

Accepted Solutions
best response confirmed by jmw_87 (Copper Contributor)
Solution

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

 

View solution in original post