How to Use Vlookup to Return An Array Result in Google Sheets

0
175
Use Vlookup to Return An Array Result in Google Sheets

Don’t limit your use of Vlookup to single search key and single cell result. You can do a lot more with Vlookup. Here we can learn the tips to use Vlookup to return an array result in Google Sheets. Hope you don’t confuse with my earlier two advanced Vlookup tutorials handling similar topic. What are they?

  1. How to Return Multiple Values Using Vlookup in Google Sheets? – Here the Vlookup formula uses one Search Key to find a match in the first column, and returns multiple values from the same row of the match found.
  2. How to Use VLOOKUP with Multiple Criteria in Google Sheets? – In this case, we are making use of a helper column to combine values in two columns and use this column as our Vlookup first column to lookup. So the Vlookup search key would also be a combined key.

But in this tutorial, I am using an entire column as search key with the help of ARRAYFORMULA and find all matches in the first column of another data set and returns the result as an array. You can easily grasp this point from the example below.

Similar: Case Sensitive Vlookup in Google Sheets

Tips to Use Vlookup to Return An Array Result in Google Sheets

As told, you can use the function Vlookup to return an array result as below in Google Doc Spreadsheets.

ARRAY-result-using-VLOOKUP

In Column I3:I, you can see few names of Sales Persons. The Vlookup formula there would look for these names in the first column of the range A3:G and returns the result from the second column. If there is no match, the formula would return blank. That’s why J5 is blank. There is no name with “Gregg Walsh” in the first column of the lookup range.

Of course I will explain you how this Vlookup Array Formula is different from the normal one, which you may be familiar with.

Formula 1:

Vlookup to Return An Array Result in Google Sheets

=ArrayFormula(IFERROR(vlookup(I3:I,A3:G,2,FALSE)))

Formula 2:

Basic Vlookup Formula.

=vlookup(I3,A3:G,2,FALSE)

Here you can read the formula 1 as below.

=ArrayFormula(IFERROR(vlookup({“Ginge Reese”;“Roy Cannon”;“Gregg Walsh”;“Ann Rivera”},A3:G,2,FALSE)))

And formula 2 is like;

=vlookup(“Ginge Reese”,A3:G,2,FALSE)

Here the first Vlookup formula uses an array as Search Key and returns an array result. Hope you know the use of IFERROR already. This way, you can use Vlookup to return an array result in Google Sheets.

Conclusion:

By learning advanced tips of using functions like Vlookup, Sumproduct, Sumif and Query you can save lots of man hours. The above single Vlookup formula could replace multiple Vlookup formulas. So learn advanced use of Google Sheets functions and improve your profit from business.

LEAVE A REPLY

Please enter your comment!
Please enter your name here