You can use the following syntax to use the ARRAYFORMULA function with the VLOOKUP function in Google Sheets:
This particular formula searches for the values in E2:E11 in the range A2:C11 and returns the value from the third column in the range.
The benefit of using ARRAYFORMULA is that we can perform a VLOOKUP for each value in E2:E11 without writing an individual VLOOKUP formula for each cell in column E.
The following example shows how to use this formula in practice.
Example: Using ARRAYFORMULA with VLOOKUP in Google Sheets
Suppose we have the following dataset that contains information about various basketball teams:
We can use ARRAYFORMULA with VLOOKUP to look up every team in column E and return their corresponding rebounds values in column F:
We can type this formula one time into cell F2 and press ENTER, and the rebounds values for each team will be returned:
This offers a much quicker way to return the rebounds values for each team as opposed to typing an individual VLOOKUP function in each cell of column F or even clicking and dragging the formula in cell F2 down to each cell in column F.
Note: You can find the complete documentation for the ARRAYFORMULA function in Google Sheets here.
The following tutorials explain how to perform other common operations in Google Sheets: