Struggling with IF search and Find to compare 2 Cells text.

Howdy all,

In an effort to be succinct.

I am presently collating a list of people that have previously completed a training course. This totals over 3500 people. Within this sample I am trying to specify, who is at their original company, in the same role, who is at their original company in a new role and most importantly who has moved on to a new company. In short I have captured their old company, new company and old and new roles.

My first column is my primary filter. Which I have set as Updated, This can contain, 3 options. “Yes”, “No match” & “Closest Match”. I have columns for new role, which I can filter against as this is simply a Yes or No answer. I am struggling though with the Yes and Closest Matches option. I have put a formula together =IF(A2="Yes",IF(ISERROR(SEARCH(H2,N2)),IF(ISERROR(SEARCH(N2,H2)),"Yes")))))) Which I was hoping would compare the cells for both the new company name against the old company name and vice versa. The trouble being that the names for the companies can have the full name. For example :Zurich Finical services. OR just Zurich, Or Zurich Insurance ltd. Now obviously the key word here is Zurich, but I can’t do a hard text search for that as there are over 600 different companies with similar variations.

So basically without VBA (I am passing this sheet on once it’s done and I don’t want a support legacy if I can help it) I am wondering if there is a way of working off an initial flag, either “Yes” or “Closest match” to compare 2 cells for a changing identical word that they have in common, and then return me “No Change”, or “new company” or “variant company” which I can then filter against.

Ideally I’d like to rest this in one cell if possible. I’ve got this far Yes =(IF(A2="No Change","No Change",IF(A2="No Match","No Match",IF(A2="Yes",IF(ISERROR(SEARCH(H2,N2)),IF(ISERROR(SEARCH(N2,H2)),"Yes")))))) But I am getting some ”False” come up and I’ve not got to the closest match flag yet.

Am I barking up the right tree, can it be done or do I need to split the data out and then pull it back in again some how.

All answers very welcome.

Thanks in advance

Jimmy