MTwem
MTwem

Reputation: 145

Why does Google Sheets' FILTER formulae change output when inside IFS formula?

When using a FILTER formula Google Sheets gives output as expected =filter(A1:C15,A1:A15="ABC")

But when I wrap that in an IFS formula it only gives the first value =ifs(A1="ABC", filter(A1:C15,A1:A15="ABC"),A1="123", filter(A1:C15,A1:A15="123"))

Example spreadsheet here: https://docs.google.com/spreadsheets/d/1wZsTXGdnzCqwwyBxWpne2n-B577_bQvXPhgcTRQGWqk/edit#gid=411641402

What have I done wrong?

Thanks MTwem

Upvotes: 0

Views: 147

Answers (2)

MTwem
MTwem

Reputation: 145

Answer from JvdV in the comments section:

Unlike IF(), the IFS() function will fail in returning a range. Seems like implicit intersection behaviour is in place or rather "Array expected output = Array expected input" . Try: =IF(OR(A1="ABC",A1=123),FILTER(A1:C15,A1:A15=A1)) – JvdV

Upvotes: 0

Erik Tyler
Erik Tyler

Reputation: 9345

From what I can see, all you need is this:

=FILTER(A1:C15,A1:A15=A1)

Upvotes: 1

Related Questions