Lookup & Reference Mastery

The Evolution of Lookups

Lookup formulas are how you connect related data without duplicating it across sheets. The toolkit has evolved significantly — understanding all four approaches lets you choose the right one for each situation.

VLOOKUP (Legacy)

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Searches the first column of a range and returns a value from a specified column to the right. The workhorse of pre-2019 Excel.

Limitations you need to know:

• Can only look right — the lookup column must be the leftmost column of your range
• Breaks when you insert or delete columns (col_index_num is a hardcoded number)
• Defaults to approximate match if you omit the FALSE argument — a common source of wrong results
• Returns only the first match

Despite its limitations, VLOOKUP still lives in the majority of existing workbooks. You need to understand it to maintain legacy files.

INDEX/MATCH (The Workhorse)

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

The combination that replaced VLOOKUP for power users. INDEX returns a value from a position; MATCH finds the position.

Advantages over VLOOKUP:

• Looks in any direction — return column can be to the left of the lookup column
• Does not break when columns are inserted or deleted
• Slightly faster on large datasets (benchmarks vary)
• MATCH with 0 always means exact match — no ambiguity

XLOOKUP (Modern)

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Available in Microsoft 365 and Excel 2021+. Does everything INDEX/MATCH does in a single, readable function.

Key advantages:

• Built-in if_not_found parameter eliminates the need for IFERROR wrapping
• Can search in any direction (including bottom-to-top with search_mode -1)
• Supports wildcard matching (=XLOOKUP("*Smith*", names, emails))
• Returns multiple columns when return_array spans multiple columns
• Exact match is the default — no need to remember to add FALSE

FILTER (Dynamic Array)

=FILTER(array, include, [if_empty])

Returns ALL matching rows, not just the first. Results spill into multiple cells automatically. Game-changer for extracting subsets of data without helper columns.

=FILTER(SalesTable, SalesTable[Region]="West", "No data")

This returns every row where Region equals "West" — potentially hundreds of rows from one formula.

Practical Patterns

Two-Way Lookup

Look up a value at the intersection of a row and column:

=INDEX(RateTable, MATCH(ProductType, RowHeaders, 0), MATCH(Region, ColHeaders, 0))

Multiple Criteria Lookup

Match on two or more columns simultaneously:

=XLOOKUP(1, (A:A=criteria1)*(B:B=criteria2), C:C)

The array multiplication creates a 1 only where both conditions are true. XLOOKUP finds the first 1 and returns the corresponding value from column C.

Return Multiple Columns

=XLOOKUP(CustomerID, CustomerTable[ID], CHOOSECOLS(CustomerTable, 2, 4, 7))

CHOOSECOLS selects specific columns from the table to return — useful when you need Name, Email, and Status but not every field.

UNIQUE + SORT + FILTER Chain

Extract, deduplicate, and sort in one formula:

=SORT(UNIQUE(FILTER(StatusColumn, DepartmentColumn="Engineering")))

This replaces what used to require pivot tables or manual copy-paste operations.

Common Mistakes and Fixes

Forgetting exact match in VLOOKUP. VLOOKUP defaults to approximate match (TRUE) if you omit the fourth argument. This sorts-and-scans the lookup column and can return wrong values on unsorted data. Always use FALSE for exact match: =VLOOKUP(A2, Table, 3, FALSE).

Referencing entire columns in VLOOKUP. =VLOOKUP(A2, B:D, 2, FALSE) forces Excel to check a million rows. Use bounded ranges or table references: =VLOOKUP(A2, Products[#All], 2, FALSE).

Not handling #N/A. Every lookup formula should handle the case where no match is found. Use XLOOKUP's built-in if_not_found, or wrap older functions: =IFERROR(VLOOKUP(...), "Not Found").

Using VLOOKUP when data is to the left. If the value you want to return is to the LEFT of your lookup column, VLOOKUP cannot do it. Use INDEX/MATCH or XLOOKUP.

When to Use What

Connection to AI Automation

These lookup patterns are the same logic that drives data enrichment in automation workflows. The mental model of "find a match in this table and return related data" appears in n8n, Make, Zapier, and custom API integrations. Master lookups in Excel and you understand half of what automation platforms do.

See also: [Excel Formula Architecture](/tutorials/excel-mastery/formula-architecture) for the foundational concepts, and [Data Modeling & Structured Tables](/tutorials/excel-mastery/data-modeling-tables) for structuring data so these lookups stay reliable.