Embed IMPORTRANGE in Formulas

• Introduction
• Method
• Caveats

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

Introduction

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.

Method

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

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

INDEX(IMPORTRANGE("SPREADSHEET_ID OR SPREADSHEET_URL", "B:B"), MATCH(A2, IMPORTRANGE("SPREADSHEET_ID OR SPREADSHEET_URL", "A:A"), 0), 1)

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

Caveats

Authorisation

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

IMPORTRANGE("SPREADSHEET_ID OR SPREADSHEET_URL", "A1")

Performance

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.