How to Use XMATCH Function in Excel (Multiple Examples)

Hello everyone! Today I show you how to use XMATCH function in Microsoft Excel step by step with simple example.

You ever want to know where a value is in your list? Not the value itself, but the position number? That is what XMATCH do! It tell you "your value is at position 3" or "position 7" in your list. Then you can use that position number with other formula like INDEX to get what you want.

1. What is XMATCH Function in Excel?

XMATCH function search for a value in a range or array and return the position number of where it found.

Think of XMATCH like looking at the class list. You search for "John" and XMATCH tell you "John is number 5 in the list." Now you know the position, you can use it to do many other thing!

XMATCH is upgrade version of old MATCH function — it have more feature and more flexible. Work even without sorting your data!

Why XMATCH is useful:

1. Find position of any value — Know where your value is in the list
2. Work great with INDEX — INDEX+XMATCH is powerful replacement for VLOOKUP
3. More flexible than MATCH — Can search from bottom, use wildcard, no need sort

📚 Related Tutorial: Want to use XMATCH with INDEX to look up value? Check out How to Use MATCH Function in Excel to understand the basic first!

2. Formula Syntax & Parameters

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

• lookup_value — Required. The value you want to find. Example: "John" or cell A2
• lookup_array — Required. The range or array where Excel will search. Example: A2:A100
• [match_mode] — Optional. How to match:
  • 0 = Exact match (default) — find exactly what you type
  • -1 = Exact or next smaller value
  • 1 = Exact or next larger value
  • 2 = Wildcard match — can use * and ?
• [search_mode] — Optional. Which direction to search:
  • 1 = Search from first to last (default)
  • -1 = Search from last to first (find last match)
  • 2 = Binary search ascending (data must be sorted)
  • -2 = Binary search descending (data must be sorted)

3. Practical Examples

Example 1: Find Position of a Product Name

You have product name list in A2:A10. You want know what position is "Laptop" in the list:

=XMATCH("Laptop", A2:A10)

👉 Result: If "Laptop" is in row 4 of your list, XMATCH return 3 (position 3 inside the range A2:A10).

💡 Tip: XMATCH return position inside your range, not the actual row number in Excel. If your range start at A2, position 1 = row 2, position 3 = row 4.

Example 2: Use XMATCH with INDEX to Look Up Data

You have Name in column A (A2:A10) and Score in column B (B2:B10). You want find score for "Mary" in cell D2:

=INDEX(B2:B10, XMATCH(D2, A2:A10))

👉 Result: XMATCH find position of "Mary" in column A, then INDEX use that position to get the score from column B. This is very popular combination — more powerful than VLOOKUP!

Example 3: Find Last Match (Search from Bottom)

You have order list in column A and some name appear more than once. You want find position of the last order from "John":

=XMATCH("John", A2:A20, 0, -1)

👉 Result: XMATCH search from bottom to top and return position of the last "John" in the list. Old MATCH cannot do this — XMATCH can!

💡 Tip: Use search_mode = -1 to find last occurrence. Very useful when you have duplicate name or value and want the most recent one.

Example 4: Wildcard Search — Find Partial Match

You want find position of any product that contain the word "Phone" in the name:

=XMATCH("*Phone*", A2:A20, 2)

👉 Result: XMATCH find first item that contain word "Phone" — like "iPhone", "Samsung Phone", or "Phone Case". The * mean "anything before or after".

4. XMATCH vs MATCH — What is Difference?

Simple rule: If you have Excel 365 or 2021, use XMATCH. It more powerful and easier. If you use old Excel, use MATCH.

💡 Quick Tips for Using XMATCH Function

1. Combine with INDEX: INDEX + XMATCH is the modern replacement for VLOOKUP and HLOOKUP. Much more flexible — can look left, right, up, down any direction.
2. Default is exact match: You no need type 0 for match_mode. XMATCH default is already exact match. Just type =XMATCH(value, range) and it work.
3. Find last duplicate with -1: If your data have duplicate and you want the last one, use search_mode = -1 to search from bottom. Very useful for recent transaction or latest record.
4. Wildcard for partial match: Use match_mode = 2 with * wildcard to find partial text match. Example: "*apple*" find any text that contain word apple.
5. Excel Version Requirement: XMATCH require Microsoft 365 or Excel 2021 or newer. For older Excel, use MATCH function instead.

📚 Related Resources & Tutorials

• How to Use MATCH Function in Excel
• How to Use LOOKUP Function in Excel
• XLOOKUP vs INDEX MATCH Function in Excel Explained
• TAKE vs FILTER Function in Excel Explained
• Top Microsoft Excel Tips and Tricks Every Spreadsheet User Should Know
• Free Download PDF - 50 Popular Excel Formulas Cheatsheet (Khmer Language)