There is a major difference between Google Sheets IFS and IF. I’ll explain you what’s this difference and how to overcome it. From the title, how to return value when the logical expression is FALSE in IFS, you may have already got some idea about this tutorial.
By default, Google Sheets IFS function can’t return a value when a logical test fails to find a match. It returns an “#N/A” No Match error. Because, the purpose of IFS function is to test multiple conditions and return the first true value.
But the IF function can return a specified value if there is no match. When you want to perform a TRUE or FALSE logical test, the correct function for this purpose is IF not IFS. But with the help of this tutorial you can learn an awesome trick. You can use IFS instead of IF or vice versa. How to? Just follow the below examples.
How to Return Value When The Logical Expression is FALSE in IFS
Value in Cell A1: USA, Value in Cell A2: RUSSIA
The above formula would check Cell A1 and A2 for a match. Since both the values are matching, the formula would return the first matching value, i.e. USA.
Value in Cell A1: INDIA, Value in Cell A2: RUSSIA
In this case, the above formula would return “RUSSIA” as it’s the only match.
Value in Cell A1: INDIA, Value in Cell A2: CHINA
Here can you guess what would be the formula output? Yup! The formula would obviously return a “#N/A” error. You can intelligently overcome this. Before that let’s see how we can use the IF function in the above example.
As you know, an IF formula can use to test only one condition. When multiple conditions are there, you should use a Nested IF as below.
=IF(A1=“USA”,“USA”,IF(A2=“RUSSIA”,“RUSSIA”,“NO MATCH FOUND”))
This formula would execute the FALSE part. Since there is no match in Cell A1 and A2. The formula would return the value “NO MATCH FOUND”
As I told you above, you can apply some logic to use IFS similar to IF. Here is that trick. There are two options.
Use the IFS formula as below. It can return the value “THERE IS NO MATCH” when the logical expression is false in IFS. I’ve highlighted the changes in this formula compared to the first IFS formula above.
=IFS(A1=”USA”,”USA”,A2=”RUSSIA”,”RUSSIA”,1*1=1,”THERE IS NO MATCH”)
In any IFS formula, you can use the above highlighted part. You may only need to change the value “THERE IS NO MATCH” to the value you want to output.
This method is common in use. You can use an IFERROR formula with IFS as below.
=IFERROR(IFS(A1=”USA”,”USA”,A2=”RUSSIA”,”RUSSIA”),”THERE IS NO MATCH”)
That’s all in this tutorial. Hope you have learned how to return value when the Logical Expression is FALSE in IFS. Enjoy.