ChatGPT Prompts for VLOOKUP and Spreadsheet Lookups
20 copy-paste ChatGPT prompts for VLOOKUP, XLOOKUP, INDEX-MATCH: lookup formulas, multi-criteria patterns, troubleshooting common errors, and the workflows that depend on lookups daily.
In short: This page contains 20 copy-paste ready prompts, organized into 5 categories with a description and pro tip for each. The first 5 prompts are free instantly, no signup needed. Hand-curated and tested by the AI Academy team.
VLOOKUP Basics
4 promptsVLOOKUP from Description
1/20✨ What it does
ChatGPT writes a VLOOKUP for [scenario] that pulls [return value] for [lookup value] from [reference table], with each argument explained and a worked example. Fill those four blanks, then paste the formula and test it on two rows you already know.
VLOOKUP for [scenario]. I have [lookup value] and want [return value] from [reference table]. Output: formula syntax, parameter explanation (lookup_value, table_array, col_index, range_lookup), expected behavior. With example.
Pro tip: VLOOKUP signature: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). 4th param: FALSE for exact (default), TRUE for approximate. Most use exact = FALSE.
VLOOKUP Common Errors
2/20✨ What it does
ChatGPT walks a checklist for a VLOOKUP that returns #N/A: the value is missing, stray spaces, text versus number, or a wrong column index. Run the list on the first error cell, then fix the match before you copy the formula down.
VLOOKUP returning #N/A. Causes: value not in table, leading/trailing spaces, text vs number mismatch, wrong column index. Output: troubleshooting checklist.
Pro tip: VLOOKUP #N/A = value not found. Most common cause: trailing whitespace in lookup value. TRIM both sides. Or: number stored as text vs actual number. Check with ISTEXT.
VLOOKUP Limitations
3/20✨ What it does
ChatGPT explains VLOOKUP limits (it cannot look left, approximate match needs a sorted table, inserted columns break it, big data is slow) and points to XLOOKUP or INDEX-MATCH. Switch one formula to XLOOKUP if your Excel has it, then keep VLOOKUP only where you must.
VLOOKUP limitations: can't look left, exact match needs sorted source for approximate, breaks if columns inserted, slow on large data. Output: explanation + workarounds (XLOOKUP, INDEX-MATCH).
Pro tip: VLOOKUP looks right only (lookup col first, return col after). For left lookup: INDEX-MATCH or XLOOKUP. Modern code = avoid VLOOKUP for new work.
VLOOKUP with Wildcards
4/20✨ What it does
ChatGPT shows VLOOKUP asterisk and question-mark wildcards for partial matches, with exact-match syntax and when that fuzzy lookup actually helps. Try a star wildcard on one of your messy keys, then keep exact match on so approximate does not grab a near row.
VLOOKUP with wildcards (* and ?). Output: enable approximate match? (no — wildcards work with exact), syntax, when useful (partial matches, fuzzy lookup). Underused.
Pro tip: Wildcards in VLOOKUP work with exact match (FALSE). =VLOOKUP("Smith*", ...) finds first row starting with Smith. Useful for partial matches. Most don't know.
Prompts get you started. Tutorials level you up.
A growing library of 300+ hands-on AI tutorials. New tutorials added every week.
XLOOKUP (Modern)
4 promptsXLOOKUP from VLOOKUP
5/20✨ What it does
ChatGPT converts a VLOOKUP to XLOOKUP side by side, showing why you skip col_index counting, get exact match by default, and can return arrays. Replace one working VLOOKUP, then compare both results on the same lookup value.
Convert VLOOKUP to XLOOKUP. Output: side-by-side comparison, why XLOOKUP simpler (no col_index counting, exact match default, can return arrays). Modern lookup.
Pro tip: XLOOKUP signature: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Look-up array + return array separate = no column counting.
XLOOKUP with Default Value
6/20✨ What it does
ChatGPT writes an XLOOKUP that uses the if_not_found argument instead of wrapping IFERROR, so a miss returns a clean default. Set that fourth argument to a label you understand, then delete the IFERROR wrap around the old formula.
XLOOKUP with default if not found. Output: 4th parameter (if_not_found) replaces IFERROR wrap. Cleaner than VLOOKUP + IFERROR.
Pro tip: XLOOKUP built-in default = =XLOOKUP(value, lookup, return, "Not Found"). Replaces =IFERROR(VLOOKUP(...), "Not Found"). Cleaner formula.
XLOOKUP Reverse Search
7/20✨ What it does
ChatGPT writes an XLOOKUP with search_mode set to -1 so you get the last match, which is how you pull the most recent record. Point it at a date-ordered list, then check that the last matching row is the one you wanted.
XLOOKUP with reverse search (find last match). Output: 6th parameter search_mode = -1 for reverse. Use case: find most recent matching record.
Pro tip: XLOOKUP search_mode = 1 (default top-to-bottom) or -1 (bottom-to-top). For "find last X" = -1. Useful for time-series data finding latest entry.
XLOOKUP for Multiple Returns
8/20✨ What it does
ChatGPT writes an XLOOKUP that returns several columns at once and spills in modern Excel or Sheets. Select a multi-column return_array, then clear the cells to the right of your formula so the spill has room.
XLOOKUP returning multiple columns. Output: return_array as multi-column range, automatic spill (modern Excel/Sheets), use cases. Powerful array feature.
Pro tip: XLOOKUP can return entire row by passing multi-column range. =XLOOKUP("ID123", IDs, A:E) returns 5 columns. Spills into multiple cells. Modern dynamic array behavior.
INDEX-MATCH
4 promptsINDEX-MATCH from Description
9/20✨ What it does
ChatGPT writes INDEX-MATCH for [scenario] as INDEX of the return range and MATCH of the lookup value, and why that beats a left-looking VLOOKUP. Fill [scenario], then test it on a key that sits to the left of the column you want back.
INDEX-MATCH for [scenario]. Output: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)), explanation, advantages over VLOOKUP. Power-user lookup.
Pro tip: INDEX-MATCH = look-anywhere. INDEX returns value at row/col; MATCH finds row. Powerful + flexible. Faster than VLOOKUP on large data. Use when XLOOKUP unavailable (older Excel).
INDEX-MATCH Two-Way Lookup
10/20✨ What it does
ChatGPT writes a two-way INDEX-MATCH-MATCH that looks up both a row value and a column value in a grid. Put the row key and column header in two cells, then verify the intersection against a cell you already know.
Two-way lookup with INDEX-MATCH-MATCH. Output: =INDEX(range, MATCH(row_value, row_range, 0), MATCH(col_value, col_range, 0)). For tables where rows + columns both lookup. Powerful pattern.
Pro tip: INDEX-MATCH-MATCH = matrix lookup. Find value where row="X" and column="Y". Common need; elegant solution. Master once; reuse forever.
INDEX-MATCH vs VLOOKUP Performance
11/20✨ What it does
ChatGPT compares INDEX-MATCH and VLOOKUP speed on large sheets, and when the smaller calculation footprint actually matters. Time both on a copy of your 50K-row file, then keep INDEX-MATCH if recalc is already slow.
Performance: INDEX-MATCH vs VLOOKUP. Output: INDEX-MATCH faster on large data, smaller calc footprint, columns can change. When matters (large datasets).
Pro tip: On <10K rows: difference negligible. On 100K+ rows: INDEX-MATCH measurably faster. VLOOKUP recalculates whole row; INDEX-MATCH only what's needed.
INDEX-MATCH Multiple Criteria
12/20✨ What it does
ChatGPT writes a multi-criteria INDEX-MATCH array formula, and notes that FILTER is simpler in modern Excel. Use FILTER if you have it, then fall back to the array pattern only on older workbooks.
Multi-criteria INDEX-MATCH. Output: array formula =INDEX(return, MATCH(1, (criteria1=range1)*(criteria2=range2), 0)). Modern: FILTER simpler. Older Excel: this pattern.
Pro tip: INDEX-MATCH with array math = multi-criteria lookup. Older Excel: enter as array (Ctrl+Shift+Enter). Modern: regular entry. FILTER cleaner if available.
Like these prompts? There are full tutorials behind them.
Learn the workflows, not just the prompts. 300+ easy-to-follow tutorials inside AI Academy — and growing every week.
Common Use Cases
4 promptsLookup with Approximate Match
13/20✨ What it does
ChatGPT builds an approximate-match lookup that assigns a score to a grade or any other tier, with a sorted reference table and the edge cases. Sort your tier table ascending, then test a value that sits exactly on a breakpoint.
Approximate match lookup (e.g., score → grade). Output: VLOOKUP/XLOOKUP with approximate, sorted reference, edge cases. Tier assignments.
Pro tip: Approximate match for tier lookups: 90→A, 80→B, etc. Reference sorted ascending. =VLOOKUP(score, table, 2, TRUE) or XLOOKUP with match_mode=-1 (≤).
Concatenate for Multi-Criteria
14/20✨ What it does
ChatGPT builds a helper column that joins two keys with a pipe so a VLOOKUP can match on both, and names INDEX-MATCH or FILTER as cleaner options. Add the helper column, then switch to FILTER if your sheet already spills.
Multi-criteria via concatenated key. Output: helper column (=A2&"|"&B2), VLOOKUP on combined. Simple but ugly. Alternatives: INDEX-MATCH-MATCH or FILTER.
Pro tip: Helper column with concat = simple multi-criteria. Limitation: extra column. Modern: avoid via XLOOKUP / FILTER. Old Excel: this works.
Fuzzy Lookup
15/20✨ What it does
ChatGPT sets up a fuzzy or partial lookup with wildcards, Power Query Fuzzy Match, or add-in FUZZY functions when names have typos or extra words. Try wildcards on one of your messy columns first, then open Power Query if the names are still too loose.
Fuzzy / partial match lookup. Output: wildcards (* and ?), Power Query Fuzzy Match, FUZZY function (specific add-ins). When data has typos / variations.
Pro tip: Fuzzy lookup for messy data ("John Smith" vs "Smith, John" vs "John D Smith"). Wildcards basic. Power Query Fuzzy Match = serious. SaaS = clean source > fuzzy match.
Lookup Across Sheets
16/20✨ What it does
ChatGPT writes a lookup that reaches another sheet with Sheet2!A:B syntax, plus when to lock the range and how that pattern behaves on a big file. Point table_array at the other sheet, then freeze the range before you fill the formula down.
Lookup across sheets. Output: sheet reference syntax (Sheet2!A:B), absolute vs relative, performance. Multi-sheet pattern common.
Pro tip: Reference other sheet: SheetName!Range. With space in name: 'Sheet Name'!Range. Performance similar to within-sheet. Naming sheets without spaces = simpler formulas.
Patterns + Troubleshooting
4 promptsLookup Cheat Sheet
17/20✨ What it does
ChatGPT gives a lookup cheat sheet: VLOOKUP for a simple right lookup, XLOOKUP for a default and reverse search, INDEX-MATCH for left or two-way, FILTER for many results. Pick the function that matches your job this week, then stick to that one in the file.
Lookup cheat sheet. Output: VLOOKUP for: simple right-lookup. XLOOKUP for: modern, default value, reverse search. INDEX-MATCH for: left lookup, two-way, fast. FILTER for: multiple results.
Pro tip: Each lookup function has sweet spot. XLOOKUP = modern default. INDEX-MATCH = power-user. VLOOKUP = legacy. FILTER = multi-result. Pattern recognition.
#N/A Error Diagnostic
18/20✨ What it does
ChatGPT diagnoses #N/A in order: missing key, leading or trailing spaces, text versus number, a sorted-table need for approximate match, and a wrong VLOOKUP column index. Work the list top to bottom on one of your error cells, then copy the fix down only after that cell is right.
Diagnose #N/A error. Output: 1) value not in lookup table, 2) leading/trailing spaces, 3) text vs number mismatch, 4) sorted requirement (approximate match), 5) wrong column index (VLOOKUP). Systematic diagnosis.
Pro tip: Most #N/A = trailing whitespace. TRIM both sides. Second most = data type mismatch (number stored as text). Fix at source > working around in formula.
Mixed Data Types
19/20✨ What it does
ChatGPT finds lookups that fail because 123 stored as text does not match the number 123, with ISTEXT and ISNUMBER checks, VALUE or TEXT conversion, and data validation to prevent it. Convert one of your columns to a single type, then re-run the lookup.
Lookup failing due to data types. Examples: "123" (text) vs 123 (number). Output: detection (ISTEXT, ISNUMBER), conversion (VALUE, TEXT), prevention (data validation). Very common bug.
Pro tip: Numbers stored as text = lookup fails silently. Detect with ISTEXT. Convert with VALUE() or *1. Prevent: data validation at entry.
Performance on Large Data
20/20✨ What it does
ChatGPT speeds lookups on 100K-plus rows with INDEX-MATCH, a sorted source for approximate match, a helper key, or a move to Power Query or a database. Move your heavy join to Power Query if the sheet still crawls, then keep one lookup column as a check.
Slow lookup on 100K+ rows. Output: INDEX-MATCH > VLOOKUP, sorted source helps approximate match, helper column with key, alternative tools (Power Query, database). Performance matters at scale.
Pro tip: On large data, lookup performance compounds (100 lookups × 100K rows). INDEX-MATCH faster. Or: aggregate first via Power Query. Or: move to database for real scale.
Free tool
Prompt Optimizer
Turn a rough idea into a structured, professional AI prompt.
Frequently Asked Questions
Prompts are the starting line. Tutorials are the finish.
A growing library of 300+ hands-on tutorials on ChatGPT, Claude, Midjourney, and 50+ AI tools. New tutorials added every week.
7-day free trial. Cancel anytime.