You can use the following basic syntax to perform a VLOOKUP with an array formula in Excel:
=VLOOKUP(E2:E8,A2:C8,3,FALSE)
This particular formula will return the values in the third column of the range A2:C8 where the values in the range E2:E8 match the values in the range A2:A8.
The following example shows how to use this formula in practice.
Example: Use Array Formula with VLOOKUP in Excel
Suppose we have a dataset in the range A2:C8 that contains information about various basketball teams and suppose we would like to look up the value in the “Rebounds” column for each team in the range E2:E8:
If we use VLOOKUP with one cell, we can look up the “Rebounds” value for the Kings:
=VLOOKUP(E2,A2:C8,3,FALSE)
However, we would have to type this formula multiple times to look up the “Rebounds” value for each team in column E.
Instead of providing one cell value to the VLOOKUP function, we could instead provide the entire range of cells in column E to look up the “Rebounds” value for each team all at once.
To do so, we can type the following formula into cell G2:
=VLOOKUP(E2:E8,A2:C8,3,FALSE)
Once we press Enter, the “Rebounds” value for each team will be returned at the same time:
The benefit of using this approach is that we don’t have to type out the VLOOKUP formula multiple times or click and drag the formula to multiple cells in order to return multiple values.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Use VLOOKUP From Another Workbook
Excel: How to Use VLOOKUP to Return All Matches
Excel: How to Use VLOOKUP to Return Multiple Columns