IMPORTRANGE with condition

Question

Using Google Sheets I want, within the same document, to import data from one sheet to another using IMPORTRANGE with conditions.

I have tried unsuccessfully:

=IF(IMPORTRANGE("https:URL","Inc Database!B2:B300")="permanent",IMPORTRANGE("htps://URL","Inc Database!A2:A300"),"")

and

=QUERY(IMPORTRANGE("https:/URL", "Inc Database!A2:A300"),"SELECT Col1 WHERE Col1 <> 'permanent'")

and

=FILTER(IMPORTRANGE("URL","Inc Database!A1:A250"),IMPORTRANGE("URL","Inc Database!B1:B250"="venture permanent"))

I want the function to say: Import any values from range A that meet criterion "permanent" in range B.

    A    |  B    
_________|_________
 Name    |type 
---------|-------
 Henry   |Permanent
 William |Intern
 John    |Permanent

David Tew · Accepted Answer · 2017-01-16 16:03:38Z

3

I have put a few examples in the following spreadsheet:

e.g. =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1LX7JfbGvgBTfmXsYZz0u5J7K0O6oHJSnBowKQJGa9lY/edit#gid=0", "Inc Database!A2:B300"),"SELECT Col1 WHERE not(Col2 = 'Permanent') ")

answered Jan 16, 2017 at 16:03

David Tew

1,4691 gold badge11 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user7220901 Over a year ago

Thanks a lot, it does work. However, only one name is loaded whereas 3 meet the criteria. When i expand the formula CTRL + D, only the same name appears. Any solutions for that as well? I used this formula: =FILTER(IMPORTRANGE("docs.google.com/spreadsheets/d/…", "Inc Database!A2:A300"),REGEXMATCH(LOWER(IMPORTRANGE("docs.google.com/spreadsheets/d/…", "Inc Database!B2:B300")),"permanent"))

David Tew Over a year ago

I am confused as to what you exactly want to achieve. (I've made the demo ss editable now, so if you want to add some names). The yellow columns are showing those names where Type is Permanent. The others showing where Type is intern (or Not Permanent). Could you explain in a different way what I haven't understood?

user7220901 Over a year ago

I added comment on the sheet., Thks

David Tew Over a year ago

I added a comment to your comment on the sheet.

user7220901 Over a year ago

Thks, so did I again

|

Karl_S · Accepted Answer · 2017-01-16 15:47:26Z

1

You need a single quote around the reference to the sheet/tab since there is a space in the name. Using your example:

IMPORTRANGE("https:/URL", "'Inc Database'!A2:A300")

But this will only import column A, so you cannot check against column B

Then use the Query. If you want everything where B is 'Permanent' then you want (untested):

=QUERY(IMPORTRANGE("https:/URL", "'Inc Database'!A2:B"),"SELECT Col1 WHERE Col1 = 'Permanent'")

This will:

Import all of the rows, starting at A2 from the main data sheet to use in the Query().
Via Query, return only those where Col2 (B) contains 'Permanent'

answered Jan 16, 2017 at 15:47

Karl_S

3,5922 gold badges24 silver badges38 bronze badges

1 Comment

user7220901 Over a year ago

Not working, thks though. In the past, it works with space and no single quote as well. SO i do not think that is the issue.

Collectives™ on Stack Overflow

IMPORTRANGE with condition

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related