Table of Contents
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.