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

Question

I have a table of data similar to:

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?

JPV · Accepted Answer · 2017-01-31 12:54:22Z

1

See if this works:

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

answered Jan 31, 2017 at 12:54

JPV

27.4k4 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user12727697 · Accepted Answer · 2020-01-16 22:35:57Z

0

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

edited Jan 16, 2020 at 22:35

user3956566

answered Jan 16, 2020 at 22:07

user12727697

11 bronze badge

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related