Lookup & Matching
Pull values from other tables, match records, and compare lists.
Compare Two Columns and Find Differences
Compare two columns row by row and flag matches and mismatches with a simple IF formula that works in Excel and Google Sheets.
=IF(A2=B2,"Match","Mismatch")VLOOKUP with an Exact Match
Look up a value in another table and return the matching result — with the FALSE argument that stops VLOOKUP returning wrong answers.
=VLOOKUP(E2,A2:B10,2,FALSE)XLOOKUP: The Modern Lookup Formula
XLOOKUP replaces VLOOKUP with a simpler, safer lookup: exact match by default, looks in any direction, and has a built-in not-found message.
=XLOOKUP(E2,A2:A10,B2:B10,"Not found")INDEX + MATCH Lookup
The classic flexible lookup: MATCH finds the row, INDEX returns the value. Works in every Excel version and looks in any direction.
=INDEX(B2:B10,MATCH(E2,A2:A10,0))Find Values Missing From Another List
Check every value in one list against another and flag the ones that don't appear anywhere in the second list.
=IF(COUNTIF(B:B,A2)=0,"Missing","In list")Pull the Latest Record for a Name
Get the most recent entry for a person or item with XLOOKUP searching bottom-up — perfect for logs where new rows land at the end.
=XLOOKUP(E2,A:A,B:B,"",0,-1)XLOOKUP vs VLOOKUP: Which Lookup to Use
Both pull a matching value from another table — XLOOKUP does it with safer defaults. Here's when each one wins, and the traps when you switch.
=XLOOKUP(E2,A2:A5,C2:C5)