=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?

 

[skyport]

CORRECTION to the above post:

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 (should be column AK) 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.

 

[Noodleski]

Match is sensitive to case? Just tried it out on some stupid example and it didn't care if it was proper, upper or lower case.
In case that is the issue, there's a formula UPPER that would allow you to make everything in upper case, so the search array and the search value are always the same case.

 

[tygrrboi]

Using your code, I get "ALERT" in the cell both when I type it exactly as written in the AK column, and when I type it with different case.

a a ALERT
b
c

a A ALERT
b
c

a x
b
c

Maybe I don't understand the question. I don't see an issue.

Have you checked for invisible spaces or characters?

 

[skyport]

I seem to have the issue if the one column has different words capped than the other column has even though the words and characters are identical.

 

[jtakw]

Hi,

As tygrrboi said, check for leading and/or trailing spaces, invisible characters, like:

Excel 2010
	A	B
1	a	FALSE
Sheet1

Cell Formulas
Range	Formula
B1	=A1="a"

That returned false because there is a space after a in A1.

 

[Noodleski]

What happens when you add Upper to your formula?

Assuming your array is in upper, you can change a non-upper search value to upper by adjusting your formula like this:

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

 

[skyport]

checking what was suggested, the problem could be originating from the fact that both columns AL and AK are populated via concatenate of multiple cells each. Perhaps there needs to be a way to convert the concatenated results before the ISERROR function. I am at a loss there.

 

[Noodleski]

Any chances of sharing a sample of the file via dropbox or something similar? My suggestion probably won't work because none of us seem to have issues with lower-upper case searches... I just wanted to confirm that the case is irrelevant to your issue.

The concatenate function shouldn't interfere with the search, provided the returned string is identical. (spaces and other delimiting characters will interfere with a search and are hard to see)

 

[skyport]

To Noodleski

I thought your idea of (UPPER inserted into the formula would have solved it however, I get an error of "too many arguments for the function"

Can we see if there is a way to overcome that first and if so it could/should do it I would think