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

0
votes

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

2
google-sheetsgoogle-sheets-formula
Your sheet is locked. Please set the link's permission to "Anyone with the link..." and "Editor." Or add me as an editor (I send a message via the locked form requesting edit permission). – Erik Tyler
Oops sorry my bad – MTwem
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
Nice one JvdV! Chuck it in the answers section and I'll accept it for you – MTwem

2 Answers

1
votes

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

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

0
votes

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