Don’t limit your Vlookup experiment to a 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. I hope you don’t confuse with my earlier two advanced Vlookup tutorials handling a similar topic. What are they?
Tips to Use Vlookup to Return An Array Result in Google Sheets
As I have mentioned, you can use the function Vlookup to return an array result as below in Google Doc Spreadsheets.
In Column I3:I, you can see a few names of Sales Persons. The Vlookup formula in the cell J3 searches down these names in the first column of the range A3:G and then returns the result from the second column.
If there is no match, the formula would return blank cell as output. That’s why cell J5 is blank. There is no name with “Gregg Walsh” in the first column of the lookup range.
Of course, I will explain to 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)))
The above formula is equal to;
=ArrayFormula(IFERROR(vlookup({"Ginge Reese";"Roy Cannon";"Gregg Walsh";"Ann Rivera"},A3:G,2,FALSE)))
Formula 2:
Basic Vlookup Formula.
=vlookup(I3,A3:G,2,FALSE)
And the above formula 2 is equal to;
=vlookup("Ginge Reese",A3:G,2,FALSE)
Just compare the formula 1 with the formula 2 and understand the difference.
In Formula 1 I’ve used a column I3:I as the search key column in Vlookup. But in Formula 2 it is just the cell reference I3.
Here the first Vlookup formula uses an array as Search Key and returns an array result. So that we should wrap the Vlookup formula with the ArrayFormula.
I hope you may know the use of IFERROR already. The role of it’s to remove #N/A! or any other errors returned by Vlookup.
If you are just concerned with #N/A! errors, better to use IFNA which is relatively a new function.
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.
For example, the above single Vlookup formula could replace multiple Vlookup formulas.
So learn the advanced use of Google Sheets functions and improve your efficiency in your job.
This article was originally published on Crawland.com.
