HomeGoogle DocsSpreadsheetHow to Return Value When The Logical Expression is FALSE in IFS

How to Return Value When The Logical Expression is FALSE in IFS

Published on

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.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here