Table of Contents
An array formula with VLOOKUP in Excel can be used to retrieve multiple values from a single lookup value. This is done by entering the formula, then pressing Ctrl+Shift+Enter to enter the formula as an array formula. By using an array formula, you can select multiple values from a range of data based on one criteria. This is useful for complex lookups that would otherwise require multiple VLOOKUP formulas.
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.