HomeGoogle DocsSpreadsheetHow to Use IMPORTRANGE Function with Conditions in Google Sheets

How to Use IMPORTRANGE Function with Conditions in Google Sheets

Published on

You can’t directly use conditions in the Google Sheets IMPORTRANGE function. However, you can extensively use the IMPORTRANGE function with conditions in Google Sheets with the help of the QUERY Function.

You need to know this simple trick of using IMPORTRANGE together with QUERY to become a proficient Google Sheets user.

This Google Sheets tutorial is intended for those who are already familiar with the QUERY and IMPORTRANGE functions. If you are unfamiliar with the two functions mentioned, please refer to the linked tutorials below.

  1. Learn Google Sheets QUERY Function.
  2. Learn Google Sheets IMPORTRANGE Function.

Steps to Use IMPORTRANGE Function with Conditions in Google Sheets

To give you a clear picture of this combination, I will provide you with a straightforward example. I will explain how to join the two formulas together.

You must already be familiar with the use of both the IMPORTRANGE and QUERY formulas. You can learn how to use them in my function guide. So let’s get started.

We have two Google Sheets files. One is called “TestA” and the other is called “TestB.” “TestA” contains some data, while “TestB” is blank. “TestB” is used solely to import data from “TestA.”

Example:

Here is our sample data in the sheet “TestA.”

query - importrange combination

Now, in “TestB,” we are importing the above data based on a condition.

Usually, without applying any conditions, we can import all the data from “TestA” to “TestB” with the IMPORTRANGE formula, as shown below.

=IMPORTRANGE("HTTPS://DOCS.GOOGLE.COM/SPREADSHEETS/D/1W1UNLCVHSPD2DHPKTF7MDQ0MI3VRVM40KR-9HFSJPIW/EDIT#GID=1641552592","SHEET1OFTESTA!A1:G9")

This formula is as per the following IMPORTRANGE syntax:

IMPORTRANGE(spreadsheet_url, range_string)

Note: When you use this formula, replace the spreadsheet URL and range string arguments with the corresponding ones from your sheet.

This formula will import all the content from the Google Sheets file “TestA” to “TestB”.

Now, I want to restrict the imported data with filtering. How can I do that? For that, we need to use the QUERY function together with the IMPORTRANGE function.

Here, I want to only import data from “TestA” to “TestB” if the values in column A of “TestA” are equal to “Safety Helmet”.

First, let us see how to use the QUERY formula independently to filter the above data. Here is that formula.

=QUERY(A1:G9,"where A='Safety Helmet'")

Now, we have two formulas: one IMPORTRANGE formula and one QUERY formula. So, it’s time to combine them. How do we do that?

Changes When Using IMPORTRANGE as the Data in QUERY

Now, see how the IMPORTRANGE function is used in the QUERY function.

First, replace the pale pink colored highlighted part of the QUERY formula with the first IMPORTRANGE formula and wrap it with curly brackets. Then, replace the pale cyan blue highlighted A with Col1. Here it is.

=QUERY({IMPORTRANGE("HTTPS://DOCS.GOOGLE.COM/SPREADSHEETS/D/1W1UNLCVHSPD2DHPKTF7MDQ0MI3VRVM40KR-9HFSJPIW/EDIT#GID=1641552592","SHEET1OFTESTA!A1:G9")},"where Col1='Safety Helmet'")

Points to be noted:

When you use the QUERY function in combination with IMPORTRANGE, you only need to change the column identifiers in QUERY.

That means, in the QUERY where clause, use Col1, Col2, etc. instead of A, B, etc. The first column in the imported range will be Col1 regardless of whether you are importing the range SHEET1OFTESTA!A1:G9 or SHEET1OFTESTA!B1:G9.

Conclusion

This way, you can use the IMPORTRANGE function with conditions in Google Sheets. You can apply multiple filters in IMPORTRANGE if you are familiar with QUERY. If not, you can find plenty of QUERY formulas on this site. Just search for them.

Even if you know how to use QUERY, you will eventually hit a roadblock. That roadblock is date filtering. So please check out these Query formula Date Criteria examples.

  1. How to Use Date Criteria in Query Function in Google Sheets.
  2. Convert Date to String Using the Long-winded Approach in Google Sheets.
  3. Examples of the Use of Literals in Query in Google Sheets.
  4. Simple Comparison Operators in Sheets Query.
  5. How to Sum, Avg, Count, Max, and Min in Google Sheets Query.
  6. How to Use Arithmetic Operators in Query in Google Sheets.
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

