How to Use IMPORTRANGE Function with Conditions in Google Sheets

0
126
IMPORTRANGE Function with Conditions

You can’t directly use conditions in Google Sheets IMPORTRANGE function. But you can extensively use IMPORTRANGE Function with conditions in Google Sheets with the help of QUERY Function. You must know this simple trick of using IMPORTRANGE together with QUERY to become a pro Google Doc user.

This Google Sheets tutorial is for those who are already familiar with QUERY and IMPORTRANGE functions. If you are not familiar with any of the said functions, go to the concerned tutorials below.

Learn Google Doc QUERY Function.

Learn Google Doc IMPORTRANGE Function.

Steps to Use IMPORTRANGE Function with Conditions in Google Sheets

To give you a clear picture of this combination, I’m providing you a very simple example. I will explain you how to join the two formulas together. It’s important that you should already know the use of both IMPORTRANGE and QUERY formulas. So let us begin.

We have two Google Sheet Files. They are TestA and TestB. While TestA contains some data, TestB contains only one blank sheet. TestB is purely for importing data from TestA.

Example:

Here is our sample data in TestA.

query - importrange combination

Now in TestB we are importing the above data based on condition.

Normally without applying any condition we can import all the data with IMPORTRANGE formula in TestB as below.

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1W1UNLcVHSpd2DHpKtF7MDq0MI3vrVM40KR-9HfSjpIw/edit#gid=1641552592″,”Sheet1ofTestA!A1:G9”)

This formula will import all the content in the file TestA to TestB. Now I want to restrict the imported data with filtering. How? For that we need to use QUERY together with IMPORTRANGE.

Here I want to only import data from TestA to TestB for column A values in TestA are equal to “Safety Helmet”

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

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

Now we have two formulas. So it’s time to combine the above two formulas. How to do that?

First replace the above Yellow coloured highlighted part of the Query formula with the first IMPORTRANGE formula. Now the 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'”)

When you use QUERY formula in combination with IMPORTRANGE, in Query ‘where’ clause, use Col1, Col2 etc. instead of A, B etc.

Conclusion

This way you can use IMPORTRANGE function with conditions in Google Sheets. You can apply multiple filtering in IMPORTRANGE, if you are familiar with QUERY. Even if you know the use of QUERY, you are going to falter at one place. That is the date filtering part. So check this Query formula Date Criteria examples.

Similar: Convert Date to String Using the Long-winded Approach in Google Sheets

LEAVE A REPLY

Please enter your comment!
Please enter your name here