Table of Contents
VLOOKUP and CONCATENATE are two powerful functions in Microsoft Excel that can be used together to efficiently retrieve and combine data from multiple sources. VLOOKUP allows users to search for a specific value in a table and return a corresponding value from a different column, while CONCATENATE allows users to join text from multiple cells into one cell. By using VLOOKUP with CONCATENATE, users can easily pull data from different tables and combine them into one cell, making it a useful tool for data analysis and organization. This combination can be especially helpful when dealing with large datasets or when creating reports that require data from multiple sources. It is a simple and effective way to streamline data management and save time in Excel.
Excel: Use VLOOKUP with CONCATENATE
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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: