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.

AND function with IF function

Authors: Debra Dalgleish

IFS

IFERROR / IFNA

AND / OR / XOR

More Logical Functions

Download the Sample File

More Functions

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)

First IF checks if the Product cell is empty (B7)
Second IF checks if the Price cell (C7) empty

if function checks for empty cell

More Information - IF

Click the link to read more about the IF function, and see other examples, and get a download file.

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:

product name
price
quantity

tax formula based on check box status

More Information - IFS

Click the link to read more about the IFS function, and see other examples, and get a download file.

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.

=IFERROR(SEARCH(B5,B2),"Not Found")

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.
If the result is an error, ISERROR returns "Not Found"

SEARCH example 1

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"

AND function with IF function

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)

AND function with IF function

More Information - AND

Click the link to read more about the AND function, and see other examples, and get a download file.

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

Download the Sample File

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