There is a major difference between Google Sheets IFS and IF. I’ll explain to you what’s that 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.
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 the 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
Example:
The value in cell A1 for the test is “USA” and the value in cell A2 is “RUSSIA”.
Formula:
=IFS(A1="USA","USA",A2="RUSSIA","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. the “USA”.
Now I am changing the value in cell A1 to “INDIA”. The cell A2 has the same value which is “RUSSIA”.
In this case, the above formula would return “RUSSIA” as it’s the only match. Now I am changing the values as follows.
The value in cell A1 is “INDIA” and the value in cell A2 is “CHINA”. Here can you guess what would be the formula output? Yup! The formula would obviously return an 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 may 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.
IFS False Part Option 1
Use the IFS formula as below. It can return the value “THERE IS NO MATCH” when the logical expression is false in IFS.
=IFS(A1="USA","USA",A2="RUSSIA","RUSSIA",1*1=1,"THERE IS NO MATCH")
In any IFS formula, you can use the last part1*1=1,"THERE IS NO MATCH"
. You may only need to change the value “THERE IS NO MATCH” to the value you want to output.
IFS False Part Option 2
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.
IFS is a great function. I end it with catch-all … , True,”THERE IS NO MATCH”)