# Why does my nested IF return the wrong result?

The tests are in the wrong order: IF stops at the first TRUE. With > tests, check the largest threshold first: =IF(B2>20,"Freight",IF(B2>5,"Large",IF(B2>1,"Standard","Letter"))).

**Excel:** `=IF(B2>20,"Freight",IF(B2>5,"Large",IF(B2>1,"Standard","Letter")))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Parcel | Weight (lb) | Service |
| P-1 | 0.6 |  |
| P-2 | 3.2 |  |
| P-3 | 12 |  |
| P-4 | 27.5 |  |
| P-5 | 5 |  |
| P-6 | 1 |  |

Formula in C2, filled to C7: `=IF(B2>20,"Freight",IF(B2>5,"Large",IF(B2>1,"Standard","Letter")))`

Result: Letter; Standard; Large; Freight; Standard; Letter

## How it works

IF stops at the first test that is TRUE. Testing `B2>1` first catches every parcel over 1 lb, so a 27.5 lb parcel never reaches the Freight test and is labeled Standard. Checking the largest threshold first sends each parcel to the narrowest band it fits.

## Other ways

- `=IFS(B2>20,"Freight",B2>5,"Large",B2>1,"Standard",TRUE,"Letter")`: The same order in IFS, which is easier to read.
- `=IF(B2<=1,"Letter",IF(B2<=5,"Standard",IF(B2<=20,"Large","Freight")))`: Working up from the smallest band with <= is just as valid. What matters is checking the bands in order.

## Common mistakes

- With > tests start from the biggest threshold; with < or <= tests start from the smallest.
- Boundary values go to the lower band with >: a 5 lb parcel is Standard. Use >= if it should move up.

## Questions

**How do I test a nested IF?**

Try one value inside each band and one on each boundary, such as 1, 5 and 20 here.

**When should I switch to a lookup table?**

Once there are more than three or four bands, or they change often. List the band starts in cells and use `XLOOKUP(B2,starts,labels,,-1)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/nested-if-returns-wrong-result