The hard way and the easy way
When I try to pull data from another tab on my spreadsheet, I typically use the vlookup formula. But the vlookup formula only pulls one column at a time. This forces me to replicate the formula in multiple columns, only for my analysis to slow down.
The array formula
Thankfully, Google has array formulas to make the data pull less painful. By designating your vlookup as an array formula, you can pull multiple corresponding columns.
We want to know how much our sales reps—Elise, Ester, and Laurence—have done in daily sales and what the cost of goods sold was for each of them.
Let's say we have the raw data in a format like this. We only need to pull the data for the three sales reps we're interested in.
Let's look at the format we want the output to be in. We have the three sales reps as well as daily sales and COGS in a new table shown below:
We now want to fill Daily Sales and COGS. So let's type this into B2:
=arrayformula(vlookup(A2, Sales!B:G, {5, 6}, 0))
You can see here that the format is slightly different from the regular vlookup:
- We've wrapped the vlookup around an arrayformula
- Instead of one column index (the column number of the column we want to pull), we have entered two column indexes and wrapped them inside {}'s.
Notice how cell B2 has a formula and C2 doesn't. That is because the array formula is running from B2.
Now simply drag the formula from B2 down to B4 and you're done.
Believe it or not, there's an easier way.
One challenge with the process above is that it only works if both your data source and the analysis sheet belong to the same Google spreadsheet. If you need to import the information from a different spreadsheet, then the process gets too complicated.
Let's see how it looks with Airboxr. We will start with a fresh sheet and will paste the names of the sales reps on column A.
This is just like how we started out earlier—but note how we haven't bothered with adding headers on columns B and C. Saved three seconds there!
Now, we launch Airboxr and start our lookup flow: simply choose the data source which has the sales information you want to look up.
Now, choose the columns you wish to pull (you can choose as many as you want from the source table, but I will be choosing Daily Sales and COGS for consistency).
In the next step, we will match the keys: we are telling the add-on which column (Sales Representative) on the active sheet should it be matching against which column (sales_rep) the source data.
That's all! ⚡️
With Airboxr, you need not worry if the data is in the same sheet or a different one. As long you have view access to the source sheet, you can use lookup to copy any number of new columns into your sheet.
Give it a shot yourself. If you haven't already downloaded the add-on—do it now. The add-on is free until December 31st, 2020. And if you sign up now, we will hook you up with a sweet discount when we go paid.