58 COMMENTS

  1. Hi! I am only able to get the first row of data to appear in the new spreadsheet, none of the other data is being transferred.

    =QUERY(IMPORTRANGE("URL","codes!B2:E200"),"SELECT Col1,Col2, Col3, Col4 where lower(Col1)='y'",1)

    Thanks for your help!

  2. Hey Prashanth,

    I am trying to use the following code, but I am still getting an Error: Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col7

    =QUERY(IMPORTRANGE("URL","Sheet1!E2:E2000"),"where Col7='YouTube'")

  3. Hi Prashanth,

    Thank you for this tutorial. I am hoping you are still checking comments. I was able to get this to work perfectly using the formula below:

    =QUERY(IMPORTRANGE("URL","2023 DATA COLLECTION!A3:Y7"),"where Col1='Bob.S.'")

    Now, I am trying to find a way to select Bob.S. in a drop down (data validation) and have the information import from the main data sheet. Can you tell me if this is possible.

    Thank you for being a such great resource.

    • Hi, Jennell V.,

      If G2 contains the drop-down, then the last part of your formula will be as follows.

      "where Col1='"&G2&"'")

      To learn, please search the term “Literals” within this tutorial.

  4. I’ve just spent a while watching tutorials but still can’t figure out why I am getting an “Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col2” with this formula:

    =QUERY(IMPORTRANGE("URL", "Sheet1!U6:U4426"), "where Col2='Set Value'")

  5. Hello, Thank you for the lesson. It is almost working, but it is importing the first row in my source range (A3) even though it doesn’t meet my criteria- all other rows are correct. Here is my formula:

    =QUERY(IMPORTRANGE("URL","Sheet1!A3:P"),"where Col3='8/313'")

  6. Hi Mr. Prashanth, I am glad I read your post here.

    The Importrange is working like a charm. Unfortunately, the Query which comes after didn’t work.

    My example:

    =IMPORTRANGE("https://docs.google.com/spreadsheets/d/xx/edit#gid=xx","BA 111!C6:O140"),"where Col4='TIADA'")

    • Hi, Ahmad Akmal,

      You have not used the Query function in your formula.

      E.g.

      =query(
      IMPORTRANGE("https://docs.google.com/spreadsheets/d/xx/edit#gid=xx","BA 111!C6:O140"),
      "where Col4='TIADA'"
      )

  7. Hi All,

    Used below formula for my work:

    =IMPORTRANGE("URL_here","d9:d12")

    Any step to change the output to horizontal?

  8. Hi Prashanth,

    I’ve learned so much from your articles, so hats off.

    However, my problem now is to combine the AND function with IMPORTRANGE.

    I am trying to import the data from a source workbook on the condition that a cell in the destination workbook contains my name. Is this possible?

    If not, could you suggest an add-on that executes this task? Here is the formula:

    =AND(IMPORTRANGE("12hHtTrXsL1pL_Fz7dH-ZejG4srs8DSTfxmKUAeEfh64","02-02-2021!D276:$K"), $B:B="Paul Salcedo",)

    Also, let me know if there’s anything wrong with this formula.

    Paul

    • Hi, Paul Salcedo,

      It is possible. I am unable to write the formula because you have not specified the cell that contains your name in the destination sheet. Also, in the source, which column contains your name.

      I assume you want to import the data if column 2 (column B) in your source contains your name.

      So the formula must be something like this-

      =Query(IMPORTRANGE("12hHtTrXsL1pL_Fz7dH-ZejG4srs8DSTfxmKUAeEfh64","02-02-2021!A276:K"),"Select * where Col2='Paul Salcedo'")

      This may help you – How to Use Query with Importrange in Google Sheets.

  9. Hi, I am trying to do this exact thing but I want to translate this data between different tabs in one Google Sheet but only one column of the information if a different column is a certain value.

    So if column D in sheet 1 says “Jane Doe”, I want it to populate the information from column B into sheet 2

    B | C | D | E
    Frog | Test | Jane Doe | Blue
    Cat | Test | Jane Doe | Orange
    Bear | Test | John Doe | Yellow

    So I want when it says Jane Doe to populate the animals into a column on sheet 2 and nothing else.

    • Hi, Brooke,

      In Sheet2, try this formula.

      =query(Sheet1!B1:E,"Select B where D='Jane Doe'")

      In this formula, if the sheet name is “Sheet 1” not “Sheet1”, then use it as below.

      =query('Sheet 1'!B1:E,"Select B where D='Jane Doe'")

LEAVE A REPLY

Please enter your comment!
Please enter your name here