Match function failing due to unrecognized characters

[Yue_Iohikazu]

Greetings.

I need your help with one match function that is failing only for some situations.

The functions is right now as following:

=INDEX(Tracker!Q4:CR4,1,(MATCH(Status!K3,Tracker!Q4:CR4,0)-1))

Its purpose is to read the a character string from "Status!K?" cells, search for that string on the "Tracker!Q?:CR?" range and return with the value from one column before, which is also a text string.

This function is working as expected except for some situations where the text in both, "Status!K" and "Tracker!Q?:CR?" contain line brakes, listings and special characters inside a single cell, this is because the text is a copy from another tool.

I believe that the reason for the function to fail on some situations is because the functions is unable to recognize some special characters, or may be the long length, which end up breaking the comparison process.

I have tried with alternatives like this:

=INDEX(Tracker!Q18:CR18,1,(MATCH(LEFT(Status!K17,20)&"*",Tracker!Q18:CR18,0)-1))
=INDEX(Tracker!Q18:CR18,1,(MATCH(LEFT(Status!K17,20)&"*"&RIGHT(Status!K17,5),Tracker!Q18:CR18,0)-1))

But results are the same, #N/A.

Can you please help?

I would really appreciate it.

Thanks in advanced and best regards.

 

[Special-K99]

Try substituting null "" for CHAR(160) thats a common one especially if data is from the internet.
Either that or identify the offending characters in question.

 

[Yue_Iohikazu]

Try substituting null "" for CHAR(160)

Dear Special-K99,

Thank you very much for your help, but none of my formulas contain any null ("") value, it contains a * within the "" which is the wildcard for the MATCH function, I included it in my alternate formulas in an attempt to only validate through the initial 20 characters from the string and allowing the rest to be overlooked due to the wildcard; although it didn't work out.

I have uploaded my excel file on the following URL for your reference:
filehosting.org | Download | Status tracker.xlsmfilehosting.org | Download | Status tracker.xlsm

Thanks in advanced and best regards.