Excel find partial match in column

Question

I am trying to write a formula which will return a positive value to a cell G3 if the content of D3 is a partial match to anything found in the same column excluding itself. I.e.

Red - No Match Blue - Partial Match Light Blue - No Match

No match because "Red" is not a partial match with any of the other results, Partial match because "Blue" IS a partial match for "Light Blue".

I am using the current formula, but it returns false when I expect it to return true

=IF(ISNUMBER(SEARCH(D3,$D$1:D2,D4:$D$10000)),"Yes","No")

My original formula:

Suggested formula:

Do you have some sample data or screenshot? To get a better understanding of the situation. With desired output. — JvdV
– JvdV, Commented May 3, 2019 at 12:00
Thank you, I have added a link to my original formula above. I am hoping column G to read "Yes" if a match is found and "No" if it is not. — Tom Sutherland
– Tom Sutherland, Commented May 3, 2019 at 12:11

Foxfire And Burns And Burns · Accepted Answer · 2019-05-03 11:56:05Z

1

a formula which will return a positive value is too broad. But I did a formula that counts your partial matches. If the result of the formula is 0, means there is no matches or partial matches:

The formula I've used:

=COUNTIFS($A$1:$A$3;"*" &A1&"*")-1

I added a -1 because with this formula you will get always at least 1 match (the cell you want to exclude). So with -1 you make sure you get the other partial matches.

You cam combine this with a normal IF. If result of this formula is 0, means there is no partial matches (excluding the own in same row, of course). If is not zero, it means there are partial matches. Then do whatever you need to do to get that positive value.

UPDATE: Now I understand what you mean with positive value. You can combine the formula above with an IF as I said. Try something like this:

=IF(COUNTIFS($A$1:$A$3;"*" &A1&"*")-1=0;"NO";"YES")

Or try:

=IF(COUNTIFS($A$1:$A$3;"*" &A1&"*")-1=0;FALSE;TRUE)

answered May 3, 2019 at 11:56

Foxfire And Burns And Burns

12.1k2 gold badges19 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tom Sutherland Over a year ago

Thank you for the advice but I don't seem to be able to get this working. It tells me that there is a problem with the formula? Edit: It selects the $A$3 when erroring.

Foxfire And Burns And Burns Over a year ago

Can you post an image of how you are doing it? Or download a sample from here: drive.google.com/open?id=1Di69g1IVGng6UOQL7bbGz0foaDfHpPYx

Tom Sutherland Over a year ago

Ah your sample link works great! Thank you. Would there be a way to alter this so that if a match is found, but that match also contains a particular word then it is ignored? E.g. BackOffer is found in BackOfferDesc so it a positive match. But, BackOffer is also found in BackOfferArabic, but I would like to ignore any matches containing "Arabic"?

Tom Sutherland Over a year ago

Actually I could do this by having another lookup searching for "Arabic" and then use both columns to show what matches, but DOESN'T contain "Arabic"

Foxfire And Burns And Burns Over a year ago

That depends on how is your data, but yeah, probably with another If or maybe another countif you could specify that only counts BackOffer excluding Arabic

Collectives™ on Stack Overflow

Excel find partial match in column

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related