Search function question vlookup / ifnumber

[JulaTheOne]

Hi everyone!

I need a little help: I have to cross match Database A user names(firstname lastname) with Database B where there are some extra characters and the order is last name firstname), so the normal vlookup does not find exact match. I tried the following: I used text to column from the Database A, to have two separate column, then I tried to find the name from Database B
=IF(IF(ISNUMBER(SEARCH(A1,C1)),ISNUMBER(SEARCH(B1,C1))),"yes","no")
This show correct result when the name is in the same line
A1 Tom
B1 John
C1 John dr tom -
The results are good, but in case:
C2 John dr tom - the result are false(No)

I need to same at Database A which is a appearing at Database 2. So I need to find the duplicated items.
Can you help me out how to correctly find what I am looking for?

 

[JulaTheOne]

The closest question what I found is: https://stackoverflow.com/questions/27366504/excel-search-if-multiple-texts-exist-in-a-cell?rq=1
But these answers are not helpful if the excel needs to look in a different line.

 

[Aladin Akyurek]

Wou you post this in an Excel readable form?

 

[JulaTheOne]

=VLOOKUP("*"&B1&"*" &"*"&A1&"*",F:F, 1,FALSE) This was the answer, I managed to figure out.

 

[James006]

Congratulations on your perseverance ... :wink: