Prompt Library

ChatGPT Prompts for VLOOKUP and Spreadsheet Lookups

20 copy-paste prompts

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.

VLOOKUP Basics

4 prompts

VLOOKUP from Description

1/20

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.

Writes VLOOKUP formulas.

💡

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

VLOOKUP returning #N/A. Causes: value not in table, leading/trailing spaces, text vs number mismatch, wrong column index. Output: troubleshooting checklist.

Troubleshoots #N/A errors.

💡

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

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).

Explains VLOOKUP limits.

💡

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

VLOOKUP with wildcards (* and ?). Output: enable approximate match? (no — wildcards work with exact), syntax, when useful (partial matches, fuzzy lookup). Underused.

Uses VLOOKUP wildcards.

💡

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.

Start 7-Day Free Trial

XLOOKUP (Modern)

4 prompts

XLOOKUP from VLOOKUP

5/20

Convert VLOOKUP to XLOOKUP. Output: side-by-side comparison, why XLOOKUP simpler (no col_index counting, exact match default, can return arrays). Modern lookup.

Converts to XLOOKUP.

💡

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

XLOOKUP with default if not found. Output: 4th parameter (if_not_found) replaces IFERROR wrap. Cleaner than VLOOKUP + IFERROR.

Uses XLOOKUP defaults.

💡

Pro tip: XLOOKUP built-in default = =XLOOKUP(value, lookup, return, "Not Found"). Replaces =IFERROR(VLOOKUP(...), "Not Found"). Cleaner formula.

XLOOKUP Reverse Search

7/20

XLOOKUP with reverse search (find last match). Output: 6th parameter search_mode = -1 for reverse. Use case: find most recent matching record.

Reverse-searches with XLOOKUP.

💡

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

XLOOKUP returning multiple columns. Output: return_array as multi-column range, automatic spill (modern Excel/Sheets), use cases. Powerful array feature.

Returns multi-columns with XLOOKUP.

💡

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 prompts

INDEX-MATCH from Description

9/20

INDEX-MATCH for [scenario]. Output: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)), explanation, advantages over VLOOKUP. Power-user lookup.

Writes INDEX-MATCH.

💡

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

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.

Two-way INDEX-MATCH lookups.

💡

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

Performance: INDEX-MATCH vs VLOOKUP. Output: INDEX-MATCH faster on large data, smaller calc footprint, columns can change. When matters (large datasets).

Compares lookup performance.

💡

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

Multi-criteria INDEX-MATCH. Output: array formula =INDEX(return, MATCH(1, (criteria1=range1)*(criteria2=range2), 0)). Modern: FILTER simpler. Older Excel: this pattern.

Multi-criteria INDEX-MATCH.

💡

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.

Try AI Academy Free

Common Use Cases

4 prompts

Lookup with Approximate Match

13/20

Approximate match lookup (e.g., score → grade). Output: VLOOKUP/XLOOKUP with approximate, sorted reference, edge cases. Tier assignments.

Builds tier lookups.

💡

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

Multi-criteria via concatenated key. Output: helper column (=A2&"|"&B2), VLOOKUP on combined. Simple but ugly. Alternatives: INDEX-MATCH-MATCH or FILTER.

Builds concatenated key lookups.

💡

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

Fuzzy / partial match lookup. Output: wildcards (* and ?), Power Query Fuzzy Match, FUZZY function (specific add-ins). When data has typos / variations.

Builds fuzzy lookups.

💡

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

Lookup across sheets. Output: sheet reference syntax (Sheet2!A:B), absolute vs relative, performance. Multi-sheet pattern common.

Looks up across sheets.

💡

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 prompts

Lookup Cheat Sheet

17/20

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.

Cheat sheet for lookups.

💡

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

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.

Diagnoses #N/A errors.

💡

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

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.

Handles data type mismatches.

💡

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

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.

Optimizes lookup performance.

💡

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.

Frequently Asked Questions

New work: XLOOKUP (simpler, more flexible, modern). Legacy or compatibility with older Excel: VLOOKUP. Most users still use VLOOKUP from habit; XLOOKUP is upgrade.

Common: forgot exact match (4th param FALSE), trailing whitespace, mixed data types, source range moved (relative reference broken). Systematic diagnosis solves 95%.

No. XLOOKUP requires Excel 365 or Excel 2021+. Older versions = INDEX-MATCH. Sheets has XLOOKUP. Match function to platform.

No, but XLOOKUP is the modern default. VLOOKUP not removed. New work = XLOOKUP. Legacy spreadsheets = VLOOKUP keeps working. Both supported indefinitely.

INDEX-MATCH faster than VLOOKUP. Power Query for transformation pipeline. Database for real scale. At >100K rows, lookup choice impacts performance noticeably.

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.

Start 7-Day Free Trial Explore AI for Data Analysts

7-day free trial. Cancel anytime.

Browse All Prompt Collections