Intermediate Excel · Interactive lesson 4 of 12
Lookup formulas
A lookup formula reads a reference table sitting somewhere else on the sheet, so the order table itself never has to hard-code a price or category by hand — change the reference table once, and every order that points at it updates too.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialLookup Formulas in Excel: XLOOKUP, VLOOKUP and INDEX/MATCHSkill check
Try these first:
- Explain what "exact match" means when looking a value up in a table.
- Write an
XLOOKUPthat pulls a price from a separate reference table. - Explain why
VLOOKUPis considered a legacy skill next toXLOOKUP. - Handle a lookup value that isn't in the table without the formula showing
#N/A.
All familiar? Skip ahead to I5 — Text & Date Functions.
Targeted teach
What you're learning
- Exact match thinking
- A lookup scans a range for a value that matches exactly, then returns whatever sits in the same position of a second range.
XLOOKUP=XLOOKUP(lookup_value, lookup_array, return_array)— no column counting, and it can look in any direction.VLOOKUP— legacy- Older and still common:
=VLOOKUP(value, table, column_number, FALSE). It only looks rightward and breaks silently if a column gets inserted — worth recognizing, not worth reaching for first. - Lookup table setup
- Keep the reference table's lookup column free of duplicates, and keep the table itself out of the way of the data that reads from it.
- Missing-value handling
- Add a fourth argument to
XLOOKUP—=XLOOKUP(A2, F2:F5, H2:H5, "Not found")— instead of letting an unmatched value show#N/A. - Don't hard-code it
- Typing "18" because that's what Widget B costs today breaks the moment the price changes — the lookup is what keeps it current.
Product list — lives in F2:H5
| F — Product | G — Category | H — Price |
|---|---|---|
| Widget A | Hardware | $12 |
| Widget B | Hardware | $18 |
| Cable Tie | Accessories | $3 |
| Power Strip | Electronics | $22 |
Where to find it in Excel
Formulas›Lookup & Reference›XLOOKUP
| A | B | C | D | E | F | G | H | ||
|---|---|---|---|---|---|---|---|---|---|
| 1 | Product | Price | Category | Product | Category | Price | |||
| 2 | Widget B | 18 | Widget A | Hardware | 12 | ||||
| 3 | Power Strip | 22 | Electronics | Widget B | Hardware | 18 | |||
| 4 | USB Hub | Not found | Cable Tie | Accessories | 3 | ||||
| 5 | Power Strip | Electronics | 22 | ||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 |
Swipe sideways to see the whole window.
- 1Formulas tab.
- 2Lookup & Reference lists XLOOKUP, VLOOKUP, INDEX, MATCH and XMATCH.
- 3The formula: what to find (A2), where to look (F2:F5), what to return (H2:H5).
- 4Keep the reference table in its own block, here F2:H5.
- 5A fourth argument, "Not found", replaces #N/A for a missing product.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided practice
An order table below, referencing the product list above. Fill in the dashed cells.
Independent variation
Pull real fields from a real list
Build a reference table of your own — products, employees, anything with a lookup column — and use XLOOKUP to pull two or three fields into a second table by matching on one column. Add a fourth "not found" argument so a typo doesn't produce a raw #N/A.
Spot a mistake or have an idea for this lesson? Send feedback