In Google Sheets, you can combine VLOOKUP with other functions to do some neat things.
For example, combining VLOOKUP with ARRAYFORMULA can be used to lookup multiple pieces of data at once, which will save you steps so you don’t have to enter multiple formulas.
In this tutorial, I will show you how to use an array formula with VLOOKUP in Google Sheets.
Table of Contents
Array Formula VLOOKUP Example
To help you understand how to combine the ARRAYFORMULA and VLOOKUP functions, let’s take a look at this example:
The formula I have used in the example above is:
=ARRAYFORMULA(IFERROR(VLOOKUP(F:F,A3:D,2,FALSE)))
In column F, I have the values that I am looking up in my table that exist in A3:D. The formula is set up to find the matching name in the table return the data that exists in 2nd column.
The benefit of using an array formula for this is that I only have to enter one formula to easily search all my data in column F. Normally, I would have to place an individual VLOOKUP next to each name I want to search, but now I can do it all in one formula.
Array Formula VLOOKUP Steps
Now I will walk you through each step on how to set this formula up.
Here are the steps:
1. First select that cell where you want to enter the formula and type the equals sign (=) and then type ARRAYFORMULA and press Tab on your keyboard
2. Then type IFERROR and press Tab on your keyboard
3. Next type VLOOKUP and press Tab on your keyboard
4. Now we need to add the values we want to lookup into our formula. I am looking up the values in column F, so I just put the entire column F here. Add a comma (,) when you are done
5. Next, you need to enter where you want to do the lookup. I am looking up in the table that exists at A3:D, so that is what I use in the formula. Add a comma when you are done (,)
6. Next, enter the number for the column that you want to return in your data. I am using 2. Add a comma (,), then type FALSE, enter three closing parentheses “)))” and press Enter to calculate your formula
7. Your VLOOKUP should now perform on all the data that you have in your lookup column from step 4.
Closing Thoughts
Using VLOOKUP together with array formula is a little advanced, but after you break it down to its steps, it’s not that hard to learn.
It can definitely help you to improve your efficiency working with spreadsheets because you can use one formula to search multiple pieces of data.
We hope you found this tutorial helpful!
More Google Sheets Tutorials:
How to Use INDEX and MATCH Together
How to Use ARRAYFORMULA
How to Use the IFNA Function
How to Use the OFFSET Function