 Home > Formulas > Logical > Topics

# Excel Logical Functions Guide

A short guide to the Excel logical functions, including IF, IFS, IFERROR, AND, OR, TRUE, FALSE and more. Some logical functions, such as LAMBBDA, LET and MAKEARRAY, are only available in Excel 365 or Excel for the Web. Authors: Debra Dalgleish

## IF Function

The Excel IF function is helpful if you want to check something on a worksheet, then show result A or B. This short video shows the steps for setting up a simple IF formula. There are written steps on the IF function page.

Video Timeline

• 0:00 Introduction
• 0:13 Do a Test
• 0:39 Order Form
• 0:55 Simple IF Formula
• 1:39 Test the Formula

### Example - IF

In this formula, there are two IF functions, with one nested inside the other.

=IF(C7="","", IF(D7="","", C7*D7)

1. First IF checks if the Product cell is empty (B7)
2. Second IF checks if the Price cell (C7) empty ## IFS Function

In Excel 2016 and later, there is also an IFS function, which you can use instead of multiple nested IF functions. With the IFS function:

• You can test multiple conditions, to see if they are TRUE.
• The formula returns the result for the first TRUE condition.

### Example - IFS

In this example, instead of using a nested IF formula, I used an IFS formula in column E.

=IFS(COUNTA(B9:D9)=3, C9*D9, B9="","", C9="","no price", D9="","no qty")

The formula checks 3 cells in an order form, to see if these cells have values entered:

1. product name
2. price
3. quantity ## IFERROR / IFNA Functions

the IFERROR and IFNA functions check the results of a calculation. If the result is an error, the value that you specify is returned instead of that error.

• IFERROR: In Excel 2007 or later; checks for any type of error
• IFNA: In Excel 2013 or later; checks for #N/A only

### Example - IFERROR

In this example, I used IFERROR with the SEARCH function, to prevent a #VALUE! error from showing in cell D5.

The SEARCH function checks cell B2, to see if it contains the text that I typed in cell B5.

• If the text is found, SEARCH returns the number of its starting position in the text string.
• If the text is not found, the result is a #VALUE! error. ## AND / OR / XOR Functions

Use the AND and OR functions to combine 2 or more tests, such as column B contains "Paid" and column D contains "Shipped".

• AND function will return TRUE, if ALL of the tests are true
• OR function will return TRUE, if ANY of the tests are true
• XOR function will return TRUE, if an ODD NUMBER of the tests are true

You can combine AND and OR functions with other Excel functions, such as IF function..

### Example - AND

This example shows AND combined in a formula with IF.

=IF(AND(B4="Paid",D4="Shipped"),"Completed","Pending")

• IF column B contains "Paid" AND column D contains "Shipped", the result will be "Completed" in column E.
• Otherwise, the result is "Pending" ### Example - OR / XOR

This example shows the difference between OR and XOR. Both formulas check the Status and Amt cells, to see if they are empty.

• Cell D4: =OR(B4<>"",C4<>"")
• TRUE - one or more of the tests is true
• Cell E4: =XOR(B4<>"",C4<>"")
• FALSE - 2 of the tests are true (EVEN number) ## More Logical Functions

The following logical functions are also available, depending on which Excel version you're using.

• All Versions: NOT, TRUE and FALSE functions are available in all versions of Excel.
• 2016 and Later: SWITCH was added in Excel 2016
• The newest functions were added in Excel 365, or Excel for the Web

Note: The links go to each function's Help page on the Microsoft website.

 Function Added Description NOT Reverses the logic of its argument TRUE Returns the logical value TRUE FALSE Returns the logical value FALSE SWITCH 2016 Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned. BYCOL 365 Applies a LAMBDA to each column and returns an array of the results BYROW 365 Applies a LAMBDA to each row and returns an array of the results LAMBDA 365 Create custom, reusable functions and call them by a friendly name LET 365 Assigns names to calculation results MAKEARRAY 365 Returns a calculated array of a specified row and column size, by applying a LAMBDA MAP 365 Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value REDUCE 365 Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator SCAN 365 Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value

• Logical Functions: Download the Excel Logical Functions workbook, to see all the examples from this page. The zipped file is in xlsx format, and does not contain macros.

## More Functions

CHOOSE

LOOKUP

Lookup - 2 Criteria