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)INDEX MATCH vs XLOOKUP: Which Lookup to Use
Use XLOOKUP when you have it — INDEX MATCH remains the answer for older Excel and true two-way row-and-column lookups. Both verified side by side below.
=XLOOKUP(E2,A2:A5,C2:C5)VLOOKUP vs HLOOKUP: Which One Fits Your Table
VLOOKUP reads tables that run down the page — nearly all of them; reach for HLOOKUP only when your headers run across the top.
=VLOOKUP(E2,A2:C5,3,FALSE)Two-Way Lookup with INDEX and MATCH
Pull a value from a grid by matching both a row label and a column header — one MATCH finds the row, another finds the column.
=INDEX(B2:D5,MATCH(F2,A2:A5,0),MATCH(G2,B1:D1,0))Look Up a Value Using Multiple Criteria
Match on two columns at once — like region AND product — by joining them into one helper key and looking that up with XLOOKUP.
=XLOOKUP(F2&"|"&G2,D2:D5,C2:C5)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)