Reporting & Analysis
Dec 4, 2021
TLDR
To efficiently pull multiple corresponding columns of data, you can use array formulas in Google Sheets. Instead of using the regular VLOOKUP, you wrap it with an array formula. This will allow you to pull data for multiple items at once. However, if you need to import data from a different spreadsheet, Airboxr provides an easier solution. It simplifies the process regardless of whether the data is in the same or a different sheet.
When working with data in spreadsheets, a common task I encounter is pulling data from one tab to another. This is a necessary step for merging datasets or conducting a more comprehensive analysis. In Google Sheets, one of the most popular functions for this task is the VLOOKUP formula but it's limitation is that it only allows you to pull one column at a time. This means that if you need to pull multiple columns of data, you would need to replicate the formula across those columns. This not only increases the complexity of your spreadsheet but can also slow down the processing time, particularly with large datasets.
This is where array formulas in Google Sheets come into play. Array formulas allow you to perform operations on multiple cells, making it a powerful tool for data analysis. By designating your VLOOKUP as an array formula, you can pull multiple corresponding columns at once, thereby streamlining your data pulling process.
Consider an example where we want to analyze the performance of our sales representatives, Elise, Ester, and Laurence. We are interested in understanding their daily sales and the cost of goods sold (COGS) for each of them. The raw data is available in a separate tab, and we need to pull the relevant data into our analysis tab.
The raw data might be structured in this way:
Our goal is to transform this raw data into a more concise and readable format that focuses only on the sales representatives and metrics we're interested in. We want the output to look something like this:
To achieve this, we use the array formula with VLOOKUP. In cell B2, we enter the following formula:
=arrayformula(vlookup(A2, Sales!B:G, {5, 6}, 0))
This formula is slightly different from the regular VLOOKUP in two key ways:
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.
