Reputation: 145
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
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
Reputation: 9345
From what I can see, all you need is this:
=FILTER(A1:C15,A1:A15=A1)
Upvotes: 1