CODEWITHSUNDEEP
filtergoogle-sheetsgoogle-sheets-formula
James
James

Reputation: 311

How to get an array of values where columns match multiple criteria

I have a table of data similar to:

Sample

where I'd like to get just the shapes which match a set of given criteria (in this case week=2 and colour=blue).

I can return the first result using index and match like:

=ArrayFormula(INDEX(C2:C14,MATCH($F$1&$F$2,A2:A14&B2:B14,0)))

but I'd like to return the all matching values (eg square and triangle) in to the range F3:Fsomething. This would preferably be done using a formula that returns a range and isn't "copied-down", as a list of all possible shapes isn't known beforehand.

How can I modify this formula to achieve this?

Upvotes: 2

Views: 1282

Answers (2)

user12727697
user12727697

Reputation: 1

to do multiple criteria you want to use * like so

=FILTER (C2:C14, (B2:B14=F2) * (A2:A14=F1))

and if you want the results all in the same cell with a delimiter, use TEXTJOIN

=TEXTJOIN([DELIMETER],[IGNORE EMPTY TEXT],text1)

=TEXTJOIN(", ",TRUE,FILTER(C2:C14,(B2:B14=F2)*(A2:A14=F1)))

Upvotes: 0

JPV
JPV

Reputation: 27282

See if this works:

=FILTER (C2:C14, B2:B14=F2, A2:A14=F1)

Upvotes: 1

Related Questions