Have you ever encountered a situation where you wanted to return a specific value when the logical expression in Google Sheets IFS function is false? In this article, I’ll share with you a clever trick to overcome this limitation and make the most out of the IFS function. So, let’s dive in!
The Difference Between IFS and IF
Before we get into the solution, let’s understand the difference between Google Sheets IFS and IF functions. The IFS function is designed to test multiple conditions and return the first true value. However, if none of the conditions are true, it returns an “#N/A” No Match error. On the other hand, the IF function allows you to specify a value to be returned when there is no match.
The Trick to Return a Value When the Logical Expression is False in IFS
With the help of this tutorial, you’ll learn an awesome trick to use IFS instead of IF or vice versa. Let’s take a look at some examples.
Example:
Assume the value in cell A1 is “USA” and the value in cell A2 is “RUSSIA”.
The formula would be:
=IFS(A1="USA","USA",A2="RUSSIA","RUSSIA")In this case, the formula checks cell A1 and A2 for a match. Since both values are matching, the formula would return the first matching value, which is “USA”.
Now, let’s change the value in cell A1 to “INDIA”, while keeping the value in cell A2 as “RUSSIA”.
The formula would return “RUSSIA” because it’s the only match.
But what if we change the values as follows:
- The value in cell A1 is “INDIA” and the value in cell A2 is “CHINA”.
Can you guess what would be the formula output? Yes, the formula would return an “N/A” error.
Using the IF Function
In situations like the one described above, you can use the IF function to handle multiple conditions. Here’s an example of a nested IF formula:
=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. It would return the value “NO MATCH FOUND”.
The IFS Trick
Now, let’s learn the clever trick to use IFS similar to IF. There are two options:
Option 1: IFS False Part
Use the IFS formula as shown below to return a specific value when the logical expression is false:
=IFS(A1="USA","USA",A2="RUSSIA","RUSSIA",1*1=1,"THERE IS NO MATCH")In any IFS formula, you can use the last part 1*1=1,"THERE IS NO MATCH". You just need to change the value “THERE IS NO MATCH” to the desired output value.
Option 2: IFERROR with IFS
Another common method is to use the IFERROR formula with IFS as shown below:
=IFERROR(IFS(A1="USA","USA",A2="RUSSIA","RUSSIA"),"THERE IS NO MATCH")This formula handles the error that occurs when the logical expression is false and returns the specified value “THERE IS NO MATCH”.
And that’s it! With these clever tricks, you can now return a value when the logical expression is false in IFS.
I hope you found this tutorial helpful. To learn more tips and tricks about Google Sheets, visit Crawlan.com. Enjoy exploring the possibilities of data manipulation in Google Sheets!
