4

I'm looking for an efficient way to filter a data set by multiple individual, case insensitive character strings for multiple columns in a Google docs spreadsheet. I can already achieve this by amending a formula with one new segment per column I want to filter. However, I feel like there should be a way to do the same while reducing the length of the formula.

What I'm currently using is this

=filter('Data'!A:R,regexmatch(lower('Data'!A:A),lower(A1)),regexmatch(lower('Data'!B:B),lower(B1)),regexmatch(lower('Data'!C:C),lower(C1)))

'Data' is the sheet I'm pulling the data from. Each cell in row 1 (1:1) of the current sheet serves as input box by which the corresponding columns from the data sheet will be filtered. I'd then place the formula in cell A2 of the current sheet. This allows me to filter each column by an individual string of characters which can be modified from the outside.

I'm thinking there should be a way to match the input cells for filtering with their corresponding columns in the data sheet without having to add individual segments for each one to the formula. Or maybe there's a different way to go about this.

1

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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.