Press Enter to search  ·  Esc to close

Claude AI — Excel Course
Use Claude to Write an XLOOKUP Formula in Excel
Progress1 of 4
Section 01

XLOOKUP is better than VLOOKUP — here's how to write it with Claude.

XLOOKUP is the modern, better replacement for VLOOKUP. It's more flexible, easier to read, and available in Excel 365 and Excel 2019+. Yet many users still struggle with it — the argument order is different, the match modes are confusing, and #N/A errors appear without clear explanation.

Claude handles XLOOKUP very well. Describe what you want to look up and what you need to return, and it writes the correct formula with the right match mode and error handling. If your existing XLOOKUP is broken, paste it in and Claude will diagnose and fix it quickly.

Home
Insert
Formulas
Data
E2
fx
=IFERROR(XLOOKUP(B2, Staff!$A$2:$A$500, Staff!$D$2:$D$500, "", 0), "")

XLOOKUP vs VLOOKUP — why XLOOKUP wins:

Feature
VLOOKUP
XLOOKUP
Return column
Column index number — breaks if columns are inserted
Direct range reference — never breaks
Look left
Not possible — lookup must be leftmost column
Yes — lookup and return can be any columns
Missing values
Needs IFERROR wrapper to avoid #N/A
Built-in 4th argument handles missing values
Match mode
TRUE/FALSE — confusing, defaults to approximate
0=exact, -1=next smaller, 1=next larger, 2=wildcard
XLOOKUP is available in Excel 365 and Excel 2019+. On older versions, use INDEX/MATCH instead — Claude handles both.
  • You describe what you need to find and Claude writes the XLOOKUP formula with all arguments correct
  • Claude chooses the right match mode — exact, approximate, or wildcard
  • Claude handles complex cases — looking left, returning multiple columns, nested XLOOKUPs
  • Claude converts old VLOOKUPs to XLOOKUP cleanly, including column index fixes

Not on Excel 365 or 2019? Tell Claude your Excel version and it will write INDEX/MATCH instead — which works in all versions and has the same advantages over VLOOKUP. Claude handles the conversion automatically.

Six steps — from describing your lookup to a tested, production-ready formula.

Summary
Staff
Raw_Data
E2
fx
=XLOOKUP(B2, Staff!$A:$A, Staff!$D:$D, "", 0)
1
Name
Emp ID ← lookup
Dept
Salary ← return
2
Sarah M.
EMP-042
Finance
£52,000
3
James T.
EMP-017
HR
£44,500
4
Priya K.
EMP-099
Ops
£48,200
5
David R.
EMP-???
Sales
"" (not found)
01

Clearly define what you need

Tell Claude the lookup value, the column where it's located, and the column you want to return. One sentence is enough: "Look up employee ID in column A of the Staff sheet and return their salary from column D."

02

Provide the data location

Specify the sheet name, the range, and whether the data has a fixed size or grows. Claude uses this to write references that won't break when rows are added.

03

Get the formula

Claude writes the full XLOOKUP, explains the key arguments, and chooses the right match mode — exact match for IDs and codes, approximate for tiered pricing or grades, wildcard for partial text searches.

04

Test immediately

Always test with a value you know should work. If it returns #N/A, send Claude the formula and 3 sample rows. The cause is almost always a data type mismatch or trailing space — Claude finds it instantly.

05

Add error handling

Once the formula works, wrap it with IFERROR to return a blank, dash, or "Not Found" instead of #N/A for genuinely missing values. Or use the built-in 4th argument of XLOOKUP — Claude will show you both options.

06

Convert old VLOOKUPs

Paste any existing VLOOKUP formulas into Claude and ask it to rewrite them as XLOOKUP. Claude handles the conversion cleanly — including fixing the hardcoded column index numbers that break when columns are inserted.

Tip: XLOOKUP can return multiple columns at once. If you need both name and salary from a lookup, tell Claude and it will write a single formula that spills both results automatically — no need to write two separate formulas.

Copy these prompts into Claude. Replace the bracketed placeholders with your actual data and column references.

Build from scratch

Prompt — paste into Claude
I need an XLOOKUP formula. I want to look up [lookup value / cell reference] in [lookup column/range] and return the value from [return column/range]. Data is on sheet [name], rows 2 to [last row]. Please write the XLOOKUP and add an IFERROR wrapper for missing values.

Debug a broken XLOOKUP

Prompt — paste into Claude
My XLOOKUP is returning #N/A. Here is the formula: [paste formula] The lookup value is [value] and it exists in the table. Here are 3 sample rows from the lookup column: [paste rows] Please identify why it's failing and give me the corrected formula.

Convert VLOOKUP to XLOOKUP

Prompt — paste into Claude
Please convert this VLOOKUP to XLOOKUP: [paste VLOOKUP formula] Data is on sheet [name]. Keep any existing IFERROR wrapper.
Checklist
Lookup value, lookup column, and return column described clearly to Claude
Sheet name and data range specified
Formula tested with at least one known value
#N/A investigated if it appears — data type or trailing spaces likely
IFERROR wrapper added for production use
Old VLOOKUPs converted to XLOOKUP where relevant
XLOOKUP availability: XLOOKUP requires Excel 365 or Excel 2019+. If you or your colleagues are on Excel 2016 or older, use INDEX/MATCH instead — tell Claude your version and it will write the right formula automatically.