Table of Contents
VLOOKUP and CONCATENATE are two useful functions in Google Sheets that can be combined to perform more powerful data analysis. VLOOKUP allows users to search for a specific value in a table and return a corresponding value from a different column. By using CONCATENATE within the VLOOKUP formula, users can combine multiple cells or values together to create a lookup value, making it easier to search for specific data. This combination of functions is especially helpful when dealing with large datasets or when trying to match data from different sources.
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:
The formula returns a value of 30, which is the correct sales value that corresponds to Bob Miller.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets: