How to Use ARRAYFORMULA with VLOOKUP in Google Sheets

You can use the following syntax to use the ARRAYFORMULA function with the VLOOKUP function in Google Sheets:

=ARRAYFORMULA(VLOOKUP(E2:E11,A2:C11,3,FALSE))

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:

=ARRAYFORMULA(VLOOKUP(E2:E11,A2:C11,3,FALSE))

We can type this formula one time into cell F2 and press ENTER, and the rebounds values for each team will be returned:

ARRAYFORMULA with VLOOKUP in Google Sheets

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.