Cross referencing 2 Columns, but formula isn't properly working.

[JoshHouse]

I am trying to cross-reference 2 columns. Column G has a list of Specialties and Column I is the column that needs to be checked if any of those terms are in Column G.

The formula I am using is =IF(OR(COUNTIF($G3,"*"&I3&"*")),"Match","No Match"). AS you can see in the screenshot you can see that there are matches and no matches. The very first instance shows in D2 but if you check both columns G & I; Column I does in fact have those terms in column G but shows to be a no match.

What am I missing or what am I not doing right?

Screenshot Image 

[#2]

@JoshHouse 

Your formula will return a match only if the entire text of I3 occurs within the value of G3, in exactly the same order. G3 does not contain "Cardiology, Interventional Cardiology" exactly like that.

If you are an Office Insider, you can probably use the new text manipulation functions.

Otherwise, you'd have to split column I into multiple columns, with one specialty per column. Or use a custom VBA function.

[#3]

@JoshHouse 

=IF(LEN(I3)-LEN(SUBSTITUTE(I3,",",""))=0,IF(N(ISNUMBER(FIND(I3,G3))),"Match","No Match"),IF(LEN(I3)-LEN(SUBSTITUTE(I3,",",""))=1,IF(AND(ISNUMBER(SEARCH(LEFT(I3,FIND(",",I3)-1),G3)),ISNUMBER(SEARCH(RIGHT(I3,LEN(I3)-FIND(",",I3)-1),G3))),"Match","No Match"),IF(LEN(I3)-LEN(SUBSTITUTE(I3,",",""))=2,IF(AND(ISNUMBER(SEARCH(LEFT(I3,FIND(",",I3)-1),G3)),ISNUMBER(SEARCH(RIGHT(I3,LEN(I3)-FIND(",",I3,FIND(",",I3)+1)-1),G3)),ISNUMBER(SEARCH(MID(I3,FIND(",",I3)+2,FIND(",",I3,FIND(",",I3)+1)-(FIND(",",I3)+2)),G3))),"Match","No Match"))))

You can try this formula which returns the expected results in my sheet if there are a maximum of 3 search criteria (separated by max. 2 comma) in column I.