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.
Table of Contents
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.
Official documentation
For the full reference behind this guide, read Microsoft’s reference for the XLOOKUP function. It is the source the details above are drawn from, and the first place to check when an option or attribute name changes.
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.
Last updated on · Written by Dinesh Kumar R
