Excel Lookup Formulas Guide: XLOOKUP, INDEX MATCH, and Multi-Criteria Searches

Overview

This section gives you a simple mental model: what lookup formulas do, when each method is useful, and why structure matters as much as syntax.

A lookup formula answers a basic spreadsheet question: given this key, return the related value from somewhere else. The key might be a product code, customer ID, month, department, or a combination of several fields. The returned value might be a price, owner, status, revenue target, tax rate, or category label.

In business use, lookup formulas usually sit between raw data and decision-making outputs. A sales tracker may use lookups to assign territory and rep names. A KPI dashboard spreadsheet may use them to map metric names to targets or owners. A forecast spreadsheet template may use them to pull assumptions into a scenario model. If the lookup layer is weak, every downstream report becomes harder to trust.

For most Excel users, the three practical lookup patterns to know are:

• XLOOKUP for modern, readable one-key lookups and many approximate match tasks.
• INDEX MATCH for flexible lookups, especially when you need compatibility with older workbooks or more control over row and column logic.
• Multi-criteria lookups for cases where one field is not enough, such as finding a price by both product and region.

At a high level, choose methods like this:

• Use XLOOKUP when available and your task is a standard lookup with one lookup array and one return array.
• Use INDEX MATCH when you need a widely understood fallback pattern or want to separate position-finding from value-returning.
• Use multi-criteria logic when uniqueness depends on more than one column.

The formula itself is only part of the job. Reliable lookups also depend on clean keys, stable ranges, consistent data types, and tables that are designed with maintenance in mind.

Core framework

This section gives you a repeatable framework for building lookups that are easier to audit and less likely to fail when your spreadsheet changes.

1) Start with the lookup question

Before writing a formula, define three things:

• Lookup value: what are you searching for?
• Lookup source: where is the reference table?
• Return field: what value should come back?

For example: “Given a SKU in cell A2, return the standard cost from the Products table.” That clarity helps avoid the common mistake of writing formulas before the sheet structure is settled.

2) Use well-structured reference tables

The best lookup formulas usually sit on top of simple tables with one row per record and one field per column. Good lookup tables share a few traits:

• Clear headers
• No merged cells
• Consistent data types in key columns
• No duplicate keys unless duplicates are expected and handled intentionally
• Separate raw data from output sheets

If a key column mixes text and numbers that only look the same, lookups may fail. The value 1001 stored as a number is not always treated the same as "1001" stored as text.

3) Prefer exact matching unless you truly need approximate

Many spreadsheet errors come from approximate matches being used by accident. In business planning sheets, exact match is usually the safer default because IDs, SKUs, account codes, and metric names should map precisely.

Approximate matching is useful in narrower cases, such as assigning a commission rate based on a threshold table or mapping a score to a rating band. If you use approximate logic, document that decision near the table.

4) Build with XLOOKUP first when available

XLOOKUP is easier to read than older lookup patterns because it clearly separates the search range from the return range. A basic structure looks like this:

=XLOOKUP(A2, Products[SKU], Products[Cost], "Not found")

Read it in plain language: find the value in A2 inside the SKU column, return the matching value from the Cost column, and show “Not found” if there is no match.

Why XLOOKUP is often the best default:

• The return column can be to the left or right of the lookup column
• The formula is easier to read during reviews
• Built-in not-found handling avoids nested error wrappers in many cases
• It supports exact and approximate match modes

5) Keep INDEX MATCH in your toolkit

INDEX MATCH remains valuable because it teaches the underlying lookup logic: first find a position, then return the value at that position.

A standard pattern looks like this:

=INDEX(Products[Cost], MATCH(A2, Products[SKU], 0))

Here, MATCH finds the row position of the SKU in A2, and INDEX returns the corresponding value from the Cost column. The 0 in MATCH means exact match.

This approach is still useful when maintaining older models, explaining formula mechanics to teammates, or constructing more advanced retrieval logic.

