How to use the IFS function

Author: Oscar Cronquist Article last updated on May 05, 2022

IFS function

The IFS function checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.

The image above shows the IFS function in column C, it returns "Larger than 50" if the corresponding value on the same row in column B is larger than 50.

It also returns "Smaller than or equal to 50" if the corresponding value is smaller than or equal to 50.

IFS function Syntax
IFS function Arguments
IFS function example
IFS function alternative
IFS function - convert month number to month name
Find the element name based on an atomic number
Get Excel file

1. IFS Function Syntax

IFS(logical_expression1, Value_if_True1, [logical_expression2, Value_if_True2],…)

The IFS function allows you to have up to 127 pairs of logical expressions and values if the logical expression evaluates to true.

2. IFS Function Arguments

logical_expression1	Required. The logical expression you want to use.
Value_if_True1	Required. The value to be displayed if the logical expression is met.
[logical_expression2]	Optional. Additional logical expression.
[Value_if_True2]	Optional. Additional value to be returned if the logical expression evaluates to true.

3. IFS function example

The IFS function is a better alternative to nested IF functions, the formula becomes a lot simpler and easier to understand.

This example shows how to calculate a grade based on a score.

Formula in cell C3:

=IFS(B3>95, "A+", B3>89, "A", B3>84, "B+", B3>79, "B", B3>74, "C+", B3>69, "C", B3>64, "D+", B3>59, "D", B3>=0, "F")

The formula in the above picture calculates the grade based on the following score levels.

A+ score > 95
A score > 89.
B+ score > 84
B score > 79
C+ score > 74
C score > 69
D+ score > 64
D score > 59
F score > 60

4. IFS function alternativ

The IFS function is an Excel 2016 function, however, you can replicate the behavior with the following formula if you have an earlier version of Excel.

Formula in cell C3:

=INDEX($F$3:$F$11, MATCH(B3, $E$3:$E$11))

The formula above uses the table E3:F11 to match the score with the correct grade.

4.1 Explaining formula

Step 1 - Find a relative position in the table of a given value

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(lookup_value, lookup_array, [match_type])

MATCH(B3, $E$3:$E$11)

becomes

MATCH(63, {0; 60; 65; 70; 75; 80; 85; 90; 96})

Note, in this specific setup the MATCH function must have the numbers in the second argument sorted from smallest to largest.
The third argument determines what the requirements are, read more: MATCH function. I know there is no third argument in this example, however, the default value is 1 meaning: Finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be sorted in a descending order

and returns 2.

Step 2 - Return the corresponding value

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

INDEX(array, [row_num], [column_num], [area_num])

INDEX($F$3:$F$11, MATCH(B3, $E$3:$E$11))

becomes

INDEX({"F"; "D"; "D+"; "C"; "C+"; "B"; "B+"; "A"; "A+"}, 2)

and returns "D" in cell C3.

5. IFS function - convert month number to month name

IFS function convert month number to month name

Formula in cell C3:

=IFS(MONTH(B3)=1,"January",MONTH(B3)=2, "February",MONTH(B3)=3,"March",MONTH(B3)=4, "April", MONTH(B3)=5,"May",MONTH(B3)=6, "June", MONTH(B3)=7, "July", MONTH(B3)=8,"August",MONTH(B3)=9, "September",MONTH(B3)=10,"October",MONTH(B3)=11,"November", MONTH(B3)=12, "December")

Explaining formula in cell B3

Step 1 - Calculate month number based on a date

The MONTH function extracts the month as a number from an Excel date.

MONTH(serial_number)

MONTH(B3)

becomes

MONTH(44805)

and returns 9.

Step 2 - Calculate logical expressions

The equal sign is a logical operator and lets you compare value to value in an Excel formula. The result is a boolean value TRUE or FALSE.

MONTH(B3)=1 evaluates to FALSE
MONTH(B3)=2 evaluates to FALSE
MONTH(B3)=3 evaluates to FALSE
MONTH(B3)=4 evaluates to FALSE
MONTH(B3)=5 evaluates to FALSE
MONTH(B3)=6 evaluates to FALSE
MONTH(B3)=7 evaluates to FALSE
MONTH(B3)=8 evaluates to FALSE
MONTH(B3)=9 evaluates to TRUE
MONTH(B3)=10 evaluates to FALSE
MONTH(B3)=11 evaluates to FALSE
MONTH(B3)=12 evaluates to FALSE

MONTH(B3)=9

becomes

9=9

and returns TRUE

Step 3 - Return the corresponding value

MONTH(B3)=1,"January",
MONTH(B3)=2, "February",
MONTH(B3)=3,"March",
MONTH(B3)=4, "April",
MONTH(B3)=5,"May",
MONTH(B3)=6, "June",
MONTH(B3)=7, "July",
MONTH(B3)=8,"August",
MONTH(B3)=9, "September",
MONTH(B3)=10, "October",
MONTH(B3)=11,"November",
MONTH(B3)=12, "December"

MONTH(B3)=9, "September"

becomes

TRUE, "September"

and returns "September" in cell C3.

6. Find the element name based on an atomic number

IFS function find name based on atomic number 1

The IFS function is great for smaller logical expressions but I recommend using a different formula combined with a table to avoid mega-formulas.

This example demonstrates this, the table in cell range E3:G116 describes the atomic numbers in the periodic table and the corresponding symbol and element name.

Creating a large IFS function for this specific example is possible, remember, you are allowed to use up to 127 pairs of logical expressions, however, I recommend putting all logical expressions and corresponding values in a table. This makes it easier to troubleshoot and examine the formula.

Formula in cell C3:

=INDEX($G$3:$G$116, B3)

Explaining formula

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

INDEX(array, [row_num], [column_num], [area_num])

INDEX($G$3:$G$116, B3)

becomes

INDEX($G$3:$G$116, 57)

and returns the value in cell E59 which is "Lanthanum".

$G$3:$G$116 is an absolute cell reference meaning it won't change when you copy cell C3 and paste it to the cells below.

B3 is a relative cell reference meaning it changes when you copy cell C3 and paste it to the cells below.

7. Get

Get the Excel file

How-to-use-the-IFS-function.xlsx

Functions in this article

IFS INDEX MATCH MONTH

Functions in 'Logical' category

The IFS function function is one of many functions in the 'Logical' category.

AND function

Perform a logical test in each argument and if all arguments return TRUE the AND function returns TRUE.

BYCOL function

Passes all values in a column based on an array to a LAMBDA function, the LAMBDA function calculates new values based on a formula you specify. It then continues with the next column until all columns in the array have been processed.

BYROW function

Puts values from an array into a LAMBDA function row-wise.

FALSE function

Returns the logical (boolean) value FALSE.

IF function

Returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IFERROR function

If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.

IFNA function

Handles #N/A errors only, it returns a specific value if the formula returns a #N/A error.

IFS function

Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.

LAMBDA function

Build custom functions without VBA, macros or javascript.

MAP function

Passes all values in an array to a LAMBDA function, the LAMBDA function calculates new values based on a formula you specify. It then returns an array with the same size as the original array.

NOT function

Returns the boolean opposite to the given argument.

OR function

Evaluates a logical expression in each argument and if at least one argument returns TRUE the OR function returns TRUE. If all arguments return FALSE the OR function also returns FALSE.

REDUCE function

Shrinks an array to an accumulated value, a LAMBDA function is needed to properly accumulate each value in order to return a total.

SCAN function

Passes values in an array to a LAMBDA function, the LAMBDA function calculates new values based on a formula you specify. It then returns an array with the same size as the original array using an accumulator parameter.

SWITCH function

Returns a given value determined by an expression and a list of values.

TRUE function

Returns the logical (boolean) value TRUE.

XOR function

Calculates the logical exclusive OR meaning if at least one of the arguments evaluates to TRUE then the XOR returns TRUE. All arguments must be evaluated to FALSE for the XOR function to return FALSE.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form

How to use the IFS function

Table of Contents

1. IFS Function Syntax

2. IFS Function Arguments

3. IFS function example