What Most People Miss About How to Do a Lookup Function in Excel

Yes, you can do a lookup function in Excel. But if you’re still using VLOOKUP without checking for exact matches or handling #N/A errors, you’ve already shipped incorrect data to your manager.

The Problem

You’re handed a sales report from Finance: 87 rows of order IDs, customer names, and product SKUs — but no prices. Meanwhile, Procurement sent a separate price list (124 rows) with SKUs and unit costs. You need to add prices to the sales report. Fast.

You try =VLOOKUP(C2,PriceList!A:B,2,FALSE) in column D. It works… until row 43. Then it returns #N/A. You scroll through PriceList and spot ‘SKU-7782’ — but it’s spelled ‘SK7782’ in the sales sheet. No warning. No highlight. Just silence and a wrong number two rows later because someone copy-pasted a formula down without locking ranges.

Here’s what your raw data actually looks like — uncleaned, mismatched, and waiting to derail your weekly forecast:

Order ID	Customer	SKU	Qty	Price (VLOOKUP result)
ORD-2024-001	Sarah Chen	SKU-7782	12	#N/A
ORD-2024-002	Acme Corp	SKU-9105	5	$45.20
ORD-2024-003	BlueSky Ltd	SKU-7782	8	#N/A
ORD-2024-004	Nexus Labs	SKU-2241	21	$112.60
ORD-2024-005	Veridian Group	SKU-9105	3	$45.20
ORD-2024-006	TerraForm Inc	SKU-7782	17	#N/A
ORD-2024-007	Luna Dynamics	SKU-5519	9	$88.95

That #N/A isn’t just noise. It means your gross margin calculation for those three orders will be zero — not blank, not flagged, just mathematically wrong. And yes, this happened on our Q3 board deck last year. (Trust me, I learned this the hard way.)

The Solution

We’ll use XLOOKUP — available in Excel 365 and Excel 2021+. If you’re stuck on 2016 or earlier, skip ahead to the “Going Further” section — but know this: XLOOKUP fixes *all* the silent failures above in one formula.

Verify your lookup range is clean: In PriceList sheet, select A1:B124. Press Ctrl + G → Special → Blanks. Delete any empty rows inside the data block. Then sort by SKU (Column A) — yes, even if it seems sorted. Excel lies about that sometimes.
Type the XLOOKUP formula in D2:
=XLOOKUP(C2,PriceList!A:A,PriceList!B:B,"Not found",0,1)
Break it down:
• C2 = what you’re looking for (SKU)
• PriceList!A:A = where to search (SKU column)
• PriceList!B:B = what to return (Price column)
• "Not found" = fallback text instead of #N/A
• 0 = exact match only (critical — no more fuzzy matches)
• 1 = search mode: 1 = first-to-last (default), -1 = last-to-first
Drag down — but don’t just drag: Select D2:D87, then press Ctrl + D. This fills all cells *at once*, avoiding relative reference drift if you accidentally click elsewhere mid-drag.

Here’s what your corrected table looks like now — no surprises, no omissions:

Order ID	Customer	SKU	Qty	Price (XLOOKUP)
ORD-2024-001	Sarah Chen	SKU-7782	12	$63.40
ORD-2024-002	Acme Corp	SKU-9105	5	$45.20
ORD-2024-003	BlueSky Ltd	SKU-7782	8	$63.40
ORD-2024-004	Nexus Labs	SKU-2241	21	$112.60
ORD-2024-005	Veridian Group	SKU-9105	3	$45.20
ORD-2024-006	TerraForm Inc	SKU-7782	17	$63.40
ORD-2024-007	Luna Dynamics	SKU-5519	9	$88.95

Notice how every SKU-7782 now returns $63.40 — consistently, reliably, and without needing to retype anything. That’s not magic. It’s just XLOOKUP respecting your intent.

Going Further

What if you’re on Excel 2016? Use INDEX/MATCH — and yes, it’s worth memorizing. Here’s the equivalent for C2:
=IFERROR(INDEX(PriceList!B:B,MATCH(C2,PriceList!A:A,0)),"Not found")

Two things most people miss: First, MATCH(...,0) forces exact match — leave out the zero and you get approximate matching (dangerous with text). Second, wrap it in IFERROR. Always. Never let #N/A leak into reports.

Need to look up based on *two conditions*? Like finding price for SKU-7782 *only* when Region = "APAC"? Use this:
=XLOOKUP(1,(PriceList!A:A=C2)*(PriceList!C:C="APAC"),PriceList!B:B,"Not found",0)

That (A:A=C2)*(C:C="APAC") creates an array of 1s and 0s — and XLOOKUP finds the first 1. Works like a charm. (Pro tip: Don’t use whole columns like A:A in huge files — switch to A2:A1000 if your data stops there. Speed matters.)

And here’s the counterintuitive one: If your lookup value has trailing spaces — say, "SKU-7782 " — TRIM() *inside* XLOOKUP won’t help. Why? Because XLOOKUP searches the *entire range*, and if PriceList!A:A contains untrimmed values too, you’ll still miss matches. Fix the source: Select PriceList!A:A, press Alt + H + F + D (Find & Select → Replace), type a space in Find what, leave Replace with blank, click Replace All.

When NOT to Use This

XLOOKUP and INDEX/MATCH are brilliant — but they’re not universal. Avoid them when:

You’re pulling data from a live database via Power Query. Let Power Query handle joins — it’s faster, auditable, and refreshes cleanly. Formulas just add fragility.
Your lookup table has duplicate keys and you need *all* matching rows (not just the first). Use FILTER() instead: =FILTER(PriceList!A:C,PriceList!A:A=C2) returns full rows where SKU matches.
You’re doing time-based lookups (e.g., “find price effective as of 2024-03-15”) and your date column isn’t sorted. XLOOKUP’s -1 search mode requires sorted data for binary search — but if it’s unsorted, set search_mode to 1 (linear) and accept the slower speed.
You’re sharing files with colleagues on Excel 2013 or older. They’ll see #NAME? and panic. Stick to VLOOKUP + IFERROR — ugly but universal.

Also: Never use lookup functions to validate user input in forms. Use Data Validation with List or Custom formulas instead. Lookup functions recalculate on every edit — validation doesn’t.

Keyboard Shortcuts

Action	Shortcut	Notes
Open Function Arguments dialog	`Shift + F3`	Press after typing `=XLOOKUP(` — shows parameter hints
Select entire column	`Ctrl + Space`	Useful for defining lookup arrays like `A:A`
Fill down formula	`Ctrl + D`	Safer than dragging — avoids accidental cell reference shifts
Find & Replace	`Ctrl + H`	Or `Alt + H + F + D` from Home tab
Toggle absolute/relative refs	`F4`	Press while editing a cell reference — cycles $A$1 → $A1 → A$1 → A1