In this tutorial, you will learn how to use ARRAYFORMULA with VLOOKUP in Google Sheets.
How to Use ARRAYFORMULA with VLOOKUP in Google Sheets
Quick Navigation
If you’ve ever tried using VLOOKUP in Google Sheets, then you already know how powerful it can be. However, it can be a bit tricky to use VLOOKUP with large datasets.
Fortunately, Google Sheets has a powerful function called ARRAYFORMULA that can help. ARRAYFORMULA allows you to apply a formula to an entire range of cells, rather than having different formulas to compute each output.
This can be a huge time saver, especially when working with large datasets.
In this guide, we will explain how to use ARRAYFORMULA with the VLOOKUP function in Google Sheets.
How to Return an Array with VLOOKUP in Google Sheets
Here’s how to return an array with VLOOKUP in Google Sheets.
Step 1
Select an empty cell to place the array formula.
In the example above, we have a mailing list that contains information such as the recipients first name, last name, and address details.
In a new table, we want to output the city of the recipient given their last name.
Step 2
Type “=ARRAYFORMULA(“ to start the array formula.
Step 3
Next, we’ll set up the VLOOKUP function inside the ARRAYFORMULA. The setup is mostly the same with the only difference being that the search key will be a range rather than a single value.
In the example above, we indicated that the range H2:H14 will be our search keys. We will find the values in the lookup table B2:F26 and return the corresponding city.
A single formula will output the entire range seen in cells I2:I14.
Summary
This guide should be everything you need to use VLOOKUP with ARRAYFORMULA in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.