Excel Logic & Lookups: IF, Nested IF and XLOOKUP

IF: your first conditional

IF(condition, if true, if false) evaluates a condition and returns one of two values. Use it to mark cells as Pass/Fail, In Stock/Out of Stock, or Flag/No Flag.

• =IF(B2>=50,"Pass","Fail") — simple two-way test
• =IF(B2="","Empty","Filled") — test for empty or filled
• =IF(AND(B2>50,C2>50),"Both pass","One or more fail") — multiple conditions

Nested IF: two levels is your limit

You can nest IF inside IF to handle three outcomes (distinction, pass, fail). But beyond three levels, the formula becomes unreadable and debugging it is a nightmare.

Use IFS() instead: it reads top to bottom like a checklist, and you do not have to count closing parentheses.

• Nested IF: =IF(B2>=75,"Distinction",IF(B2>=50,"Pass","Fail"))
• IFS version: =IFS(B2>=75,"Distinction",B2>=50,"Pass",TRUE,"Fail")

VLOOKUP vs XLOOKUP — which to use

VLOOKUP searches the first column of a table and returns a value from a column to its right. It cannot look left, and it breaks silently if you insert a new column.

XLOOKUP fixes both: you specify the lookup column and the return column by name, so it survives structural changes.

• VLOOKUP: old standard, but fragile
• XLOOKUP: modern standard, flexible and safe
• Use XLOOKUP unless you are in an old version of Excel that does not support it (pre-2019)

PivotTables: turn rows into answers

A PivotTable summarises thousands of rows into a small grid in seconds. Drag a category field into Rows, a numeric field into Values, and it sums by default.

• Select any cell in your table → Insert > PivotTable
• Drag category to Rows (e.g. Region, Product)
• Drag number to Values (e.g. Sales) — defaults to Sum
• Drag a second category to Columns (e.g. Month) for a cross-tab