Google Sheets IMPORTRANGE: Looking up data with the VLOOKUP function

Note! This is part of a series on using IMPORTRANGE in Google Sheets. If you don’t know how to use IMPORTRANGE or what it is, I encourage you to head back to the first tutorial.

When I first sat down to write this tutorial, I had a specific opinion that one approach to using VLOOKUP on IMPORTRANGE data was better than another. However, I wanted to be certain. I ran some basic tests comparing the two approaches to see how they both perform over large data sets, and you know what? I was surprised to find I couldn’t find a discernable difference in performance.

Next, I reached out to some of the other fellow Google Sheets nerds, who like to go way too far with software for all the wrong reasons, and they seemed to feel that those demi-gods of Google devs really understand the wayward predilections of their users and may have benevolently stored the IMPORTRANGE data locally in the sheet you have imported to.

Will we ever know for certain? Only ever perhaps in the lay of the tea leaves or roll of the bones. We can but only guess the ways of the Googler as they traverse the digital world in all their etheral glory.

What does this mean in short? There are two pretty solid ways to use VLOOKUP with IMPORTRANGE. I’ll cover both in this tutorial.

The Example

Here is a link to the sample data that I am importing if you want to play along:

IMPORTRANGE – VLOOKUP folder

Select the two files. Then right-click > Make a copy and add your very own copy in your desired folder.

Note! There is also a bonus bunch of Easter Eggs, interesting formulas and layouts hidden around these files for the curious. 

In our example, we have a Sales Google Sheet that contains the companies that we sold to, the products of sale, the quantities and the ID of the sales rep who handled the sales.

The rep ID is useful but it really doesn’t tell us all that much about the sales rep. At the very least our sales manager would like to see the name and email of the rep so that they can follow up with them.

Fortunately for us, we have another Google Sheet that has a full list of all the sales reps in the company that are listed by their IDs. I’ve named it … wait for it … Sales Team. (Marketing gold!!!).

Now we could be ding-dongs and manually copy and paste in the sales rep data, but what if that data changes? What if they change their surname or their email or phone number? We would have to change their details in every other document that their information was in. Not efficient at all matey-potatey.

Import the range and then apply VLOOKUP to it

In this scenario, we can create a new Google Sheet tab in our Sales Google Sheet and import the range from our Sales Rep Google Sheet. From there we can use VLOOKUP on that imported range to find the corresponding sales rep ID with their data.

Let’s get into it!

Import the range

Go to your Sales sheet and create a new sheet tab by clicking the plus (+) button down the bottom left next to your other tabs. A new sheet tab will appear. Double click on the text of the new tab and rename it to, “team import”.

We only want to add the sales rep’s name and email into our Sales sheet:

We can find this data in our Sales Rep Google Sheet including the rep ID from ranges A2:C.

In cell A1 of your ‘team import‘ tab, add your IMPORTRANGE.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/yourSheetID/edit","Team!A2:C")

For me this would be:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1RPoN1QrJFb1XE8E4ErZdN3BmiDeVuxlSX7A8sPMtU48/edit","Team!A2:C")

You may get an error message to give permission to access the file first. Follow the instructions to provide access.

Referencing the imported range with VLOOKUP

Now head to the ‘Sales’ tab of your Sales Google Sheet. In cell N2, we will add our first VLOOKUP. It should look like this:

=VLOOKUP(M2,'team import'!$A$1:$C$26, 3, TRUE)

VLOOKUP takes 4 arguments:

1. The item to search. For us, this is the rep ID.
2. The search range. This includes the item to search column and the column we want to find the name in. For us, column A has all the rep ids and column C has their names so we will reference this entire range.
   Note the dollar signs ($) on both the starting and end ranges. These make the range absolute, meaning they are locked in and will not change if you drag them down the sheet.
3. Column position of the range that you want to be displayed. We need to determine the column location in the search range we added in the previous parameter. Counting across from column A (rep id) to column C (rep name) we have 3.
4. The range is ordered ascending by item to search. This is true for us. All of our rep ids are ordered from lowest to highest.

Back in Sales!N2 where your formula is, select the cell and double click the little square in the bottom left of the cell to duplicate the formula down the sheet.

The result will look like this:

To assign the email of the rep for each row in column O of your Sales tab you would then use the following formula:

=VLOOKUP(M2,'team import'!$A$1:$B$26, 2, TRUE)

The emails are on the second column of your range so we only need to select up to column B here. The IDs are still sorted so we can set the final argument of the VLOOKUP to TRUE.

Select cell O2 and double click the little square to duplicate the range all the way down.

Hide the imported data

We don’t need to see our team import sheet tab now that we are all done. We can go ahead and hide the tab. Right-click on the tab and select Hide sheet.

VLOOKUP the IMPORTRANGE directly

We can actually go one further to improve our formula here and use IMPORTRANGE directly inside VLOOKUP. Removing the need to create an extra Google Sheet tab.

Now, if you are worried, like I was, that this approach would heavily slow down your formula, I can say that after some basic testing that there doesn’t seem to be a visible downgrade in performance using this approach. My best guess is that the range is being cached so that all the IMPORTRANGE formulas reference this cache first and not directly to the originally imported document each time. Ingenious stuff it this is really what is going on behind the scenes.

So how do we implement this? Well, it’s really simple. We replace the second argument of the VLOOKUP function with our IMPORTRANGE function:

Here is what our Rep Name ( Col N)  and Rep Email (Col O) would look like:

Rep Name (Col N)

=VLOOKUP(M2,IMPORTRANGE("your URL here","Team!A2:C"),3, TRUE)

For example:

=VLOOKUP(M2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1RPoN1QrJFb1XE8E4ErZdN3BmiDeVuxlSX7A8sPMtU48/edit","Team!A2:C"),3, TRUE)

Rep Email (Col O)

=VLOOKUP(M2,IMPORTRANGE("your URL here","Team!A2:C"),3, TRUE)

For example:

=VLOOKUP(M2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1RPoN1QrJFb1XE8E4ErZdN3BmiDeVuxlSX7A8sPMtU48/edit","Team!A2:C"),3, TRUE)

That’s it. Easy, hey?

Futureproofing and simplifying the admin of your formula

So these two approaches are fairly simple to handle, but what if we want to add more sales to our Sales sheet tab? We would have to go in and manually drag the formulas down.

Ugh! boring!

via GIPHY

 Drag the formula all the way down

One approach would be to drag your formula all the way down to the bottom of the page so that when a new sale is made the rep name and email is updated automatically.

One problem here is that you will see an ugly error.

We can resolve this by adding the IFNA function to the formula.

=IFNA(VLOOKUP(M2,IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1RPoN1QrJFb1XE8E4ErZdN3BmiDeVuxlSX7A8sPMtU48/edit”,“Team!A2:C”),3, TRUE))

Now you won’t see the messy #N/A at the bottom, if you don’t have a rep ID in Col M. Likewise if there is a rep number that can’t be found in the Sales Rep Google Sheet, the associated cells in Col N and O will be blank too.

This is generally a good enough warning that something is wrong in most cases. However, if you want to see an error when you get a dud ID you could do something like this:

=IF(M2 = “”,””, VLOOKUP(M2,IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1RPoN1QrJFb1XE8E4ErZdN3BmiDeVuxlSX7A8sPMtU48/edit”,“Team!A2:C”),3, TRUE))

Here we use the IF function. It states that if there is nothing in M2, then display nothing in the current cell. Otherwise, run the formula.

Just two formulas

We can take this to an even more convenient level. With a little Google Sheets magic, we can add two formulas to Row 2 and not have to worry about manually duplicating the formula all the way down the sheets.

Enter ARRAYFOMULA.

The ARRAYFORMULA function allows you to apply a formula across an array. In our example, we want to apply the same two formulas down our rows in columns N and O.

Go ahead and delete out all the formulas from M2:N (if you don’t you will get a ref# error).

Here is what our formula would look like for Column N:

=ARRAYFORMULA(IFNA(VLOOKUP(M2:M,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1RPoN1QrJFb1XE8E4ErZdN3BmiDeVuxlSX7A8sPMtU48/edit","Team!A2:C"),3, TRUE)))

Note that the first argument of the VLOOKUP change from M2 to the range M2:M. This is because our ARRAYFORMULA will iterate through each row applying the formula to your selected range.

Apply this formula to column O changing the column position argument (the third VLOOKUP argument to ‘2’) and you are all done. Now you only have two formulas to modify if you need to in future.

=ARRAYFORMULA(IFNA(VLOOKUP(M2:M,IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1RPoN1QrJFb1XE8E4ErZdN3BmiDeVuxlSX7A8sPMtU48/edit”,“Team!A2:C”),2, TRUE)))

Insert yet another Highlander Joke here.

Yeah, yeah. There can be only one.

Generally, I am pretty happy with leaving my ARRAYFORMULA set in each column. The formula is still pretty clear and easy to maintain.

However, I know some of you just can’t get enough of combining formulas into one big rat king of a formula. So I present to you the double VLOOKUP-IMPORTRANGE combo.

=ARRAYFORMULA(
  {
IFNA(VLOOKUP(M2:M,IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1RPoN1QrJFb1XE8E4ErZdN3BmiDeVuxlSX7A8sPMtU48/edit”,“Team!A2:C”),3, TRUE)),

IFNA(VLOOKUP(M2:M,IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1RPoN1QrJFb1XE8E4ErZdN3BmiDeVuxlSX7A8sPMtU48/edit”,“Team!A2:C”),2, TRUE))
}
)

Here, we have encapsulated the two formulas into an array using curly braces ({}). We then wrap this in our ARRAYFORMULA. Remove the other formula in cell O2 and then add this formula replacing the URL with your own in cell N2.

Conclusion

While this approach is pretty solid for small data sets extracted from an IMPORTRANGE we can look to other functions to extract more elaborate data from our imported range.

In the next tutorial, we will cover how to use the FILTER and QUERY functions on IMPORTRANGE. To keep up to date when the next tutorial will be released subscribe (below). 

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

 

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

The IMPORTRANGE series

• Introduction to IMPORTRANGE.
• Google Sheets IMPORTRANGE: Prevent clever editors from accessing other ranges of your imported sheet
• Google Sheets IMPORTRANGE: Using FILTER and QUERY on imported Google Sheets data. 🚧🚧🚧  under construction 🚧🚧🚧

~Yagi