Most lookup problems are not lookup problems. The formula is fine — your join key has a trailing space, a number stored as text, or two columns that only look identical. These five ChatGPT prompts help you write the right formula and, more often, find out why it keeps failing.
What to know before you paste
- ChatGPT cannot open your workbook, so describe the layout: which column holds the key, which column holds the value you want back, and which row the data starts on.
- Say which tool you use. Excel, Google Sheets, and LibreOffice differ, and older Sheets files may not support XLOOKUP at all.
- Paste two or three sample rows with fake data. Real column names and types catch mismatches the model would otherwise guess past.
The prompts
1. Write the formula from a sheet description
I am working in [Excel or Google Sheets]. My lookup key is in [key column] on sheet [source sheet], and I want the matching value from [return column]. The data starts at row [first data row]. Write the XLOOKUP formula and a VLOOKUP version, then tell me which one is safer here and why.What it does: Builds both formulas from your layout so you can compare them.
How to use: replace [Excel or Google Sheets], [key column], [source sheet], [return column], and [first data row].
2. Fix a #N/A that will not go away
My lookup returns #N/A. Key column: [key column]. Values in it come from [where the key came from]. Target table is on sheet [sheet name]. List the five most likely causes in order, and give me a formula that will tell me which one it is, such as a check for trailing spaces or text-vs-number type.What it does: Turns a vague #N/A into a ranked list of causes plus a diagnostic formula.
How to use: replace [key column], [where the key came from], and [sheet name]; paste a sample of your key values.
3. Convert a VLOOKUP to XLOOKUP
Here is a VLOOKUP I want to modernize: [paste VLOOKUP formula]. Rewrite it as XLOOKUP with the same result. Set the not-found argument to [not-found value], and explain any place where the new formula behaves differently from the old one.What it does: Upgrades a fragile column-count-dependent lookup into a stable one.
How to use: replace [paste VLOOKUP formula] and choose a [not-found value] like “Not found” or 0.
4. Two-way lookup by row and column
I need a value at the intersection of a row and a column. Row headers are in [row header range], column headers in [column header range], and the data block is [data range]. Write a formula that looks up [row key] and [column key] together, and tell me what to change if my headers are exact matches versus partial.What it does: Handles matrix lookups where a single key is not enough.
How to use: replace the three ranges plus [row key] and [column key] with your real cell references.
5. Lookup with more than one condition
I want the value where two columns match at once: [condition column 1] equals [value 1] and [condition column 2] equals [value 2]. The same lookup key can appear more than once. Give me the formula, and tell me whether it returns the first or last match, since that matters for [why it matters].What it does: Returns the right row when one key is not unique.
How to use: replace the two condition columns, their values, and [why it matters] (for example, “the latest invoice”).
FAQ
Is XLOOKUP always better than VLOOKUP?
Nearly always. It looks left or right, survives a new column, and has a built-in not-found result. Use VLOOKUP only when an older file must keep working.
Why does my lookup fail only for some rows?
That pattern usually points to a data type mismatch. A number stored as text will not match the same number stored as a value, so check the key column’s type.
Can ChatGPT handle approximate matches and ranges?
Yes. Tell it what should happen between the key values — exact only, next smaller, or next larger — and it will set the match mode for you.
What is the fastest way to clean up trailing spaces?
Ask for a formula using TRIM or a helper column, then paste values back. The prompt can return both the cleanup step and the lookup so you do it in one pass.
Do these prompts work in Google Sheets?
Yes, when you name it as the tool. Some functions differ, and XLOOKUP is missing from very old Sheets files, so the model will suggest INDEX and MATCH instead.
GuPrompt keeps every prompt copy-paste ready. No account needed.