How to Use ARRAYFORMULA with VLOOKUP in Google Sheets

Using ARRAYFORMULA with VLOOKUP in Google Sheets allows you to perform VLOOKUP on a range of cells without having to copy the formula across each cell. ARRAYFORMULA can apply the VLOOKUP function to an entire column or array of data, saving you time and reducing the risk of errors.

Here's how to use ARRAYFORMULA with VLOOKUP in Google Sheets:

1. Set up your data: Ensure your data is organized in a clear and structured way. You'll need a lookup table with the data you want to search through and a separate column or table where you'll input the search keys and display the results.
2. Prepare the lookup table: Make sure the lookup table has a unique identifier in the first column (e.g., ID number, name, etc.) and the desired data in the subsequent columns.
3. Start the formula: In the cell where you want the first result to appear, type =ARRAYFORMULA( to begin your formula.
4. Enter the VLOOKUP function: Inside the ARRAYFORMULA function, type VLOOKUP( to start the VLOOKUP function.
5. Specify the search range: Select the range of cells containing the search keys as the first argument for VLOOKUP. Make sure to include the entire range you want to apply the VLOOKUP to.
6. Add a comma: Type a comma , to separate the first argument from the second.
7. Select the lookup table: Click and drag to select the entire lookup table as the second argument for VLOOKUP.
8. Add another comma: Type a comma , to separate the second argument from the third.
9. Specify the column index: Enter the column number of the data you want to return from the lookup table as the third argument for VLOOKUP. The first column has an index of 1, the second column has an index of 2, and so on.
10. Add another comma: Type a comma , to separate the third argument from the fourth.
11. Specify the search type: Enter FALSE as the fourth argument for VLOOKUP, which indicates that you want an exact match for the search key.
12. Close the VLOOKUP function: Type a closing parenthesis ) to close the VLOOKUP function.
13. Close the ARRAYFORMULA function: Type another closing parenthesis ) to close the ARRAYFORMULA function.
14. Press Enter: Press Enter to apply the formula to the specified range.

Example

Suppose you have a list of products with their ID, name, and price. You want to find the price for a range of product IDs.

1. Set up your data:

In another part of the sheet, you have a list of product IDs that you want to look up:

2. In cell F1, where you want the first result to appear, enter the following formula:

=ARRAYFORMULA(VLOOKUP(E1:E3, A1:C3, 3, FALSE))

3. Press Enter. The formula will now apply VLOOKUP to the entire range E1:E3, and the results will appear in the corresponding cells in column F: