Forum Discussion
Cross referencing 2 Columns, but formula isn't properly working.
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(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.