Module 2 · The formulas you will actually use Lesson 6 of 15
Looking things up — XLOOKUP and INDEX-MATCH
VLOOKUP only searches left to right and breaks when a column is inserted. XLOOKUP is the modern replacement in Microsoft 365, and INDEX-MATCH is the version that works everywhere, including older Excel and Google Sheets.
The concept
A lookup fetches a value from another table by a key: give it an employee id, get the name. XLOOKUP(id, ids, names) is the modern form and reads left to right, with an optional fourth argument for the answer when nothing matches. INDEX and MATCH together do the same job in every version of Excel.
Wrap it so a miss is readable: IFERROR(XLOOKUP(...), 'not found'). Lookups are also the honest way to bring data from a second sheet into a report — code in one sheet, descriptions in another — instead of retyping them, which is where hand-maintained spreadsheets go wrong.
=XLOOKUP(A2, ids, names) by column NAME, not a number
=XLOOKUP(A2, ids, names, "not found") avoids #N/A
=XLOOKUP(A2, ids, Table2, , , 1) return several columns
# Older Excel:
=INDEX(names, MATCH(A2, ids, 0))
# Never VLOOKUP with a hand-typed column number: it breaks on insert.Common mistakes
The mistake is VLOOKUP with a hard-coded column number, which breaks the moment somebody inserts a column between you and it. And a lookup key with a trailing space — "Pune " against "Pune" — returns nothing; TRIM both sides of the comparison.
Worked example
=XLOOKUP(A2,Employees!A:A,Employees!C:C,"not found") returns the department for an employee code, and — unlike VLOOKUP — returns a readable message instead of #N/A when the code is missing.
Your practice task
Join two sheets: pull the department and manager for 200 employee codes, count how many codes did not match, and fix the mismatches before reporting anything.
Do this before moving on. Reading a solution teaches the shape of it; typing it teaches you.
Key takeaway
A lookup fetches a value from another table by a key: give it an employee id, get the name.
Keep your place in this course
A free account saves which lessons you have finished and unlocks the certificate. The lesson itself stays free to read.
Create free account or sign inCourse complete
You finished every lesson.
Your certificate is ready, and the projects you built are the proof to attach to an application.
Almost there
15 lessons left of 15
Finish every lesson to unlock the certificate. Each one ends in a practice task — do them and the final project brief writes itself.