Skip to content
FormulaCraft

XMATCH

XMATCH is a modern replacement for MATCH that returns the position of a lookup value within a row or column array. Unlike MATCH, it defaults to exact match, supports wildcards, and can search in reverse order. Use it to find where a value sits in a list before using that position with INDEX or another formula.

Excel
=XMATCH("Banana",B2:B5)
Google Sheets
=XMATCH("Banana",B2:B5)

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Runs server-side · free · no signup

How it works

  1. 1"Banana" is the lookup value — XMATCH will find where this appears in the array.
  2. 2B2:B5 is the lookup array — the range to search; XMATCH returns a 1-based position within it.
  3. 3No third argument means exact match mode (0) is used by default.

Need a version for your data?

Try: “Find what position 'Banana' is in my fruit list”

Related

VLOOKUPXLOOKUPINDEX / MATCH

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: