=IF(ISERROR(MATCH Question

[skyport]

I am using the following formula, which gives me one problem.

=IF(ISERROR(MATCH(AL1,AK:AK, 0)), "", "ALERT")

I need to be able to have some way for the solution to ignore the differences in upper and lower case between the two columns if that's possible. As an example, the entry in AL1 is exactly the same as one of the entries in column K except for words that are in caps in the one column and not in the other and that prevents the formula from recognizing the match.

Is there some way around this problem?

 

[Noodleski]

Depends where you have placed it. Did you add it to the searched value or the search array? Technically, if the case really is the problem, this below should solve it. (Added something to remove trailing spaces as well.)

IF(ISERROR(MATCH(TRIM(UPPER(AL1)),TRIM(UPPER(AK:AK)), 0)),"","ALERT")

confirm with ctrl + shift + enter.

 

[skyport]

I would have thought that would do it but it did not. I am setting up a way for you to link to a sample I have

 

[skyport]

the link below will show an example. Sheet2 of the workbook is where the original data is entered. The sheet titled concate begins a series of processing that includes the data/formula in columns A-F, K, AL, AK, CA, CK, CL

Note: there is a precise reason why it is spread out - to allow for later additions in the other columns

https://drive.google.com/open?id=0B6dj7oSKEy9xNW9nSzc1NW5RY0U

 

[Noodleski]

Hi skyport,

I tried opening the file, but google drive is telling me I need permission to view the document. Probably a setting on the document itself. You have to 'publish' it.

 

[skyport]

I redid it and it says anyone with the link below can view and download. Nothing else required.

Note: On sheet "concate" column CA is where the ISERROR function occurs and delivers result into column K

https://drive.google.com/file/d/0B6dj7oSKEy9xc2UtSHNuaEE5Snc/view?usp=sharing

 

[Noodleski]

The accent after 'physician' is a different character. The code function returns 146 for one and 39 for the other.

 

[skyport]

wow can't imagine that one. If I take the accent out of both columns it works fine. How can what appear to be the same character be different like that?

 

[Noodleski]

wow can't imagine that one. If I take the accent out of both columns it works fine. How can what appear to be the same character be different like that?

We'll need someone a lot smarter to answer that one, I'm afraid. At least you know what has been bugging you.

 

[skyport]

is there a way to convert any and all accents in column AK to the same one used in column AL? That would solve it.

 

[Noodleski]

Sure.

CTRL + H will call the replace window native to excel.
Paste the accent used in AK into the top field, the accent used in AL into the field below, press 'replace all' and that should fix it.

You should be able to figure out where the accent is coming from. Is it you who's typing data into column AK or someone else. Some kind of data dump from an external source?