ExamplesFor You
Home / Excel / XMATCH
✍️ Excel lookup examples

XMATCH Examples in Excel

Learn XMATCH examples for modern position lookups, reverse searches and exact matches.

Updated 2026-06-125 practical examplesCopy-ready

What it does

XMATCH is a modern version of MATCH with clearer options for exact matching and search direction.

Syntax or pattern

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
✅

5 practical examples

1

Find exact position

Return the position of a product in a list.

=XMATCH(A2, Products[Product])

XMATCH defaults to exact match in many common cases.

2

Search from bottom to top

Find the last occurrence of a value.

=XMATCH(A2, Products[Product], 0, -1)

Search mode -1 scans from the end.

3

Find a column by header

Locate a month header in a table.

=XMATCH(H1, Sales[#Headers])

Useful for dynamic column selection.

4

Use XMATCH with INDEX

Return a price using XMATCH as the position finder.

=INDEX(Products[Price], XMATCH(A2, Products[Product]))

A modern alternative to INDEX MATCH.

5

Find next larger value

Match a value to the next larger threshold.

=XMATCH(B2, Thresholds, 1)

Helpful for bands, levels and tiers.

Common mistakes to avoid

  • Mixing up match_mode and search_mode.
  • Using approximate modes without understanding thresholds.
  • Assuming duplicates are handled automatically.

Related Excel examples

MATCH Examples in ExcelINDEX MATCH Examples in ExcelXLOOKUP Examples in Excel📊 All Excel examples

FAQ

Why use XMATCH?

It has clearer defaults and supports reverse searches more directly than MATCH.

💡 Useful resources

Here are some ideas for you

Optional resources that may help if you are learning formulas, building reports, or working in spreadsheets often.

  • 📘
    Excel formula books

    Practice formulas with structured examples you can keep beside your desk.

    See ideas
  • ⌨️
    Excel shortcut guides

    Build speed with keyboard shortcuts for selection, formatting and navigation.

    See ideas
  • 🔢
    Numeric keypads

    Helpful if you enter many numbers on a laptop or compact keyboard.

    See ideas
  • 🖥️
    External monitors

    Useful for viewing large worksheets, formulas and reference tables side by side.

    See ideas
  • 📒
    Desk notebooks

    Sketch formula logic, report ideas and table structures before building.

    See ideas
  • 💻
    Laptop stands

    Make long spreadsheet sessions more comfortable and ergonomic.

    See ideas

Some links in this section may be affiliate links. Choose only what is useful for your own work.