Lookup Functions — Let AI Tame VLOOKUP and Its Successors
Chapter 3: Lookup Functions Complete Guide
Lookup functions are the most-used category in Excel, hands down. Matching salary records to departments, pulling product prices into order tables, merging HR data across spreadsheets — none of this works without lookups. Most people know VLOOKUP but use it wrong, don't know XLOOKUP exists, and have never touched INDEX+MATCH. This chapter fixes all three, with full AI prompt workflows for every case.
Lookup Function Use Cases
The core idea: given a key (usually an ID or name), retrieve related information from another table.
Three canonical workplace scenarios:
- Payroll + department matching: Salary table has employee IDs; department info lives in an employee master table. VLOOKUP bridges them.
- Order table + product catalog: Orders have product codes but no prices. The product catalog has codes, names, and prices. Lookup pulls prices into the order table for revenue calculations.
- HR data consolidation: Attendance, performance, and salary are separate sheets, all keyed on employee ID. Lookups merge them into one analysis table.
VLOOKUP Complete Guide
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Parameters
lookup_value
The value to search for — usually a cell reference like A2. VLOOKUP searches for this in the first column of table_array.
table_array
The range containing your data. The first column must be the lookup column. Return columns are numbered from this range's left edge. Lock with $ when copying: $A$2:$D$100.
col_index_num
Which column to return — counted from the left edge of table_array. 1=first column (the lookup column), 2=second column, etc. Not the sheet column number.
range_lookup
0 or FALSE = exact match (use this 99% of the time). 1 or TRUE = approximate match (for range lookups like tiered pricing — requires lookup column to be sorted ascending).
Five Workplace Cases
Case 1 Look up department by employee ID Salary sheet (Sheet1) has employee IDs in column A. Employee master (Sheet2) has: A=ID, B=Name, C=Department. Goal: fill column D in Sheet1 with each employee's department.
How do I use VLOOKUP to get departments?
Case 2 Look up product price by product code Order sheet: A=product code, B=quantity, C=unit price (to fill), D=amount. Product catalog sheet: A=code, B=name, C=price.
Four Common VLOOKUP Errors + AI Fix Prompts
#N/A — Value not found
Possible causes: value genuinely missing; type mismatch (number vs. text number); hidden leading/trailing spaces; full-width vs. half-width character differences.
Diagnose: Use =EXACT(A2, Sheet2!A2) to check for exact character match. Use =LEN(A2) to check for unexpected spaces.
✅ AI Fix Prompt: My VLOOKUP returns #N/A. The lookup value is "E001" and I can see "E001" in the lookup column — they look identical but it won't match. Diagnose possible causes and give me steps to check each one.
#REF — Column index out of range
col_index_num is larger than the number of columns in table_array. For example, table_array is A:C (3 columns) but col_index_num is 4.
✅ Fix: Count the columns in your table_array. Make sure col_index_num does not exceed the total column count.
Wrong value returned (no error, but result is incorrect)
Cause 1: Miscounted the column index. Manually count columns in table_array to confirm which number is your target column.
Cause 2: range_lookup is missing or set to 1 (approximate match) when data is not sorted. Approximate match requires ascending sort — without it, results are unpredictable.
✅ AI Fix Prompt: My VLOOKUP returns wrong values with no error. Formula: =VLOOKUP(A2, Sheet2!A:D, 3, 0). I want to return the department (Sheet2 column C). Please review the formula for issues.
All rows return the same value when copied down
The lookup_value (first argument) is absolutely referenced: $A$2. Every row looks up A2 instead of moving to A3, A4...
✅ Fix: Use relative reference for lookup_value (A2, no $). Use absolute reference for table_array ($Sheet2.$A:$C). Correct: =VLOOKUP(A2, Sheet2!$A:$C, 3, 0)
XLOOKUP — The VLOOKUP Upgrade
Available in Excel 365 and Excel 2021. If you have either, prefer XLOOKUP over VLOOKUP.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Requires column number | ❌ Yes — must count columns; breaks if columns are inserted | ✅ No — directly specify return range |
| Reverse lookup (return column to the left) | ❌ Not supported natively | ✅ Supported — lookup and return arrays are independent |
| Default match mode | ⚠️ Approximate match — easy to forget the 0 | ✅ Exact match by default |
| Not-found handling | ❌ Returns #N/A — must wrap with IFERROR | ✅ 4th parameter sets the not-found value directly |
| Return multiple columns | ❌ Separate VLOOKUP per column | ✅ return_array can span multiple columns |
| Search from bottom up | ❌ Top-down only | ✅ search_mode=-1 searches from last row up |
INDEX + MATCH — The Universal Lookup Combination
Full structure:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
How it works:
MATCH(lookup_value, lookup_range, 0) → returns the ROW NUMBER where lookup_value is found
INDEX(return_range, row_number) → retrieves the value at that row number from return_range
Four Advanced Cases
INDEX+MATCH Case 1 Reverse Lookup (VLOOKUP cannot do this) Employee table: A=ID, B=Name, C=Department. Need to look up ID by name — the return column (A) is to the LEFT of the lookup column (B).
INDEX+MATCH Case 2 Two-Dimensional Lookup Price matrix: column A has product models (row headers), row 1 has regions (column headers), intersection cells are prices. Look up price by both product and region simultaneously.
INDEX+MATCH Case 3 Multi-Condition Lookup (Excel 2019 Compatible) Price history table: A=product name, B=effective date, C=price. Same product has multiple rows (one per price change). Match both product and date to get the specific price.
INDEX+MATCH Case 4 Find Name of Max/Min Value Sales table: A=salesperson name (A2:A20), B=sales amount (B2:B20). Find the name of the top and bottom performers.
MATCH Used Standalone
MATCH is usually paired with INDEX, but standalone it's useful when you need the position, not the value:
Check if a value exists:
=ISNUMBER(MATCH("John Smith", A:A, 0)) → returns TRUE/FALSE
Dynamic column number in VLOOKUP (survives column reordering):
=VLOOKUP(A2, ProductTable!$A:$Z, MATCH("Unit Price", ProductTable!$1:$1, 0), 0)
This always finds the "Unit Price" column by header name, not by position.
Lookup Function Quick Reference
| Scenario | Recommended | Version |
|---|---|---|
| Standard lookup (lookup column left of return column) | XLOOKUP or VLOOKUP | XLOOKUP: 365/2021 |
| Reverse lookup (return column to the LEFT) | XLOOKUP or INDEX+MATCH | INDEX+MATCH: all versions |
| Multi-condition lookup | XLOOKUP (& concat) or INDEX+MATCH array | INDEX+MATCH: all versions |
| 2D lookup (row + column) | INDEX + double MATCH | All versions |
| Return multiple columns at once | XLOOKUP | 365/2021 only |
| Find name of max/min value | INDEX+MATCH+MAX/MIN | All versions |
| Find last matching record | XLOOKUP (search_mode=-1) | 365/2021 only |
| Check if value exists | ISNUMBER(MATCH(...)) | All versions |
| Range/tiered lookup (approximate) | VLOOKUP (range_lookup=1) | All versions |
AI Prompt Templates (8 Ready-to-Use)
General lookup formula request
I need a lookup formula in Excel. Table structure: [describe columns].
Lookup key: [which cell/column has the lookup value]. Return value: [which column to return]. Excel version: [365/2021/2019]. Formula goes in [target cell], needs to copy down.
VLOOKUP error diagnosis
My VLOOKUP has an error: [paste formula]
Error type: [#N/A / #REF / wrong value returned] Table structure: [describe both tables] Lookup value example: [show what you're looking for and what's in the lookup column] Diagnose the cause and give me the fix.
Convert VLOOKUP to XLOOKUP
Rewrite this VLOOKUP as XLOOKUP (Excel 365):
[paste your VLOOKUP formula] Explain each parameter in the new version.
Multi-condition lookup (Excel 2019 compatible)
I need a lookup matching two conditions, compatible with Excel 2019:
Table: [describe columns A, B, C] Condition 1: [field] in [cell] Condition 2: [field] in [cell] Return: [field] column's value Give me INDEX+MATCH array formula.
2D lookup (row and column)
I need to look up a value at the intersection of a row and column:
Data layout: [row headers in col A, column headers in row 1, data in B2:X100] Row condition: [cell with row lookup value] Column condition: [cell with column lookup value] Give me INDEX + double MATCH formula.
Find name of max/min
Name column: [range, e.g. A2:A50]
Value column: [range, e.g. B2:B50] Give me INDEX+MATCH+MAX/MIN to find the name of the highest and lowest values. Also, how do I list ALL names if there are ties? (Excel 365)
Function selection advice
Tell me which lookup function to use for these scenarios:
Excel version: [365/2021/2019] Scenario: [describe: position of lookup vs return column, single or multiple conditions, single or multiple return columns] Just give me the recommendation and brief reason.
Cross-sheet or cross-workbook lookup
I need to look up data from another [sheet/workbook]:
Current location: [sheet name, cell with lookup value] Source: [sheet name or file name, lookup column and return column] Give me the correct cross-reference syntax and any pitfalls to watch for.
Previous ← Chapter 2: Excel Efficiency Fundamentals Next Chapter 4: Conditional Functions →