The ExcelHelpDesk Support team received the following request for help
Problem – Matching Names and Numbers
I have two separate spreadsheets. One with a list of names and numbers and one with a list of some of the names on the first list.
I want to tell it to match the names on the spreadsheets and if they match, then put the number on page one next to the name on page two.
Is this possible?
For this problem we received some sample workbooks. We then used some formulas to compare the information and match the number to the name. See below the sample workbooks received for this request.
Step 1 – Create a Unique Name to Compare
To allow a direct comparison between the worksheets we need to create a “unique” name that can be easily compared. To acheive this we will use the CONCATENATE formula in both workbooks. The “unique” name will be a concatenation of Last Name and First Name from column A and B. To start with we insert a new column in sample 1 workbook before column C and then include the formula
Step 2 – Drag down and include Unique Name in other Workbook
Now we need to drag down that formula in sample 1 workbook and then also include the formula in the sample 2 workbook. For the sample 2 workbook include the formula in column C directly and then drag it down for the other rows in that workbook. See below for the resulting sample workbooks after creating this Unique name in each.
Step 3 – vLookup to find the number for the name
Now we need to create lookup for the number and name in sample 1 workbook and if it is found display it in the sample 2 workbook next to the name. To do this we create the following formula in the sample 2 workbook.
=VLOOKUP(C1,'[excel example 1.xlsx]Sheet1′!$C$1:$D$8,2,FALSE)
For this formula the parameters are
Lookup Value: C1 Concatenated Unique Name in Sample 2 workbook
Table Array: Column C & D in Sample 1 workbook
Col Index Number: 2 to bring back the number
Range Lookup: False to perform an exact match
Once the formula is included to the first row drag it down to all the rows and the matching will auto complete and return the number for the name found. If it is not found an #N/A will be returned.
We now have a matching value in the sample 2 workbook for the name found in sample 1 and the number. This approach can be used in many situations where you want to match data between separate lists. The key part of the matching is determine what can be the unique match between the 2 sets of data and then creating the lookup.
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment