The Excel IFS function, and when a lookup table beats it

Updated August 14, 2026

Nested IF statements are fine up to about two levels. At three, you are counting brackets. At five, you have a formula nobody will ever safely edit — including you, in six months.

=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F"))))

That is the same logic IFS was added to express:

=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F")

One function, one level, and the conditions read down the formula in the order Excel checks them.

The syntax

=IFS(condition1, value1, [condition2, value2], ...)

Pairs, all the way along: a test, then what to return if that test is TRUE. Excel works left to right and stops at the first TRUE. Up to 127 pairs, which is 119 more than any formula should ever need.

The stop-at-first-TRUE behaviour is the thing to internalise, because it means order is the logic. In the grade formula above, B2>=60 is TRUE for a score of 95 as well as for 65 — it just never gets evaluated for 95, because B2>=90 matched first. Write the conditions from most restrictive to least and it works. Write them the other way round and every score becomes a D:

=IFS(B2>=60,"D",B2>=70,"C",B2>=80,"B",B2>=90,"A")   ← wrong, always D

No error, no warning. Just quietly wrong numbers, which is the worst kind of spreadsheet bug.

The catch-all everyone forgets

Unlike IF, IFS has no else. If no condition is TRUE, it returns #N/A.

The fix is to make the last condition literally TRUE, which always matches:

=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", B2>=60,"D", TRUE,"F")

Read that final pair as “otherwise, F”. Get in the habit of adding it even when you think the conditions are exhaustive — they usually are until someone types text into a numeric column.

If you would rather see the problem than paper over it, leave the catch-all off and let #N/A tell you that a row fell through. That is a legitimate choice for a working sheet. It is the wrong choice for a report someone else reads.

A worked example: commission bands

=IFS(D2>=250000, D2*0.08,
     D2>=100000, D2*0.06,
     D2>=50000,  D2*0.04,
     TRUE,       0)

Readable, auditable, and each rate sits next to the threshold it belongs to. Compare that to the nested version and the maintenance argument makes itself.

Where IFS is the wrong tool

Here is the part most tutorials leave out. That commission formula has the rates and the thresholds buried inside a formula. When finance changes the top rate to 9%, someone has to find the formula, understand it, and edit it correctly in every cell it appears.

A lookup table does the same job with the numbers on the sheet where people can see and change them:

=D2 * XLOOKUP(D2, $F$2:$F$5, $G$2:$G$5, 0, -1)

The -1 match mode means “exact match, or the next smaller value” — exactly the banding behaviour the IFS chain was hand-coding. Now the rates are data, not logic, and updating them is a typing job rather than a formula job.

Rule of thumb: if the values inside your IFS are business rules that change, put them in a table and look them up. If they are structural logic that will not change, IFS is clearer than a lookup. See XLOOKUP vs INDEX MATCH for how the lookup side of that works.

Version support, and what to do without IFS

IFS is in Excel for Microsoft 365, Excel 2019, Excel 2021, and Excel for the web. It is not in perpetual Excel 2016 or earlier. Open an IFS formula there and you get #NAME? — Excel does not recognise the function at all.

That matters if you send workbooks to clients or colleagues on older builds. Two ways to stay compatible:

• Nested IF, holding your nose. Works everywhere back to Excel 97.
• A lookup table with approximate match, which is better than both and works everywhere: VLOOKUP(D2,$F$2:$G$5,2,TRUE) against an ascending threshold column does the banding job in every version of Excel ever shipped.

That second option is the one we usually end up recommending. It is version-proof, it is readable, and it puts the business rules on the sheet.

Common mistakes

Conditional logic and lookups are the two things that come up in every Excel course we run, usually within the first hour, because they are where most of the manual recalculating in a business actually lives. If you want to work through your own sheets with an instructor, the Excel and Office 365 courses cover this ground, and the course schedule shows what is running next.