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

 

[izhassan]

What do you have exactly in A1 and in C1:C3?

A1 and C1 has...

This is an honest suggestion: 1. Please remove the quota system in performance evaluation. 2. On promotion issues, I believe it is not fair. For example, I am the senior secretary (job grade 15) in our department, but until now (8 years of service), I did not get promotion. Whereas there are other secretaries in same department (3 years of service), but they got promotion. Suggestion: Serious focus on promotion matters (fairness).

A2 TREE
A3 WHY
A4 how

C2 tree
C3 How
C4 why

The formula being used is IF(ISERROR(MATCH(A1,$C$1:$C$4,0)),"","MATCH")

A1 and C1 are not matching even though I copy pasted A1 into C1. The rest return MATCH.

 

[Aladin Akyurek]

...
This is an honest suggestion: 1. Please remove the quota system in performance evaluation. 2. On promotion issues, I believe it is not fair. For example, I am the senior secretary (job grade 15) in our department, but until now (8 years of service), I did not get promotion. Whereas there are other secretaries in same department (3 years of service), but they got promotion. Suggestion: Serious focus on promotion matters (fairness).
...

What is this? Do you have an Excel question or a problem with your boss? As far as I know, we do just Excel here...

 

[izhassan]

What is this? Do you have an Excel question or a problem with your boss? As far as I know, we do just Excel here...

That is the data in the cell A1 and C1 which is not matching using the IF(ISERROR(MATCH formula. I have a list of comments from a recent survey and I'm matching them to departments.

 

[izhassan]

Each row in Column A and C is full of comments, small to long comments. A has the entire comments made by the organization and C is department wise. I want to match them using IF(ISERROR(MATCH. That paragraph above is not matching. Please check as to why or what can I use as an alternative to match a bunch of text in two different columns.

 

[Aladin Akyurek]

That is the data in the cell A1 and C1 which is not matching using the IF(ISERROR(MATCH formula. I have a list of comments from a recent survey and I'm matching them to departments.

If so, C1 = tree (or three), won't match A1 at all. If you think it should, which part of A1 does it match?

 

[izhassan]

If so, C1 = tree (or three), won't match A1 at all. If you think it should, which part of A1 does it match?

Those random words (Tree , tree, WHY and why) are just to see if the formula is working which it is. I write the formula in B1 and then scroll it down to B4. It shows a match because A2 matches C2 and A3 matches C4 and A4 matches C3. So A1 should match C1 but it isn't. That is my issue.

 

[Aladin Akyurek]

Those random words (Tree , tree, WHY and why) are just to see if the formula is working which it is. I write the formula in B1 and then scroll it down to B4. It shows a match because A2 matches C2 and A3 matches C4 and A4 matches C3. So A1 should match C1 but it isn't. That is my issue.

Instead of discussing a formula, try to describe what you want to match against those comments along with the result that must obtain...

 

[izhassan]

Instead of discussing a formula, try to describe what you want to match against those comments along with the result that must obtain...

Okay... so I want to match this big paragraph...

"This is an honest suggestion: 1. Please remove the quota system in performance evaluation. 2. On promotion issues, I believe it is not fair. For example, I am the senior secretary (job grade 15) in our department, but until now (8 years of service), I did not get promotion. Whereas there are other secretaries in same department (3 years of service), but they got promotion. Suggestion: Serious focus on promotion matters (fairness)."

with this paragraph...

"This is an honest suggestion: 1. Please remove the quota system in performance evaluation. 2. On promotion issues, I believe it is not fair. For example, I am the senior secretary (job grade 15) in our department, but until now (8 years of service), I did not get promotion. Whereas there are other secretaries in same department (3 years of service), but they got promotion. Suggestion: Serious focus on promotion matters (fairness)."

Both are in different columns. Data is huge. I just want the result to show the word MATCH beside the cell which contains the data. So if data is in A1 and C765, then the B1 should show MATCH. If A45 has a similar data such as in C32 then B45 should show MATCH.

 

[Aladin Akyurek]

Okay... so I want to match this big paragraph...

"This is an honest suggestion: 1. Please remove the quota system in performance evaluation. 2. On promotion issues, I believe it is not fair. For example, I am the senior secretary (job grade 15) in our department, but until now (8 years of service), I did not get promotion. Whereas there are other secretaries in same department (3 years of service), but they got promotion. Suggestion: Serious focus on promotion matters (fairness)."

with this paragraph...

"This is an honest suggestion: 1. Please remove the quota system in performance evaluation. 2. On promotion issues, I believe it is not fair. For example, I am the senior secretary (job grade 15) in our department, but until now (8 years of service), I did not get promotion. Whereas there are other secretaries in same department (3 years of service), but they got promotion. Suggestion: Serious focus on promotion matters (fairness)."

Both are in different columns. Data is huge. I just want the result to show the word MATCH beside the cell which contains the data. So if data is in A1 and C765, then the B1 should show MATCH. If A45 has a similar data such as in C32 then B45 should show MATCH.

The strings to match are longer than 255 chars, something MATCH cannot handle.

In B1 enter rather and copy down:

=IF(SUMPRODUCT((C:C=A1)+0),"match","no match")