Help with =IsNumber(Search for multiple criteria

[HKM]

Hi All

I have a long list of first and last names that I want to look up in my master file. I need every variation of the first and last name since they are not always entered in the same order. The formula I found works for this purpose is:
=ISNUMBER(SEARCH('Names'!$A$2,A2))*ISNUMBER(SEARCH('Names'!$B$2,A2))
A2 - First Name
B2 - Last Name

I need to find every first and last name combination using the data in sheet "Names" that matches (based on the formula above) in sheet "Search File".

Can someone please help me identify a way to do this? I was thinking maybe a loop function in vba, but I'm not exactly sure how to go about it.

Any help would be greatly appreciated.

Thank you!

 

[vletm]

Something like this ...

 

[HKM]

Thanks for the reply vletm!

This is great. The only issue I see is that the function isn't assuming a relationship between the first and last names listed on one row in the names tab. Ex: Row 11. "John David" is being flagged because there's a FN: John LN: Stewart and a FN: Larry LN: David listed on the names tab.

Also, is there a way to not do an exact match. so if there's a "Johnny" instead of "John" I want the function to recognize this as a match.

Thank you!

 

[vletm]

@HKM hmm ...
>Columns D only shows do the 1st part of Column A can found from 'First Name'- or 'LastName'-column. Column E works same way.
Cells [D1&E1] aren't titles, those tries to tell that F = FirstName and L = Lastname.
Row2's - cells[D2] - 'F2', means that 'John' has found from 2nd row of FirstName.
Column C shows rows FirstName and Column F shows rows Lastname.
> Case 'Johnny' - 'John', Did You test to add 'Johnny' to column FirstName?
That way it works!
But but, if You add 'Johnny' to LastName, this gives the 1st LastName, not necessary 'Johnny' ( like row 4 ).
> If You write only one 'string' like 'CarlStewart', then this not work.
Questions?