IFS vs VLOOKUP for Grades and Bands: Which Is Better?

IFS vs VLOOKUP comes down to where the band rules live. IFS writes every rule inside the formula, such as "80 or more is an A". VLOOKUP with TRUE reads the rules from a small band table on the sheet. Both turn a score into a grade, a sales figure into a commission rate or an age into an age group. This guide is for anyone setting up bands in a gradebook, a pay sheet or a job test, and it shows both methods on the same data.

IFS vs VLOOKUP at a glance#

IFS is quicker for a few fixed rules, and VLOOKUP is easier to maintain when the bands are many or change.

The example data#

All examples use five student scores and a five-band scale. The band table lists the minimum score for each grade:

The correct grades are A, D, B, F and A. Columns A to C hold the students and the D and E columns hold the band table.

How to grade with IFS#

IFS checks a list of conditions in order and returns the result for the first one that is TRUE. In C2:

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

Fill it down (Windows: Ctrl+D; Mac: ⌘D). For Chen's 74, the first test (74>=80) is FALSE, the second (74>=70) is TRUE, so the result is B. IFS stops there and ignores the rest.

Two rules make IFS work for bands:

• Test the highest band first. If you start with B2>=50, every score of 50 or more gets a D, because that test is TRUE first.
• End with TRUE,"F". TRUE is always true, so it catches every score that failed the other tests. Without it, Dina's 49 returns #N/A.

The IFS function guide covers the syntax, and nested IF vs IFS shows the older nested IF version of the same formula.

How to grade with VLOOKUP and a band table#

VLOOKUP with TRUE as its last argument does an approximate match: it finds the largest value in the first column that is less than or equal to the lookup value. That is exactly how bands work. In C2:

=VLOOKUP(B2,$D$2:$E$6,2,TRUE)

For Chen's 74, VLOOKUP walks down 0, 50, 60, 70, 80. The largest value not above 74 is 70, so it returns column 2 of that row: B. Eko's 80 matches 80 exactly and gets an A.

Three things must be true for this to work:

• The table is sorted from smallest to largest in the first column.
• The first row starts at the lowest possible score, here 0. A score below the first value returns #N/A.
• The table range is locked with $ signs so it doesn't move when you fill down.

The difference between TRUE and FALSE is the key idea here; the guide to VLOOKUP TRUE vs FALSE explains it, and the VLOOKUP function guide covers every argument.

The same grades side by side#

Both formulas give identical results on this data:

The difference shows when something changes. Say the school moves the A cut-off from 80 to 85. With VLOOKUP, you type 85 in D6 and every grade updates. With IFS, you edit the formula in C2 and fill it down again, and if the same rule is copied into other sheets, you have to find and edit each one.

They also behave differently with bad data. If someone types "absent" in the Score column, VLOOKUP returns #N/A, which is easy to spot. IFS returns "A", because Excel treats any text as greater than any number, so "absent">=80 is TRUE. Add an ISNUMBER test first if your scores might contain text.

When to use IFS#

Use IFS when the rules are few, fixed and easy to read in one line:

• Two to four bands that rarely change, such as Pass/Merit/Distinction.
• Rules that test more than one column. For example, an A needs 80 or more and 90% attendance: =IFS(AND(B2>=80,C2>=0.9),"A",B2>=70,"B",TRUE,"C"). VLOOKUP can't do this.
• Rules that aren't simple ranges, such as different results for a blank cell, a negative number or a specific code.
• A self-contained formula that has to work when copied to another workbook without its table.

If you are matching exact values rather than ranges, such as "N" for North, SWITCH vs IFS shows a shorter option.

When to use VLOOKUP#

Use a VLOOKUP band table when the bands are data, not logic:

• Five or more bands, such as tax brackets, commission tiers or shipping weights.
• Cut-offs that change, so a manager can edit a table instead of a formula.
• Bands shared by many sheets, so there is one place to update.
• Files for older Excel. VLOOKUP works in every version; IFS shows #NAME? in Excel 2016 and earlier unless it comes with Microsoft 365.
• Readers who need to see the rules. A visible table explains the grading better than a long formula.

In Microsoft 365 or Excel 2021, XLOOKUP can do the same with =XLOOKUP(B2,$D$2:$D$6,$E$2:$E$6,,-1). The -1 match mode means "exact match or the next smaller value". The guide to VLOOKUP approximate match compares both.

Common mistakes with IFS and VLOOKUP bands#

These slips give wrong grades without an error message, so they are worth checking:

1. IFS in the wrong order. Lowest band first gives every passing student the lowest passing grade.
2. No TRUE catch-all in IFS. Scores below every band return #N/A.
3. Band table not sorted. VLOOKUP TRUE assumes ascending order and returns odd results otherwise.
4. Band table missing the zero row. Scores below the first cut-off return #N/A.
5. Using FALSE in VLOOKUP. FALSE asks for an exact match, so 74 isn't found and you get #N/A.
6. Forgetting the 4th argument. VLOOKUP's default is TRUE. That works for bands, but write TRUE anyway so the next reader knows it was deliberate.
7. Unlocked table range. Without $ signs, the table slides down as you fill, and the last students get #N/A.
8. Boundary confusion. Check scores that sit exactly on a cut-off, such as 80. Both formulas above give an A for 80. If your rule says "more than 80", use > in IFS and adjust the table.

For a full gradebook built both ways, see the letter grade formula guide.

Our recommendation#

For grades and bands, we recommend a VLOOKUP band table (or XLOOKUP if everyone has Microsoft 365) as the default. The rules sit in plain sight, a change takes one edit, and the formula stays short however many bands you add. Choose IFS when you have a short fixed scale, or when a band depends on more than one column, which a lookup can't handle.

In job tests, read the task: "use a lookup table" means VLOOKUP, and "use IFS" means IFS. If it only asks for the right grade, either one is correct.

Practise it#

Both methods are common in Excel tests, so it pays to be fluent in each. Practise nested IF and IFS in a live spreadsheet, then practise VLOOKUP with approximate match on a band table. ExcelReady checks each formula as you type. Start with Chen's grade in the exercise on this page.