Two lists of Names that are jumbled up [Closed]

Report
-
Posts
4
Registration date
Thursday June 2, 2016
Status
Member
Last seen
June 2, 2016
-
Hello,

How are you all?

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

Any ideas?

Much appreciated.

Thanks



1 reply

Posts
4
Registration date
Thursday June 2, 2016
Status
Member
Last seen
June 2, 2016

Hi Johnny,

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:

=VLOOKUP(B4,E:F,2,FALSE)

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

Source: http://www.excelvlookuphelp.com/tutorial-vlookup-and-the-wildcard-characters-and-for-partial-matches/

The other example that you will find useful is on the following post:

http://www.excelvlookuphelp.com/how-do-i-do-a-vlookup-with-multiple-values/