I have two lists of names, one has all the names (A) e.g. Michael Luis Stevens, the other does not or the name is jumbled up (B) e.g. Stevens L Michael, i want to be able to match them both up so that i can apply a vlookup function in order to see their relative pay or super.
So that both columns of names are exactly the same
Here are a couple of articles from excelvlookuphelp.com which you might find useful. The first is on how to look up part of a string (in case your names aren't in multiple columns (eg. one column for first name, one for surname).
There are two wildcard characters in Excel:
an asterisk (*) which matches one or more characters, and
a question mark (?) matches a single character.
To use the asterisk character, we’ll use student names and numbers as a worked example.
In the first case (Cell B4 in online example), we’ll show that a standard vlookup can be used and the asterisk (*) can be used in the SearchFor cell B4
In the second case, we’ll show you how to build the wildcard characters into your formula so you only need to type in the part of the string to search for in column B (see cell B5 in the online example)
excel vlookup partial match
So the formula in Scenario A (cell C4 in the online example) is our standard:
And the formula in Scenario B (cell C5) includes concatenation, using the & to join the asterisks to the SearchFor term to the wildcard asterisks
=VLOOKUP(“*” & B5 & “*”,E:F,2,FALSE)
A couple of things to note:
If there are multiple matches, vlookup will return only the first one
You can do the same as the above for the question mark (?) wildcard character but you need to enter one question mark for each missing character in the search term so it’s more precise but also more fiddly to get right