Why does XLOOKUP return #N/A when the value is clearly in the list? Why does it work in column D but fail in column E—even though both look identical? Why did your colleague’s version pull the bonus % correctly while yours pulled the department name instead?
The answer isn’t ‘you typed it wrong.’ It’s that XLOOKUP doesn’t just find values — it matches positions, respects direction, and treats empty cells like landmines. I found this out last Thursday at 4:17 p.m., staring at a payroll sheet for Acme Corp while my manager waited for headcount validation.
The Setup
We’re working with a small but realistic HR dataset: 9 employees, three columns — full name (A2:A10), department (B2:B10), and base salary (C2:C10). No headers in row 1; everything starts at A2. This is how most exported CSVs land in our inbox.
| Full Name | Department | Base Salary |
|---|---|---|
| Sarah Chen | Finance | $82,500 |
| Diego Morales | Engineering | $114,200 |
| Priya Kapoor | Marketing | $76,800 |
| Marcus Bell | HR | $69,300 |
| Aisha Johnson | Finance | $91,000 |
| Takumi Sato | Engineering | $107,400 |
| Lena Petrova | Marketing | $72,100 |
| Jamal Wright | Finance | $85,600 |
| Elena Ruiz | HR | $67,900 |
The Challenge
You need to build a lookup table in F2:F10 that returns each employee’s department — but using only XLOOKUP, not VLOOKUP or INDEX/MATCH. The catch? Your source list (A2:C10) isn’t sorted. And someone pasted a stray space after ‘Finance ’ in B5 — yes, row 5, right before Aisha Johnson’s entry. Also, you’re told to deliver this by 3 p.m., and you’ve already tried =XLOOKUP(E2,A2:A10,B2:B10) twice. Both times, it returned #N/A for Aisha.
This isn’t about syntax. It’s about how XLOOKUP handles exact match logic, default search direction, and whitespace sensitivity — none of which show up in the tooltip.
Walking Through It
Start in cell F2. You want to look up E2 (which contains “Aisha Johnson”) in column A, and return the matching department from column B.
Step 1: The bare-minimum formula
Enter: =XLOOKUP(E2,A2:A10,B2:B10)
Result: #N/A
Why? Because XLOOKUP defaults to exact match — good — but also defaults to searching top-to-bottom. That’s fine. But here’s what no one tells you: if there’s even one trailing space in the lookup array (and there is — B5 has “Finance ”), XLOOKUP still finds the match in column A… but then tries to return the *corresponding* value from column B, which now includes that invisible space. And if your lookup value (“Finance”) doesn’t have the space, it fails on the return side — silently.
Step 2: Fix the match behavior
Update to: =XLOOKUP(E2,A2:A10,B2:B10,,0)
The fourth argument is if_not_found — left blank. The fifth is match_mode. Setting it to 0 forces exact match (already default), but adding it makes the formula more readable and avoids accidental mode changes later.
Still #N/A? Yes — because the problem isn’t match mode. It’s data cleanliness.
Step 3: Add search_mode — and uncover the real issue
Now try: =XLOOKUP(E2,A2:A10,B2:B10,,0,1)
The sixth argument is search_mode. 1 = top-to-bottom (default). -1 = bottom-to-top. Try -1: =XLOOKUP(E2,A2:A10,B2:B10,,0,-1)
It works. Why? Because when searching bottom-up, XLOOKUP hits Jamal Wright first (row 8, Finance), skips the corrupted “Finance ” in row 5, and pulls B8 — clean, no space.
But that’s fragile. Better fix the root cause.
Step 4: Clean the data inline
Final working version:=XLOOKUP(E2,A2:A10,TRIM(B2:B10),,0)
Yes — you can wrap the return_array in TRIM(). It works. No helper column needed. Just make sure you press Ctrl+Shift+Enter if you’re on older Excel (pre-365); otherwise, it spills cleanly. On Excel for Microsoft 365, it auto-arrays.
| Lookup Value (E2:E10) | Formula Used | Result |
|---|---|---|
| Aisha Johnson | =XLOOKUP(E2,A2:A10,TRIM(B2:B10),,0) | Finance |
| Diego Morales | =XLOOKUP(E2,A2:A10,TRIM(B2:B10),,0) | Engineering |
| Priya Kapoor | =XLOOKUP(E2,A2:A10,TRIM(B2:B10),,0) | Marketing |
| Marcus Bell | =XLOOKUP(E2,A2:A10,TRIM(B2:B10),,0) | HR |
The Result
Here’s what lives in F2:F10 after applying =XLOOKUP(E2,A2:A10,TRIM(B2:B10),,0) down the column:
| Employee | Department |
|---|---|
| Aisha Johnson | Finance |
| Diego Morales | Engineering |
| Priya Kapoor | Marketing |
| Marcus Bell | HR |
| Sarah Chen | Finance |
| Takumi Sato | Engineering |
| Lena Petrova | Marketing |
| Jamal Wright | Finance |
| Elena Ruiz | HR |
What Could Go Wrong
These aren’t theoretical. Each one cost me at least 22 minutes last week.
Mistake #1: Forgetting that XLOOKUP returns the first match — even if duplicates exist
You have two ‘Sarah Chen’ entries — one in row 2 ($82,500), another in row 12 ($89,000, promoted). If you use =XLOOKUP(“Sarah Chen”,A2:A20,C2:C20), you’ll always get $82,500 — never the newer value. There’s no built-in ‘last match’ option. To get the latest, sort descending first or add FILTER() around it.
Mistake #2: Using entire columns (A:A) with dynamic arrays
Try =XLOOKUP(E2,A:A,B:B) in Excel 365. It’ll work — until you insert a row above row 1. Then the whole column reference shifts and breaks every dependent formula. Always use explicit ranges like A2:A1000 — or better, convert to a Table (Ctrl+T) and use structured references like Table1[Name].
Mistake #3: Assuming wildcards work without setting match_mode to 2
You type =XLOOKUP(“Fin*”,A2:A10,B2:B10) hoping to match “Finance”. Nope. Wildcards only activate when you explicitly set match_mode = 2 (wildcard match). So it’s =XLOOKUP(“Fin*”,A2:A10,B2:B10,,2). Without that 2, it looks for the literal asterisk.
One last shortcut worth memorizing: Press Alt+M+V to open the Evaluate Formula dialog — then step through each part of your XLOOKUP to see exactly where the mismatch happens. Do this before blaming the data.