6) Handle multi-criteria lookups deliberately

Sometimes one field is not enough. A pricing table may depend on both product and region. A staffing sheet may need employee plus date. A budget table may require department plus month plus scenario.

You can solve this in a few practical ways:

• Create a helper key by joining fields, then use a normal lookup
• Use a formula that matches multiple conditions directly
• Restructure the data if the table design is making lookups harder than necessary

A helper key is often the cleanest option for shared business spreadsheet templates because it is easy to audit. For example, a helper column might combine product and region:

=B2&"|"&C2

Then your lookup value can use the same pattern, and XLOOKUP or INDEX MATCH can search the helper key.

7) Protect formulas against change

Most lookup problems appear after the workbook changes. New columns get inserted. Raw data shifts. A manual copy-paste introduces spaces. To reduce future repair work:

• Use Excel Tables or clearly named ranges
• Avoid hard-coded row limits if the dataset will grow
• Label assumptions and lookup tables clearly
• Test with a few known records before trusting outputs
• Add a simple error flag column when accuracy matters

These habits matter in dashboards, financial model spreadsheet files, and recurring reporting packs where the same lookup logic supports many decisions.

Practical examples

This section turns the framework into real spreadsheet tasks you are likely to encounter in reporting, planning, and operations.

Example 1: Return a product category from a master list

Suppose your transactions sheet contains SKUs in column A, and you want to pull category names from a product master table.

XLOOKUP:

=XLOOKUP(A2, Products[SKU], Products[Category], "Missing SKU")

INDEX MATCH:

=INDEX(Products[Category], MATCH(A2, Products[SKU], 0))

This pattern is common in expense coding, SKU mapping, and dashboard preparation. It is especially useful before building summary views or charts. If you later turn the output into a dashboard template, stable categorization becomes essential for trustworthy totals.

Example 2: Pull a monthly target into a KPI sheet

Imagine a KPI dictionary or target table with columns for Metric, Month, and Target. You want the target for the metric in A2 and the month in B1.

A helper-key method is simple:

• Add a key column in the target table: =[@Metric]&"|"&[@Month]
• Use a lookup key in the report sheet: =A2&"|"&B$1

Then look up the target:

=XLOOKUP(A2&"|"&B$1, Targets[Key], Targets[Target], "No target")

This is a clean pattern for KPI dashboard spreadsheet work because it avoids hidden complexity and is easier for another user to review. If you maintain target definitions across reports, a separate reference sheet like a KPI dictionary can keep metric names and target logic standardized. Related reading: KPI Dictionary Spreadsheet: Define Metrics, Formulas, Owners, and Update Cadence.

Example 3: Find a price by product and region

In a quote model, the same product may have different prices by region. A single SKU lookup is not enough.

Create a helper key in the price table:

=[@Product]&"|"&[@Region]

Then use:

=XLOOKUP(E2&"|"&F2, PriceTable[Key], PriceTable[Price], "No match")

This approach is practical in small business spreadsheet templates because it is robust and visible. Anyone reviewing the file can inspect the key and understand why a result was returned.

Example 4: Approximate match for thresholds

Suppose you have a table of revenue thresholds and bonus rates. Here approximate matching can be appropriate, because the correct answer depends on the nearest lower bound.

=XLOOKUP(A2, Thresholds[Revenue], Thresholds[Rate], , -1)

The exact match mode and search options depend on your setup, so test carefully with boundary values. Approximate lookups are useful, but they deserve extra checking because a poorly sorted threshold table can return the wrong answer.

Example 5: Two-way lookup with INDEX MATCH

Sometimes you need to return a value at the intersection of a row and a column, such as finding a budget amount for a department and month from a matrix table.

A common pattern is:

=INDEX(B2:M10, MATCH(A15, A2:A10, 0), MATCH(B14, B1:M1, 0))

This means:

• Find the department row
• Find the month column
• Return the value at their intersection

