Non-exact Vlookup with multiple search criteria - Microsoft Excel

Question

I'm having trouble using Excel to retrieve some dates from a table. The following figure illustrates the format of the data:

Name---------------------------First Name------------------- Surname
Billy Peter Hunt---------------Tim---------------------------Smith
Chris Paul Smith---------------Brad--------------------------Johnson
Dave Colin Jones---------------Karen-------------------------Perry
Dr James Clyde Watt------------Dave--------------------------Jones
John Phil Cross----------------Chris-------------------------Smith
Kelly Holmes-------------------Hector------------------------Baxter
Norman Bryce-West--------------Billy-------------------------Hunt

I want to be able to search through the Name column for the first cell that has the individual's First Name AND Surname and output the results into a new column for each person listed in the First Name and Surname columns (such as "Tim Smith," "Brad Johnson," etc.).

I am unable to do an exact match search because there are numerous middle names, titles, and double-barreled surnames in the Name column. To discover if there are any sub-strings that match, I must scan through each cell's contents.

I've discovered that I can search using either the First Name OR the Surname columns using Vlookup with wildcards, but I'm unable to figure out how to use both columns simultaneously.

Any assistance would be greatly welcomed in this. I've been putting all kinds of things into Google and yelling in frustration a lot.

Answer 1

Use the array function:

=INDEX(A$2:A$100,MATCH(9^99,SEARCH(B2,A$2:A$100)*SEARCH(C2,A$2:A$100)))

This will work with Ctrl+Shift+Enter but not with Enter alone.  

Depending on how many rows you have, replace 100 to any other amount you require (or, for the entire column, change A$2:A$100 to A:A, but this may take longer).

Keep in mind that if there are multiple people with the same name and last name (e.g. Tim Smith and Smith Tim, the function will give you the last result from the column).