Google Sheets: How to Use VLOOKUP with CONCATENATE


You can use the VLOOKUP function with the CONCATENATE function in Google Sheets 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 how to do so in practice.

Example: How to Use VLOOKUP with CONCATENATE in Google Sheets

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:

=VLOOKUP(F2&G2, A2:D10, 4, FALSE)

The following screenshot shows how to use this formula in practice:

Google Sheets VLOOKUP with CONCATENATE

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 Google Sheets:

Google Sheets: How to Use VLOOKUP with IF Statement
Google Sheets: How to Use VLOOKUP with COUNTIF
Google Sheets: How to Use VLOOKUP to Return Max Value

Leave a Reply

Your email address will not be published. Required fields are marked *