Even if XLOOKUP is available, INDEX MATCH is still helpful for this kind of row-and-column retrieval logic. It also appears often in inherited planning models, budget forecast template files, and operations dashboard template workbooks.

Example 6: Clean up lookup keys before matching

If a lookup should work but returns errors, test whether extra spaces or inconsistent casing are involved. You may need to normalize data in a helper column with functions like TRIM or VALUE before applying the lookup. A repaired key column is often better than stacking more complexity into one long formula.

Once your lookup outputs are stable, you can combine them with formatting and reporting layers. For example, after mapping status codes or categories, conditional formatting can make the output easier to scan: Google Sheets Conditional Formatting Guide for Dashboards and Status Tracking. And if lookup-fed results are driving a report page, layout matters too: Excel Dashboard Design Best Practices for Readable KPI Reporting.

Common mistakes

This section helps you diagnose failures quickly and avoid the patterns that create fragile spreadsheets.

Using the wrong match type

If you need exact matching, make that explicit. Approximate logic can quietly return a nearby answer that looks plausible but is wrong.

Looking up non-unique keys

If the source table contains duplicate SKUs, names, or IDs, the formula may return the first match rather than the match you intended. When uniqueness depends on more than one field, use multi-criteria logic.

Mixing text and numbers

A numeric ID stored as text may not match the same visible ID stored as a number. Standardize key columns before building the report layer.

Ignoring spaces and hidden characters

Imported data often contains leading or trailing spaces. If a lookup fails unexpectedly, inspect the raw values and test cleaned helper columns.

Hard-coding ranges that do not expand

If your source data grows each month, a fixed range can exclude new rows. Excel Tables are usually safer for ongoing operations and reporting files.

Embedding too much logic in one formula

A long nested formula may feel efficient, but it is harder to audit. If a lookup depends on cleaned keys, helper fields, or scenario logic, split that work into visible steps.

Not testing with known cases

Before using a lookup in a planning model or dashboard, test a handful of records where you already know the correct answer. This small step catches many setup errors early.

Forgetting the broader model context

A lookup formula may be correct while the source table is outdated. If your sheet supports forecasting or decision analysis, the maintenance process around the lookup table matters as much as the formula. That is especially true in scenario models and weighted scoring sheets where a small mismatch can change conclusions. For related modeling contexts, see Scenario Planning Spreadsheet: Best Case, Base Case, and Worst Case Models and Weighted Scoring Model Spreadsheet for Vendor, Hire, and Project Decisions.

When to revisit

This section gives you a practical checklist for knowing when a lookup setup should be reviewed, updated, or rebuilt.

Lookup formulas are not a one-time task. Revisit them whenever the sheet structure, source data, or reporting purpose changes.

Review your lookup setup when:

• You add new columns or change table layouts
• You switch from one source export to another
• You begin tracking new scenarios, regions, products, or departments
• You notice duplicate keys appearing in reference tables
• You convert a simple report into a recurring dashboard
• You inherit a workbook built with older formulas and want easier maintenance

Use this quick refresh process:

1. List the key fields each lookup depends on.
2. Check that those fields are still unique where needed.
3. Confirm that data types are consistent across source and output sheets.
4. Replace brittle fixed ranges with tables or named ranges if the file is growing.
5. Test several known records, including one missing case and one edge case.
6. Add clear labels or notes if approximate matching is intentional.

If you are building a reusable lookup formulas spreadsheet or a broader set of business spreadsheet templates, it also helps to keep a small “formula reference” tab with the purpose of each key lookup, the source table name, and any assumptions. That makes handoffs easier and reduces future repair time.

The main goal is not to memorize every formula variation. It is to build lookup logic that stays readable and dependable as your workbook evolves. XLOOKUP is often the cleanest modern choice. INDEX MATCH remains a strong fallback and a useful thinking tool. Multi-criteria lookups become manageable when you structure keys deliberately. If you return to these three ideas whenever your data model changes, your spreadsheets will be easier to trust and easier to maintain.