Lookups: XLOOKUP and VLOOKUP

Why it matters

Real data rarely lives in one table. Orders list an item; a separate table lists each item's supplier and lead time. Test results list a sample ID; another sheet lists which patient or batch it belongs to. A lookup brings a value from one table into another by matching a shared key, the spreadsheet version of a database join.

The core idea: match a key, return a value

You need:

• a lookup value (the key in this row, such as the item name),
• a lookup column in the other table where that key appears once,
• a return column holding the value you want.

Keys must match exactly. "Pipette tips (rack)" and "pipette tips(rack)" are different keys to a lookup (case usually does not matter, but spaces and punctuation do). Codes such as SKU-1042 make better keys than free-text names.

The practice tables

Orders (from earlier lessons) has Item in column B. Add a sheet named Items:

Note that "Marker pens (pack)" is missing on purpose.

XLOOKUP (modern and recommended)

=XLOOKUP(B2, Items!$A$2:$A$8, Items!$B$2:$B$8, "Not listed")

Read it as: find B2 in the Items item column, return the matching Supplier, and if there is no match, show "Not listed". The ranges are absolute so the formula can be filled down. For the first order it returns SafeHands; for the marker pens it returns Not listed.

XLOOKUP is available in Google Sheets, Excel 2021 and Microsoft 365, and LibreOffice Calc 24.8 or newer. It matches exactly by default, can look left or right, and does not break when columns are inserted.

VLOOKUP (older, everywhere)

=VLOOKUP(B2, Items!$A$2:$C$8, 2, FALSE)

Arguments: the value, the whole table range, the column number within that range to return (2 = Supplier, 3 = LeadTimeDays), and FALSE for exact match. VLOOKUP works in every version of every tool, so you will meet it in older files.

Its two traps:

1. Leaving off FALSE. The default is an approximate match, which assumes sorted data and can return a wrong row with no error. Always write FALSE (or 0) for exact matching.
2. The key must be the leftmost column of the range, and the column number is a hard-coded count. Insert a column into the Items table and 2 may now point at the wrong field.

INDEX and MATCH together (=INDEX(Items!$B$2:$B$8, MATCH(B2, Items!$A$2:$A$8, 0))) are the classic alternative that avoids both traps in older tools.

Handling missing matches

A failed VLOOKUP returns #N/A. Wrap it to show something readable:

=IFERROR(VLOOKUP(B2, Items!$A$2:$C$8, 2, FALSE), "Not listed")

Be careful: IFERROR hides every error, including a typo in your formula. Use it once you know the formula is right, and then count the "Not listed" rows with COUNTIF. A missing match is information: here it tells you the Items table needs a new row.

Worked example: expected arrival date

Add Supplier (G) and LeadTimeDays (H) to Orders with XLOOKUP, then Arrives (I):

G2: =XLOOKUP(B2, Items!$A$2:$A$8, Items!$B$2:$B$8, "Not listed")
H2: =XLOOKUP(B2, Items!$A$2:$A$8, Items!$C$2:$C$8, "")
I2: =IF(H2="", "", A2+H2)

Dates are numbers of days underneath, so adding 3 to 2026-09-02 gives 2026-09-05. Format column I as a date.

Common mistakes

• Trailing spaces or different spellings in keys: fix them with the cleaning techniques in the next lesson.
• Relative ranges that slide down as you fill (A2:A8 becomes A3:A9). Use $.
• Duplicate keys in the lookup table: the lookup silently returns the first match.
• VLOOKUP without FALSE.

Quick recap

• A lookup matches a key in another table and returns a value from the same row.
• XLOOKUP(value, lookup_range, return_range, if_not_found) is the modern choice.
• VLOOKUP(value, table, column_number, FALSE) works everywhere; never forget FALSE.
• Missing matches are data-quality signals; handle them visibly.

Try it

Open the Do tab to join the Orders and Items tables.