Search code examples
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

  • The reason your original formula isn't working is that a Query string doesn't recognize ranges. In your case, the issue stems from E2:E65000 and F2:F65000.

    You may try this formula:

    =QUERY(A1:I5,"Select Col1, Col2, Col4, Col8, Col9 WHERE Col6='beauty' OR Col7='beauty'")

    You can also use matches instead of =:

    =QUERY(A1:I5,"Select Col1, Col2, Col4, Col8, Col9 WHERE Col6 matches 'beauty' OR Col7 matches 'beauty'")

    Expected Output:

    KW1 x x x x
    KW3 x x x x
    KW4 x x x x

    Reference