How to use VLOOKUP and IMPORTRANGE in Google Sheets

Get a selection of expert articles

Combining the power of VLOOKUP and IMPORTRANGE is a great way to transfer specific data between two spreadsheets. Expand the horizons of both functions and apply vlookup across Google Sheets.

Continue reading to use VLOOKUP across Google Sheets and discover how Sheetgo can provide a great alternative.

Make your data work together

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])

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.

    Spreadsheets are just the start

    Use VLOOKUP between two Google Sheets

    Now you know how to put these two great functions together. For a related set of complementary functions, check out how to combine QUERY and IMPORTRANGE.

    Ready to streamline your spreadsheet data?

    You may also like…