This function is an evolution of the old CONCATENATE function.
TEXTJOIN allows concatenation of an entire range with a specified delimiter.
To combine the column data into a full address, the formula would be:
•The first argument is the delimiter. This is what will separate the values.
•The second argument is either TRUE or FALSE. Indicating this as TRUE or 1 means it will ignore empty cells. As FALSE, it would consider empty cells and still show them in the result.
CONCAT is obviously another evolution of CONCATENATE.
Using the previous example, if CONCAT is used, it will result to:
CONCAT only accepts one kind of argument, which is the range. It will combine the texts, that’s all. No delimiter can be set, nor ability to ignore blanks. Therefore, it is a more basic kind of TEXTJOIN.
Similar to TEXTJOIN, the #VALUE error will show up if the result is more than 32767 characters.
The IFS function has been introduced to handle a situation where you will string several IF statements in one.
=IFS (B2<=80,"Fail" , B2<=90,"Good" , B2>90,"Pass" )
Note that unlike in the nested IF, the IFS function does not allow a “catch rest” or the last argument in a multiple IFs formula. All possible outcomes must have their corresponding logical test.
The SWITCH is similar to the old CHOOSE function, but is not limited in accepting numbers in order. It is used to simply conditional statements.
=SWITCH( A2 , "Japan","Asia" , "France","Europe" , "Philippines","Asia" )
The formula starts with cell to be evaluated. Then succeeding values and their corresponding outcomes follow.
In the example, if A2 is “Japan,” then the result will be “Asia.”
If A2 is “France,” the result is “Europe.”