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
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.
Example - IF
In this formula, there are two IF functions, with one nested inside the other.
=IF(C7="","", IF(D7="","", C7*D7)
More Information - IF
Click the link to read more about the IF function, and see other examples, and get a download file.
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:
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:
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.
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.
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".
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.
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.
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.
Note: The links go to each function's Help page on the Microsoft website.
Last updated: May 23, 2023 9:33 AM