The IFS guide
IFS takes pairs of a test and a value and returns the value of the first test that is TRUE: =IFS(B2<=1, 4.5, B2<=5, 8, B2<=20, 15, TRUE, "Freight"). It has no "otherwise" part, so the last test is TRUE.
Syntax
=IFS(test1, value1, [test2, value2], …)
Up to 127 pairs. IFS reads them left to right and stops at the first test that is TRUE. If none is, the result is #N/A.
A default with TRUE
Nested IF ends with an "otherwise" value. IFS doesn't have one, so add a last pair whose test is simply TRUE: it always passes, and its value covers everything the other tests missed. A 26 kg parcel fails <=1, <=5 and <=20, then lands on TRUE,"Freight".
Order the tests
With <=, start with the smallest band: a 0.8 kg parcel is also <=20, so if that test comes first, it gets the 20 kg price. With > it's the other way round: start with the largest. Either works if the order matches the sign.
IFS or nested IF?
They give the same answers. IFS is easier to read: one bracket at the end instead of one per IF. It needs Excel 2019, 2021 or 365; older versions show #NAME?. Compare with nested IF. For exact codes rather than ranges, SWITCH is shorter still.
Common mistakes
- No TRUE at the end. Anything outside the bands shows #N/A.
- < instead of <=. "Up to 5 kg" includes exactly 5.
- Numbers in quotes.
"8"is text and can't be added up. - Hiding #N/A with IFNA. Add the missing case instead, so it shows what it should.
FAQ
- How do I use IFS in Excel?
- Give it pairs of a test and a value: =IFS(B2<=1,4.5,B2<=5,8,TRUE,15). It returns the value of the first test that is TRUE.
- How do I set a default value in IFS?
- Make the last test TRUE: …,TRUE,"Other"). TRUE always passes, so it catches everything the earlier tests missed.
- Why does IFS return #N/A?
- None of the tests was TRUE for that row. Add TRUE and a value as the last pair.
- What is the difference between IF and IFS?
- IF has one test and an otherwise value; several conditions need IFs inside IFs. IFS takes many tests in one function but has no otherwise value.



































































