Why Lookup Functions?
Lookup functions find a value in one table and retrieve related data from another column — the Excel equivalent of a database join.
Example: You have an employee ID in your payroll sheet and need to pull the employee's name and department from the HR master list. Instead of copying and pasting manually, VLOOKUP does it instantly for thousands of rows.
Sample Tables
Table 1 — Payroll (columns A–C)
A B C
Emp ID Month Hours
EMP-001 Jan-2026 168
EMP-002 Jan-2026 160
EMP-003 Jan-2026 176
Table 2 — Employee Master (columns E–H)
E F G H
Emp ID Name Department Salary
EMP-001 Priya Sharma Finance 75000
EMP-002 Raj Patel Technology 92000
EMP-003 Meera Singh Marketing 68000
EMP-004 Arjun Nair Finance 81000
VLOOKUP — Vertical Lookup
The most widely used lookup function.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argument | Meaning |
|---|---|
lookup_value | The value to find (e.g., the employee ID) |
table_array | The range to search in (must include lookup column as first column) |
col_index_num | Which column of the table to return (1 = first, 2 = second, etc.) |
range_lookup | FALSE = exact match (always use FALSE!), TRUE = approximate match |
Basic VLOOKUP
=VLOOKUP(A2, $E$2:$H$5, 2, FALSE)
→ Looks for value in A2 within column E of the table, returns column 2 (Name)
=VLOOKUP(A2, $E$2:$H$5, 3, FALSE) → returns Department (column 3)
=VLOOKUP(A2, $E$2:$H$5, 4, FALSE) → returns Salary (column 4)
VLOOKUP with IFERROR
Always wrap VLOOKUP in IFERROR to handle missing values:
=IFERROR(VLOOKUP(A2, $E$2:$H$5, 2, FALSE), "Not Found")
VLOOKUP Limitations
- Looks right only — the lookup column must be the leftmost column of the table. You cannot look up based on column E and return a value from column C (to its left).
- Fragile to inserted columns —
col_index_num = 3breaks if you insert a column in the table (it would now return column 4's data). - Slow on large ranges —
$E:$His slower than$E$2:$H$1000. - One value only — returns the first match.
HLOOKUP — Horizontal Lookup
Like VLOOKUP but for data arranged horizontally (headers in a row, data below):
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
-- Find Q2 revenue for product "Laptop"
=HLOOKUP("Laptop", $B$1:$E$5, 3, FALSE)
HLOOKUP is rarely needed — most data is vertical. If you have horizontal data, consider transposing it.
INDEX and MATCH — The Professional Alternative
INDEX + MATCH is more powerful and flexible than VLOOKUP. It's what experienced Excel users prefer.
INDEX — Return a Value by Position
=INDEX(array, row_num, [col_num])
=INDEX($F$2:$F$5, 2) → "Raj Patel" (2nd value in the Name range)
=INDEX($E$2:$H$5, 2, 3) → "Technology" (row 2, column 3 of the full table)
MATCH — Find a Position
=MATCH(lookup_value, lookup_array, [match_type])
match_type: 0 = exact, 1 = less than (sorted ASC), -1 = greater than (sorted DESC)
=MATCH("EMP-002", $E$2:$E$5, 0) → 2 (EMP-002 is in the 2nd row of the range)
=MATCH("Finance", $G$2:$G$5, 0) → 1 (Finance is in the 1st row)
Combining INDEX + MATCH
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
=INDEX($F$2:$F$5, MATCH(A2, $E$2:$E$5, 0))
→ MATCH finds where EMP-001 is in column E (row 1)
→ INDEX returns the Name from row 1 of column F
→ Result: "Priya Sharma"
Advantages Over VLOOKUP
-
Looks left — lookup column doesn't need to be first:
=INDEX($E$2:$E$5, MATCH(A2, $F$2:$F$5, 0)) → Lookup by Name (column F), return ID (column E) — VLOOKUP can't do this -
Column-insertion proof — you reference actual ranges, not column numbers:
=INDEX($H$2:$H$5, MATCH(A2, $E$2:$E$5, 0)) → Salary range is hardcoded — inserting columns doesn't break it -
Works with large datasets — MATCH uses binary search on sorted data (match_type 1 or -1) which is much faster than VLOOKUP's full column scan.
Two-Way Lookup with INDEX + MATCH + MATCH
Look up both a row and a column:
=INDEX(data_range, MATCH(row_value, row_headers, 0), MATCH(col_value, col_headers, 0))
-- Quarterly sales table: find Q3 revenue for "Laptop"
=INDEX($C$2:$F$5, MATCH("Laptop", $B$2:$B$5, 0), MATCH("Q3", $C$1:$F$1, 0))
XLOOKUP — The Modern Replacement (Excel 365/2021)
XLOOKUP replaces both VLOOKUP and HLOOKUP. It's cleaner, more flexible, and fixes all VLOOKUP limitations.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | Meaning |
|---|---|
lookup_value | Value to find |
lookup_array | Where to look (just the lookup column) |
return_array | What to return (just the return column) |
if_not_found | Value if not found (replaces IFERROR wrapping) |
match_mode | 0=exact (default), -1=next smaller, 1=next larger, 2=wildcard |
search_mode | 1=first to last (default), -1=last to first, 2=binary ASC, -2=binary DESC |
Basic XLOOKUP
=XLOOKUP(A2, $E$2:$E$5, $F$2:$F$5, "Not Found")
→ Find A2 in E column, return matching F value, "Not Found" if missing
=XLOOKUP(A2, $E$2:$E$5, $F$2:$H$5, "Not Found")
→ Return ALL THREE columns (F, G, H) at once! — VLOOKUP can only return one
XLOOKUP Returning Multiple Columns
-- Place in one cell — results spill across multiple columns automatically
=XLOOKUP(A2, $E$2:$E$5, $F$2:$H$5)
→ Returns: Name | Department | Salary (three columns at once)
XLOOKUP Looking Left
=XLOOKUP("Priya Sharma", $F$2:$F$5, $E$2:$E$5)
→ Lookup by Name, return Emp ID — impossible with VLOOKUP
XLOOKUP Last Match
=XLOOKUP(A2, $E$2:$E$5, $F$2:$F$5, , 0, -1)
→ Searches last to first — returns the most recent match
LOOKUP Function (Legacy)
Older function, less commonly used today:
=LOOKUP(lookup_value, lookup_vector, result_vector)
→ Always approximate match; lookup_vector must be sorted ascending
Practical Examples
Example 1: Payroll Sheet — Pull Employee Details
Payroll sheet (A=EmpID, B=Month, C=Hours):
D2: =XLOOKUP(A2, MasterSheet!$A$2:$A$100, MasterSheet!$B$2:$B$100, "Unknown")
→ Employee Name from master
E2: =XLOOKUP(A2, MasterSheet!$A$2:$A$100, MasterSheet!$C$2:$C$100, 0)
→ Department
F2: =XLOOKUP(A2, MasterSheet!$A$2:$A$100, MasterSheet!$D$2:$D$100, 0)
→ Hourly rate
G2: =C2*F2 → Total pay = hours × rate
Example 2: Dynamic Report — Two-Way Price Lookup
Product prices change by region:
North South East West
Phone 15000 14500 15200 14800
Tablet 22000 21500 22500 21000
=INDEX($B$2:$E$3, MATCH(A6, $A$2:$A$3, 0), MATCH(B6, $B$1:$E$1, 0))
→ Lookup price for product A6 in region B6
Example 3: Grade Assignment with XLOOKUP Approximate Match
Score cutoffs: 90→A, 80→B, 70→C, 60→D, 0→F
Scores in D column, cutoffs in F:G columns
=XLOOKUP(D2, $F$2:$F$6, $G$2:$G$6, "Invalid", -1)
→ match_mode -1: finds next smaller value
→ Score 85 → matches 80 → returns "B"
Example 4: Last Transaction Date per Customer
-- Find the most recent order date for each customer
=XLOOKUP(A2, orders!$B$2:$B$1000, orders!$C$2:$C$1000, "No orders", 0, -1)
→ Searches backward (last to first) — returns most recent match
Choosing the Right Lookup Function
| Situation | Best Function |
|---|---|
| Excel 365/2021 | XLOOKUP — use for everything |
| Older Excel, simple lookup | VLOOKUP |
| Need to look left, or multiple criteria | INDEX + MATCH |
| Horizontal data | HLOOKUP or transpose the data |
| Approximate match (tax brackets, grades) | XLOOKUP with match_mode -1, or VLOOKUP with TRUE |
Common Mistakes
1. VLOOKUP range_lookup not set to FALSE
=VLOOKUP(A2, $E$2:$H$5, 2) → defaults to TRUE (approximate match!)
=VLOOKUP(A2, $E$2:$H$5, 2, FALSE) → always use FALSE for exact match
Approximate match requires sorted data and often returns wrong results on unsorted lists.
2. Not locking the table range
=VLOOKUP(A2, E2:H5, 2, FALSE) → when copied down, table shifts (E3:H6, etc.)
=VLOOKUP(A2, $E$2:$H$5, 2, FALSE) → locked — table stays fixed when copying
3. Duplicate values in lookup column
VLOOKUP and XLOOKUP both return the first match. If your lookup key isn't unique, you'll get unexpected results. Clean duplicates first.
4. Column number out of range (VLOOKUP)
=VLOOKUP(A2, $E$2:$H$5, 5, FALSE) → #REF! — table only has 4 columns
5. Data type mismatch
Lookup value: 1001 (number)
Lookup array: "1001" (text)
→ No match! Ensure both are the same type.
Fix: =VLOOKUP(TEXT(A2,"0"), $E$2:$H$5, 2, FALSE)
Practice Exercises
- Use VLOOKUP to pull department and salary for each employee ID in a payroll table, looking up from a master list.
- Rewrite the same lookup using INDEX + MATCH.
- Use XLOOKUP to retrieve multiple columns (name, department, salary) in a single formula.
- Create a grade lookup where scores map to letter grades using approximate match (XLOOKUP with match_mode -1).
- Find the most recent transaction amount for each customer using XLOOKUP with search_mode -1 (last to first).
Summary
In this chapter you learned:
VLOOKUP(value, table, col_num, FALSE)— vertical lookup; table's leftmost column must be the lookup key; always use FALSE for exact matchHLOOKUP— horizontal version of VLOOKUPINDEX(range, row, col)— return a value by positionMATCH(value, range, 0)— find the position of a valueINDEX + MATCH— more flexible than VLOOKUP; can look left; resistant to column changesXLOOKUP(value, lookup_col, return_col, fallback, match_mode, search_mode)— modern replacement; handles left-lookup, multi-column returns, last match, and approximate match- Always lock table ranges with
$when copying formulas IFERRORwrapper for VLOOKUP;if_not_foundargument in XLOOKUP- Data type must match between lookup value and lookup array
Next up: Date & Time Functions — work with dates, calculate durations, and schedule events.