Home / Formulas / vLookup and Match Names and Numbers

vLookup and Match Names and Numbers

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

=CONCATENATE(A1,B1)

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

About Excel Help Desk

Check Also

INDIRECT to extract a value from another worksheet

You can see that we have the Worksheet name in Column A and the value that is needed to be extracted in Column D. The supplied worksheet included a manually entered formula to pull through the value from the other worksheet. We will now demonstrate how to create a formula using the INDIRECT function that will automatically pull through values from other worksheets without manually entering each worksheet name and cell reference.