How to Use Filter with Multiple Criteria in Google Sheets

formulasgoogle sheetsgoogle-sheets-filtergoogle-sheets-query

I'm working with Google Sheet for healthcare purposes organizing patients lists and endorsement to team issues:

=filter('CASES LIST'!C:R,('CASES LIST'!Q:Q="admission")+('CASES LIST'!Q:Q="pending")+('CASES LIST'!Q:Q="PCI"))

Can I make it shorter like:

=filter('CASES LIST'!C:R,('CASES LIST'!Q:Q="admission"or"pending"or"pci"))

I want to filter the data if it contains either admission, pending or pci.

Best Answer

Please use the following query formula (adjusting ranges to your needs), instead of the one you already use.

=QUERY({P1:R},"where Col2 matches 'admission|PCI|pending' ",0)

Functions used:

QUERY

Related Solutions

Google Sheets – Handling Variables with Data from Another Sheet

You use INDIRECT().

=INDIRECT(B1 & "!" & A1)

I'm assuming that the values are in row 1 and that the value in Row B is surrounded by single quotes. Otherwise, you'll have to put them in.

=INDIRECT("'" & B1 & "'!" & A1)

The & concatenates the values. You could use the function CONCATENATE() instead.

[I put spaces for readability, but they're not needed.]

Google-sheets – Case insensitive character string filter for multiple individual columns (dynamic input for each)

It appears that you are using regexmatch just to test string equality. This isn't necessary, equalities can be entered simply as Data!A:A = A1. You don't even need lower, because string comparison in filter is case-insensitive:

=filter(Data!A:R, Data!A:A = A1, Data!B:B = B, Data!C:C = C1)

(Also, there is no need for single quotes around sheet name when it does not contain spaces.)

Best Answer

Related Solutions

Google Sheets – Handling Variables with Data from Another Sheet

Google-sheets – Case insensitive character string filter for multiple individual columns (dynamic input for each)

Related Topic