Embed IMPORTRANGE in Formulas

10 Jun 2017
functions 2 minute read

How to embed the IMPORTRANGE function within other functions in Google Sheets.


Having mastered using the importrange function to link together disparate spreadsheets, you can also use it as part of a larger formula to provide lookup functionality, without having to import an entire range of data.


Consider the (simplified) situation where you use a code to identify something from a lookup table in a separate spreadsheet. The formulas below assume that this table will be in the first sheet, otherwise, you will need to prefix the range reference with the sheet name (e.g. A:A becomes Sheet1!A:A).

A1Widget Type A (Model 1)
A3Widget Type A (Model 3)
A5Widget Type A (Model 5)
B2Widget Type B (Model 2)
B4Widget Type B (Model 4)
B6Widget Type B (Model 6)

By using an embedded importrange you can access the ‘name’ of a particular part, by the code, in a separate spreadsheet, using the formula below. This assumes that your code is in the first column (second row) of your spreadsheet (A2).


Whilst you can use importrange as part of any formula you wish, although you should read the caveats below first!



When you use first connect to a sheet using importrange you will get an ‘N/A’ error until you perform a simple authorisation. To do this, simply hover over the cell and wait for a message to appear. This should say something like ‘You need to connect these sheets’ and include an ‘Allow Access’ button. Once you click this button, you are authorising the first sheet to connect to the second sheet (as you). This alleviates the need to grant any other users of the first sheet access to the second sheet (useful if you just want to provide a simple filtered view). However, if you use an embedded version of the function, you won’t get the same prompt message, just an error. You need, therefore, to do an initial range import to set up the authorisation before you start writing more complex formulas. Use a function such as this to kick start the process (you can delete it after one run, although you might need to re-run it in the future if you ever need to delete the user which granted the original authorisation).


This technique works well for small scale and infrequent lookups (up to a few hundred rows), but you will see significant performance issues if you try to use it on larger datasets. In these cases, it is much faster to import an entire range into a ‘spare’ sheet of the current spreadsheet and perform lookups against that.

Tagged Google, Sheet

Related Google Calendars on the Web (01 Sep 2018), Protecting Data with Google Drive (14 Aug 2018), Dealing with confidential information in schools (24 Jul 2018)