Vlookup help

Hello,

Here is the data I am working with:

Column C is (LastName FirstName)
Column D is (LastName)
Column E is (Name Formatted in our Database)
Column F is output column

The problem is, the way the name in our database formats the client info is not the same as the client's name. There are inconsistencies such as the use of "," , "and", "&"...etc.

Step 1: I used vlookup to match the last names to the list in our database with the following formula: =VLOOKUP(D2,E:E,1,FALSE)
- the exact match meant that this would always produce a #N/A value because I guess Excel is looking for entries in our database that show only the last name. Is this a correct assumption?
Step 2: I used vlookup to match the last name by approximation via the true condition - this returned a lot of wrong results.
- this is probably because of similarities in name (Brown, Brady, Br....etc).
Step 3: I used vlookup to match the first 5 characters of the last name via: =VLOOKUP(LEFT(D2,5)&"*",E:E,1,TRUE)
- This did not work for me until i changed the character value in the LEFT formula to 10. i'm not sure why? Once done, this seemed to work about 60% of the way. What does the &"*" in the formula mean?

Here is the situation now:

There are many people with similar last names but the formula cannot distinguish between first names. So for example, there are 3 people in column C with the common last name (Brady Andy, Brady Bob, Brady Tom and Jane). The formula is returning only (Brady Tom and Jane) to all three in column F.

I want a function that first does an exact match in Vlookup, then if it cannot, then vlookup an approximate. This is the solution (formula) i've used:

=IFERROR(VLOOKUP(C2,E:E,1,FALSE),VLOOKUP(LEFT(D2,10)&"*",E:E,1,TRUE))

Is this the best course of action? Any suggestions to make this process more efficient?

I still see some errors after applying this formula. For example:

In column C, I have 2 names - "Chambers Tom" and "Chambers Mary". In our database, they are entered as "Chambers, Tom & Mary" so I understand why Excel went to the approximate criteria within the formula. However, instead of returning "Chambers, Tom & Mary", it shows "Charkra, John" in column F. Shouldn't the chambers be more of a match?

Also, What is the difference between the IFNA function and IFERROR? I tried using an IFNA formula and it did not work for me? I also tried the following: =IF((VLOOKUP(C2,E:E,1,FALSE),"#N/A"),VLOOKUP(LEFT(D2,10)&"*",E:E,1,TRUE))

This formula didn't work, can you explain to me why? I'm asking a lot of fundamental questions because I really want to understand and grasp how I should think in Excel. I appreciate the efforts.

Hi,

First, (as an example), you may have a last name of "Smith John" in column C, "Smith" in Column D, and maybe something like "Smith, John & Jane" in column E. Are you trying to match each row from Column B to its counterpart from Column E, written to column F? In other words, you want to know the Database reference for the client John Smith.

Regarding the &"*" in the formula, the * is a wildcard. It basically says that this letter can be anything. The & in the formula is joining 2 things together, so the part of the formula that says LEFT(D2,5)&"*" for where D2 is Smith John would be interpreted as Smith*. The wildcard, being at the end, simply says look for items that start with Smith, so Smith John, Smith Bob, Smith Jane would all meet that criteria, since all of them start with Smith. If I changed the 5 to 10, as you had done, it would become Smith John*, and only Smith John would qualify, in this example.