Quick answer: Five formulas cover most real Excel work: XLOOKUP (find anything in a table — the modern replacement for VLOOKUP), IF (decisions), SUMIF(S)/COUNTIF(S) (conditional totals), TEXT (format numbers into labels), and IFERROR (clean results instead of #N/A). This guide gives the syntax, a worked example of each, and the mistakes that break them. Beginner setup lives in our Excel shortcuts guide.
XLOOKUP: the one lookup to learn in 2026
=XLOOKUP(lookup_value, lookup_array, return_array, "Not found")Example: find a student’s grade from an ID — =XLOOKUP(A2, Students!A:A, Students!C:C, "No match"). Unlike old VLOOKUP, XLOOKUP can look left, defaults to exact match, and takes a friendly not-found value instead of wrapping in IFERROR. If your Excel predates it, the legacy form is =VLOOKUP(A2, Students!A:C, 3, FALSE) — same idea, with the column number counted from the lookup column and exact match forced with FALSE.
IF and friends: making sheets decide
=IF(B2>=40, "Pass", "Fail")
=IFS(B2>=90,"A", B2>=75,"B", B2>=40,"C", TRUE,"F") // graded bands
=SUMIF(Region, "Chennai", Sales) // total for one city
=COUNTIF(B:B, ">80") // count above 80
=SUMIFS(Sales, Region, "Chennai", Month, "Jan") // multiple conditionsThe pattern to internalize: the plural -IFS versions take (sum_range, condition1, range1, condition2, range2…) — conditions come as range/criteria pairs. Also note text criteria like ">80" live inside quotes; this trips everyone once.
The formula mistakes that break sheets
| Symptom | Cause | Fix |
|---|---|---|
| #N/A on values that clearly exist | Lookup column contains text-numbers or stray spaces | Clean with TRIM; store numbers as numbers (ISNUMBER to test) |
| Formula copies wrong as you drag | References shifted | Lock with $: $A$2 (F4 toggles), or use table references |
| Dates behave like text | Regional format mismatch | Re-enter with DATE(); never type dates as “12.01.2026” text |
| Slow file with many lookups | Whole-column references over huge ranges | Use Excel Tables (Ctrl+T) — faster and self-expanding |
These five, plus structured references on Tables, handle attendance registers, grade books, sales trackers and budget sheets — the everyday files real jobs run on. For formula practice with an instructor reviewing your logic, Ampersand Academy teaches Advanced Excel one-to-one.
Frequently asked questions
What is XLOOKUP in Excel?
XLOOKUP is the modern replacement for VLOOKUP and HLOOKUP: it searches one range for a value and returns the matching value from another range, supports exact match by default, can look in any direction, and accepts a custom not-found message.
Why does my VLOOKUP return #N/A?
Usually the lookup value and table data differ invisibly – stray spaces, numbers stored as text, or a non-exact match mode. Clean with TRIM, convert types with VALUE, and always force FALSE for exact match.
What does the dollar sign do in Excel formulas?
$A$2 locks the reference so it does not shift when copied. A locks the column only, A1 the row only. Press F4 while editing a reference to cycle through the four modes.
How do I sum values with multiple conditions?
Use SUMIFS: =SUMIFS(Sales, Region, Chennai, Month, Jan) sums Sales where both conditions hold. The sum range comes first, then condition/range pairs.
Is Excel still worth learning in 2026?
Yes – spreadsheets remain the universal tool of business, and advanced Excel skills (lookups, Power Query, pivot models) are listed in analyst, finance and operations roles everywhere.