Summary  
The chapter explains how to use lookup functions to retrieve values from a reference table by matching keys, covering exact and approximate match modes and handling missing entries.

General domain of usage  
Expense tracking and budgeting

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.

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

Note

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)
```

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.

### 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])
```

`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.

Learn Excel the fun way — step by step, you'll go from basic data handling to building charts and dashboards that actually make sense. You'll learn how to organize messy data, use formulas with confidence, and turn numbers into clear insights.


Messy spreadsheets turn into clean, structured data. Tables, sorting, filtering, and smart input rules help keep everything accurate and easy to work with.

Raw data becomes clear and readable. Formatting, alignment, and conditional highlights make important insights stand out instantly.

Numbers start working for you. Formulas, functions, and logical conditions handle calculations, automate tasks, and solve real problems.

Data turns into visuals that tell a story. Charts, sparklines, and design improvements make information easy to understand at a glance.

Big datasets become simple summaries. PivotTables, interactive controls, and dashboards bring everything together into clear, dynamic insights.

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.

Looking Up Values

Exact Match

Approximate Match

VLOOKUP