CODEWITHSUNDEEP
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulas
bottsjw
bottsjw

Reputation: 11

IF results different from IFS result in Google Sheets

Using these two formulas in Google Sheets results in different output results. The IF result will expand the array across the cells as expected. The IFS result only displays the first array value.

=IF(1=1,{1,2,3}) RESULT: 1 2 3 (across three cells)
=IFS(1=1,{1,2,3}) RESULT: 1 (in one cell)

Can someone explain why these results differ?

Upvotes: 1

Views: 298

Answers (2)

pnuts
pnuts

Reputation: 59442

Not at answer to why (IMO off topic) but to get the same result from IFS:

=IFS({1,1,1},{1,2,3})

Can be written:

=IFS({1=1,1=1,1=1},{1,2,3})

Upvotes: 0

player0
player0

Reputation: 1

You may think that behaviour of IFS() is similar to IF() and reason of having IFS() is to avoid nesting of multiple IF() but that's not so true. Yes, there are some common baselines however, there is a major difference when it comes to arrays. IFS() in a combination of arrayed output expects arrayed input - that's why you got returned single-cell output instead of arrayed output.

  • let's say your IF() formula is like:

  • "converting" it into IFS() will return this (because cell A1 is not array/range):

  • now let's add a arrayformula (ranged input):

  • also note this behaviour:

Upvotes: 1

Related Questions