google-sheetsgoogle-sheets-formula

How can I extract columns based on multiple column value/criteria?

I'm working on a database of keywords that is around 70000 rows and 20ish columns. I would need to extract columns based on multiple-column criteria.

Here is what the database looks like (it seems to me that the table doesn't convert into a table)

Keyword Col A ColB Col C Col D Col E Col F Col G Col H
KW1 x x x x beauty auto x x
KW2 x x x x kitchen auto x x
KW3 x x x x moto beauty x x
KW4 x x x x kitchen beauty x x

...

What I'd need is: If in Col E or Col F "Beauty" is written, then export columns "Keyword", Col A, Col C, Col G, Col H.

➡ So I'd need rows 1,3,4 to be extracted into a new sheet.

My first draft is this query function, but it either returns VALUE or ERROR

=QUERY(FullSheetRange,"select A, C, G, H, where E2:E65000='Beauty' OR F2:F65000='Beauty'")

Ideally, I'd like it to be only with a classic or Array formula so I can make it a template easily shared to other people as well.

Solution

  • Simple QUERY() should work. In QUERY() function criteria's you do not need to mention full range. Instead use, only column name to refer full column. Try-

    =QUERY(A:I,"select A, C, G, H where (F ='beauty') OR (G ='beauty')")

    Image for clarification.

    enter image description here