Excel Functions Missing in Office 2016: IFS, MAXIFS, MINIFS, SWITCH, CONCAT, TEXTJOIN

Where did the IFS function go? (same for MAXIFS, MINIFS, SWITCH, CONCAT and TEXTJOIN) Can’t find IFS function in Excel 2016?

Does it seem like you were once able to use the IFS function in Excel with your worksheets? Can’t find the IFS function in Excel 2016? Do you have a standalone license (a non-Office 365 subscription) of Office? Do your function names now resolve to “_xlfn.IFS”? (same for MAXIFS, MINIFS, SWITCH, CONCAT and TEXTJOIN)

In looking at various posts online, one user notes in his version 16.0.6568.2025 the IFS function worked. After an update from Microsoft to version 16.0.6868.2048 the IFS function no longer worked. (Lost new functions like “ifs” & “switch” & “maxifs” after upgrade to 16.0.6868.2048 at today)

Other posts such as Excel new functions IFS, MAXIFS, MINIFS, SWITCH, CONCAT, TEXTJOIN not available in ms office 2016, or Why is Excel not able to compute Maxifs function, or Excel 2016 also seem to show where people expected to see IFS, MAXIFS, MINIFS, SWITCH, CONCAT and TEXTJOIN functions but are not able to use them.

What is going on?

Based on some answers in the Answers forum from Microsoft, those functions are only made available to subscribers to the Office 365 subscription product. Some references indicate it may be part of Office 2019.

Were the functions removed from existing standalone copies of Excel 2016? I can’t tell. But it does seem like there is an unmet expectation that buying a full license of Excel gets the most current version of Excel.

So, what now?

If you have a standalone version of Excel that does not have the IFS, MAXIFS, MINIFS, SWITCH, CONCAT or TEXTJOIN function you have a few options to get the functionality.

Option 1: Install the Excel PowerUps Premium Suite add-in

The Excel PowerUps Premium Suite add-in, available on this site, enables an IFS, MAXIFS, MINIFS, SWITCH, CONCAT and TEXTJOIN function (in addition to about 80 other functions) to Excel 2016, Excel 2013, Excel 2010 and Excel 2007.

You can try this for free for 30 days. No registration, no email needed.

Option 2: Subscribe to Office 365

If you subscribe to Office 365 (I know, you may already have a permanent full standalone license) you will get the functions along with other updates that Microsoft releases into Office over time.

Option 3: Use Equivalent Function Constructs in Excel

IFS Function Equivalent

Instead of this

IFS(A1>1, 1, A2>2, 2, A3>3, 3 ...)

You would nest the IF statements like this

IF(A1>1, 1, IF(A2>2, 2, IF(A3>3, 3, ... )))

Keeping track of the multiple levels of IF statements can be tricky which is why IFS is so useful.

MAXIFS Function Equivalent

Instead of this

MAXIFS(A1:A10, B1:B10, "value")

You would use an array formula (use CTRL+SHIFT+ENTER when entering) using a combination of the MAX and IF functions such as the following

MAX(IF(A1:A10="value",B1:B10))

You can do multiple criteria by multiplying the criteria together

MAX(IF(((A1:A10="value")*(C1:C10="value2"))>0, B1:B10))

MINIFS Function Equivalent

Similar to the MAXIFS case, instead of this

MINIFS(A1:A10, B1:B10, "value")

Use an array formula using a combination of the MIN and IF functions such as the following

MIN(IF(A1:A10="value",B1:B10)

Multiply the range comparisons as above for multiple ranges.

SWITCH Function Equivalent

Instead of this

SWITCH(A1, "value", "first", B1, "value2", "second", "default")

You would use nested IF statements such as this

IF(A1="value, "first", IF(B1="value2", "second", "default"))

For a larger number of comparisons, keeping track of the multiple nested IF statements can be tricky in a similar way to the IFS situation.

CONCAT Function Equivalent

Instead of this

CONCAT(A1, B1, C1)

You would use the concatenation operator such as

A1 & B1 & C1

TEXTJOIN Function Equivalent

Instead of this

TEXTJOIN(",", TRUE, A1, B1, C1)

Use the concatenation operator with some conditional checking for the delimiter like this

A1 & IF(ISEMPTY(B1), "", ", " & B1) & IF(ISEMPTY(C1), "", ", " & C1)

You need the conditional check so you don’t get delimiters stacked up if that isn’t what you need.