Master the XMATCH Function in Excel 365

—

by

Himanshu
in

📘 What is the XMATCH Function in Excel?

The XMATCH function is a modern alternative to MATCH, introduced in Excel 365 and Excel 2021. It returns the relative position of an item in a row or column. Unlike MATCH, it supports reverse search, wildcard matching, and exact or approximate search modes.

🔧 Syntax of XMATCH

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
ParameterDescription
lookup_valueThe value you want to search for
lookup_arrayThe range or array to search in
match_mode(Optional) 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard match
search_mode(Optional) 1 = first-to-last (default), -1 = last-to-first, 2 = binary ascending, -2 = binary descending

✅ Key Features of XMATCH

  • Supports vertical and horizontal lookups
  • Can search from last to first (search_mode = -1)
  • Allows wildcard characters (match_mode = 2)
  • Works with arrays and spilled ranges
  • Better compatibility with dynamic arrays

🧪 Examples

🔹 Example 1: Basic Exact Match

=XMATCH("Priya", A2:A10)

Searches for “Priya” in the list and returns the position where it’s found.

✅ If “Priya” is in cell A5 (4th position in A2:A10), the result is 4.

🔹 Example 2: Wildcard Match

=XMATCH("P*", A2:A10, 2)

Returns the first item starting with “P”.

✅ Useful for partial string lookups.

🔹 Example 3: Reverse Search

=XMATCH("Complete", A2:A10, 0, -1)

Searches bottom-up for “Complete”.

🔹 Example 4: Approximate Match

If you have numbers like 50, 60, 70, and you’re looking for 65:

=XMATCH(65, A2:A10, 1)

Returns the position of the next larger number (70).

🔹 Example 5: Use with INDEX for Advanced Lookup

=INDEX(B2:B10, XMATCH("Ravi", A2:A10))

Finds Ravi in column A and returns corresponding value from column B.

✅ Powerful alternative to VLOOKUP or INDEX+MATCH.

🎓 Common Use Cases

  • Find row/column numbers dynamically
  • Combine with INDEX for 2D lookups
  • Reverse search to find last matching item
  • Match using wildcards like "*Report" or "Jan???"
  • Create dynamic dashboards or filters

❓ 5 Interview-Based Questions on XMATCH

  1. What is the key difference between XMATCH and MATCH in Excel? (Expected: XMATCH supports reverse search, wildcards, exact/approximate modes, and works with dynamic arrays.)
  2. How would you find the last occurrence of a value in a list using XMATCH? (Hint: Use search_mode = -1)
  3. What does the following formula return? =XMATCH(75, A2:A6, -1) (Expected: Returns the position of the largest number less than or equal to 75.)
  4. Can XMATCH be used with INDEX to replicate VLOOKUP? Provide an example. (Yes, e.g., =INDEX(B2:B10, XMATCH("ItemName", A2:A10)))
  5. Explain how to use XMATCH for partial matches using wildcards. (Set match_mode = 2, e.g., =XMATCH("Jan*", A2:A10, 2))

📌 Final Thoughts

XMATCH is more powerful and flexible than MATCH and a great fit for modern Excel tasks involving dynamic lookups. If you’re preparing for interviews or building advanced dashboards, mastering XMATCH can save time and simplify your logic.

🚀 Want to Master Excel 365 Lookups?

Enroll in my in-depth Excel training course covering:

  • XMATCH, XLOOKUP, INDEX-MATCH, FILTER, LET, LAMBDA
  • Dashboards, automation, case studies

👉 Mastering MS Excel – A Comprehensive Training Course

On sale products