You can use the VLOOKUP function with the CONCATENATE function in Excel to look up two values in a range that have been concatenated together and return a corresponding value from another column.
The following example shows exactly how to do so.
Example: How to Use VLOOKUP with CONCATENATE in Excel
Suppose we have the following dataset that contains information about the total sales made by various employees at some company:
Suppose we would like to look up the total sales made by Bob Miller:
Since there are two employees who have a first name of “Bob”, we must first create a helper column that concatenates the first and last name of each employee.
To do so, we can type the following formula into cell A2:
=B2&C2
We can then click and drag this formula down to each remaining cell in column A:
Next, we can use the following formula with the VLOOKUP function to look up Bob Miller and return his sales value:
=B2&C2
The following screenshot shows how to use this formula in practice:
The formula returns a value of 30, which is the correct sales value that corresponds to Bob Miller.
Note: If we simply used a VLOOKUP function to look up “Bob” in the original dataset, the formula would have returned 22 since this is the sales value that corresponds to the first “Bob” in the dataset.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Compare Two Lists Using VLOOKUP
Excel: Return 0 Instead of #N/A with VLOOKUP
Excel: VLOOKUP if Cell Contains Word within Text
