Table of Contents
The VLOOKUP function in Google Sheets is a powerful tool that enables users to search for specific data within a spreadsheet. When combined with the CONCATENATE function, it allows for the search of data from multiple columns at once. By using CONCATENATE to join multiple cells, the VLOOKUP function can be directed to search for data across multiple columns, making it easier to find and retrieve specific information. This feature is particularly useful for organizing and analyzing large amounts of data, as it streamlines the search process and improves efficiency in data management.
Google Sheets: 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:
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: