How to use VLOOKUP and IMPORTRANGE in Google Sheets
- An overview of IMPORTRANGE
- Understanding VLOOKUP
- Combining VLOOKUP and IMPORTRANGE
- Going a step further with ARRAYFORMULA
- Cleaning up with ARRAY_CONSTRAIN
- Common use cases
- Using Sheetgo to transfer and process data
- Use VLOOKUP between two Google Sheets
An overview of IMPORTRANGE
Let’s review how IMPORTRANGE works. It’s straightforward and only needs two arguments: the URL of the source spreadsheet and the range to import from it.
=IMPORTRANGE(spreadsheet_url, range_string)
A few pointers to keep in mind:
- You must wrap both arguments in quotation marks.
- You need view access to the source spreadsheet.
- This function will return a REF error the first time it runs. To solve it, click the cell that contains the error and then allow access to the source spreadsheet.
Check out our ultimate guide to IMPORTRANGE.
Understanding VLOOKUP
The VLOOKUP function searches across a range, one row at a time. If it finds a specific value in the first column, it returns a value in the same row from a specified column. As its name indicates, it’s commonly used to look up data.
It takes four arguments:
- search_key: the value to match.
- range: the cell coordinates to search.
- index: the column offset that indicates which values to return.
- [is-sorted]: whether the data is sorted. If unsure, set this to “FALSE” for good measure.
=VLOOKUP(search_key, range, index, [is_sorted])
Check out our deep dive into this function.
Combining VLOOKUP and IMPORTRANGE
Using these two functions together allows us to apply VLOOKUP between two Google Sheets. Something that would be impossible otherwise.
The idea is simple, we just have to use IMPORTRANGE as the VLOOKUP range. Below is the basic syntax for this combination.
=VLOOKUP(search_key, range, index, [is_sorted])
Let’s take a look at what that formula would look like with real information.
=VLOOKUP(A3, IMPORTRANGE(“2JLmFDIKcZPATrjWF_9js8Pvw3d2mWzSzYypB1s4z8uo/edit#gid=1703103179”, “follow_up!A:B”), 2, FALSE))
This approach is good for importing small data sets. However, if working with large spreadsheets, it’s better to use VLOOKUP on the source spreadsheet and import the result.
Going a step further with ARRAYFORMULA
The VLOOKUP can only be applied to one search key at a time. That means you would have to copy-paste it across necessary cells. To avoid this, we can add ARRAYFORMULA.
The formula below is an improved version. It includes this other function and provides a range for the VLOOKUP search key.
=ARRAYFORMULA(VLOOKUP(A3:A, IMPORTRANGE(“2JLmFDIKcZPATrjWF_9js8Pvw3d2mWzSzYypB1s4z8uo”, “follow_up!A:B”), 2, FALSE))
Unfortunately, this new formula also introduces an issue: the arrayformula returns a series of errors if information is missing.
Cleaning up with ARRAY_CONSTRAIN
The errors introduced by the last modification can be fixed by constraining the number of rows in the array. For that we will use ARRAY_CONSTRAIN to add this limitation. Again, the modifications are in bold.
=ARRAY_CONSTRAIN(ARRAYFORMULA(VLOOKUP(A3:A, IMPORTRANGE(“2JLmFDIKcZPATrjWF_9js8Pvw3d2mWzSzYypB1s4z8uo”, “follow_up!A:B”), 2, FALSE)), COUNTA(A3:A), 1)
The only significant change in this formula is the use of COUNTA for the number of rows. This function will count all the cells containing values that VLOOKUP can try to match.
And that’s the final iteration of this formula, these final changes made it simpler and more efficient.
Common use cases
Using both VLOOKUP and IMPORTRANGE can significantly enhance your data management. Here are some use cases that highlight how this combination could be useful.
Employee Information
- Case: Human Resources keeps employee records in one spreadsheet, but each department has separate project assignment sheets.
- Solution: Import employee records into department-specific sheets, then match project assignments with employee details like names and positions.
Inventory Management
- Case: Your company has several warehouses, each maintaining its own inventory spreadsheet.
- Solution: Import data from each warehouse’s sheet into a master inventory tracker, find stock levels and item details across all locations.
Financial Analysis
- Case: You manage multiple client accounts, each with its own financial records spreadsheet.
- Solution: Import and aggregate financial data from all client sheets into one. Afterwards, look up financial metrics to analyze (for example, total revenue, expenses, and profit margins per client).
With the power of VLOOKUP and IMPORTRANGE on your side, you can manage and analyze data across multiple spreadsheets.
Using Sheetgo to transfer and process data
Unfortunately relying heavily on both of these functions can be problematic. Importrange breaks often and slows down spreadsheets.
We have a solution: Sheetgo connections. This feature can transfer data like IMPORTRANGE and process it like VLOOKUP. But it’s more efficient, as it allows you to configure when to transfer the data. It also gives you a lot of processing options and allows you to introduce forms to gather data.
Since Sheetgo is a no-code tool you can forget about using and maintaining all these complicated functions. Just set up your connections and keep important data close at hand.
Cut out data handling tasks and free up time for more important tasks, try out Sheetgo today.
