Often you may want to use the VLOOKUP function in Excel to return multiple values vertically.
Unfortunately, the VLOOKUP function can only look up some value in a range and return a corresponding value only for the first match.
However, you can use the following syntax to look up some value in a range and return multiple values vertically:
=FILTER(B2:B12, D2=A2:A12)
This particular formula returns all values from the range B2:B12 where the corresponding value in the range A2:A12 is equal to the value in cell D2.
The following example shows how to use this formula in practice.
Example: Use VLOOKUP to Return Multiple Values Vertically
Suppose we have the following dataset in Excel that contains information about points scored by basketball players on various teams:
Suppose we would like to look up “Mavs” in the team column and return each points value vertically.
We can type the following formula into cell E2 to do so:
=FILTER(B2:B12, D2=A2:A12)
The following screenshot shows how to use this formula in practice:
The formula correctly returns the values 22, 15, and 30.
Notice that each of these values represents a value in the points column that corresponds to the “Mavs” in the team column:
Note: You can find the complete documentation for the FILTER function in Excel here.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Use VLOOKUP to Return All Matches
Excel: Use VLOOKUP to Return Blank Instead of Zero
Excel: How to Use TRUE or FALSE in VLOOKUP
