GUIDEDeep dive
VLOOKUP, XLOOKUP and INDEX+MATCH
VLOOKUP only looks right and breaks when columns are inserted; XLOOKUP is safer; INDEX+MATCH works both directions and on older versions. Confirm exact versus approximate matching first.
Choosing
Prefer XLOOKUP in newer versions.
Use INDEX+MATCH on older versions or for left lookups.
VLOOKUP only fits fixed columns to the right.
Common errors
Omitting FALSE and getting approximate matches.
Lookup values with spaces or type mismatches.
Counting the return column incorrectly.
FAQ
What if XLOOKUP finds nothing?
Use the fourth argument for a default such as "not found".
Can I look up with multiple criteria?
Yes; use a helper key with XLOOKUP, or FILTER/SUMIFS.
Sources
Related calculators
Excel Functions: 1060 Gamified Cards1060 Excel cards: 60 function basics plus 1000 range/constant formula-output questions, in 53 levels with spaced repetition.More guides
Hiragana vs KatakanaRead guideSpaced Repetition and the Forgetting CurveRead guideOn Readings vs Kun ReadingsRead guideAn N5 Kanji Study OrderRead guideKEEP READING
Keep reading
Read in order, or jump to another guide on the same topic.
← PreviousExcel Formula BasicsReferences, absolute/relative, errors and IF.Next →The Word/PPT Shortcuts Worth LearningSave, undo, formatting, slideshow and alignment.