Learn Looking Up Values | Calculating Data Like a Pro

Swipe to show menu

Lookup functions solve one of the most common problems in spreadsheet work: you have data in one place and need to pull related information from another place based on a matching value.

You give the function a value to search for, tell it where to look, and tell it what to return when it finds a match. Excel handles the searching automatically, for every row, every time the data changes.

=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found])

Exact Match

An exact match requires the lookup value to correspond perfectly to something in the reference table — the default in XLOOKUP.

Approximate Match

An approximate match finds the closest value that does not exceed the lookup value, used for tiered systems like tax brackets or discount thresholds.

Note

This behavior depends on the match_mode. The statement above is only accurate when match_mode = -1, where the function returns the next smaller value if an exact match is not found. For other match_mode settings, the result follows different matching rules.

In XLOOKUP, approximate match is controlled by the match_mode argument — the fourth optional argument after if_not_found. Setting it to -1 tells Excel: "if you can't find an exact match, return the next smaller value instead."

=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; -1)

0	-1	1	2
Exact match	Next smaller	Next larger	Wildcard
Default. Returns error if value not found exactly. Use for text categories.	If no exact match, returns result for the largest value ≤ lookup value. Use for tiered systems.	If no exact match, returns result for the smallest value ≥ lookup value.	Allows * and ? as wildcards in the lookup value for partial text matching.

Note

Approximate match requires the lookup array sorted ascending. An unsorted table produces wrong results silently.

VLOOKUP

VLOOKUP is still present in millions of spreadsheets and you will encounter it regularly. Its core constraint is structural: the lookup value must always sit in the first column of the table array, and it returns values by column index number. Insert a column between the lookup and return columns and that index number becomes wrong — VLOOKUP returns the wrong data without any warning. XLOOKUP references the return column directly, so it is immune to this problem.

=VLOOKUP(search_key; range; index; [is_sorted])

Note

VLOOKUP still works and you will encounter it in inherited files. You need to be able to read it. But for any formula you write yourself, use XLOOKUP — it is more robust, more readable, and handles approximate matching more explicitly.

Retrieve Monthly Budget by Category

Create a reference table below your Summary section with columns Category and Monthly Budget.

Enter the following values:
- Rent — 1,500.00
- Groceries — 400.00
- Utilities — 250.00
- Transport — 200.00
- Dining — 150.00
Format these values as currency.

In your Expenses table, retrieve the budget for each row using: =XLOOKUP(B9;I12:I20;J12:J20)

This returns the budget corresponding to each category.
Handle missing categories

Update the formula to avoid errors when a category is not found: =XLOOKUP(B9;I12:I20;J12:J20;"No budget defined")

This keeps your sheet readable and highlights missing mappings.
Lock the lookup table

Lock the lookup ranges using F4 so they do not shift when the formula is copied.
Apply approximate match for discount tiers

Create a new table with columns Spending thresholds and Discount values.

Enter the following values:
- 0 — 0
- 500 — 5
- 1000 — 10
- 2000 — 20
Format the discount values as percentages.

Then calculate the discount tier for each expense using: =XLOOKUP(D9;12:M16;N12:N16;"No tier";-1)

Everything was clear?

Thanks for your feedback!

Section 3. Chapter 7

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Section 3. Chapter 7