Table of Contents
VLOOKUP is a powerful function in Excel that allows users to search for a specific value in a table and return a corresponding value from a different column. While it is commonly used to retrieve a single value, it is also possible to use VLOOKUP to return multiple values in one cell. This can be achieved by using the INDEX and MATCH functions in conjunction with VLOOKUP. By combining these functions, users can create an array formula that will return all matching values in a single cell. This can be useful when working with large datasets and needing to quickly display multiple related values. With the proper understanding of these functions, users can efficiently use VLOOKUP to return multiple values in one cell in Excel.
Excel: Use VLOOKUP to Return Multiple Values in One Cell
Often you may want to use the VLOOKUP function in Excel to return multiple values in one cell that match a specific lookup value.
Unfortunately, the VLOOKUP function is only designed to return the first matching value, but you can use the TEXTJOIN function with the following syntax to return multiple values in one cell:
=TEXTJOIN(", ",,IF($A$2:$A$12=D2,$B$2:$B$12,""))
This particular formula looks up the value in cell D2 in the range A2:A12 and returns all corresponding matching values in the range B2:B12.
The following example shows how to use this syntax in practice.
Example: Use VLOOKUP to Return Multiple Values in One Cell
Suppose we have the following dataset in Excel that shows the number of points scored by basketball players on various teams:
Suppose we would like to look up the name “Mavs” in the Team column and return all matching values from the Points column in one cell.
We can type the following formula into cell E2 to do so:
=TEXTJOIN(", ",,IF($A$2:$A$12=D2,$B$2:$B$12,""))
The following screenshot shows how to use this formula in practice:
The formula returns all of the matching values from the Points column for each row where the Team column is equal to Mavs:
Note that we specified a comma to be used as the delimiter in the TEXTJOIN function, but we could choose to use any other delimiter that we’d like.
For example, we could use the following formula to use a space as a delimiter instead:
=TEXTJOIN(" ",,IF($A$2:$A$12=D2,$B$2:$B$12,""))
The following screenshot shows how to use this formula in practice:
This formula now returns all of the matching values from the Points column for each row where the Team column is equal to Mavs, with each value separated by a space.
Note: You can find the complete documentation for the TEXTJOIN function .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: