Table of Contents
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 .