determine if data exists on 2 sheets

What type of formula would I need to use for the following:



I need to find a way to determine if the same client name exists in tabs A and B.

Hi, welcome to the forum

I have changed your title for you, for future reference, please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?).

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

I have looked at your file, and this is not a simple comparison.

1. much data on sheet1 has a leading space (no biggie to overcome that)
2. some names end with . on 1 sheet, but , or another, or 1 sheet has ., the other doesn't (again, not a huge problem)
3. some names start with 1st/last name on 1 sheet, but last/1st name on the other sheet ..."Adam Sauer" and Sauer, Adam (big problem)

I made a start with this...
=MATCH(TRIM(A2)&"*",'search accounts'!$A$2:$A$8466,0)
This will give you the row number that a match was found on. Once we have a match, we can adapt that to show whatever you want.

It cannot find 3939 out of 4174