Home › Guides › How to Use the XMATCH Function
Lookups

How to Use the XMATCH Function

Last updated: 2026-09-06

⚡ Quick Answer

XMATCH searches for a specified item in an array and returns its relative position coordinates.

=XMATCH("Region B", A2:A100, 0)

📊 Visual Demo

Book1 - Excel
HOME · INSERT · FORMULAS · DATA
fx =XMATCH("Region B", A2:A100, 0)
ABCDE
11Target IDA2:A500 (Exact Match)=XMATCH("ID_45", A:A)
22Evaluation—ExactIndex Row #14
33NestingINDEX PairingCombinedRobust Lookup

🧠 Deep Dive

XMATCH searches for a specified item in an array and returns its relative position coordinates.

Key Insights

  • XLOOKUP searches any direction and defaults to exact match.
  • Boolean multiplication enables multi-criteria lookups.

Common Mistakes

  • Forgetting exact match argument in VLOOKUP.
  • Lookup column containing hidden spaces.

Pro Tips

  • Always wrap lookups in IFERROR.
  • Clean keys with TRIM.

FAQ

What does this formula do in plain English?

XMATCH searches for a specified item in an array and returns its relative position coordinates.

Why is my formula not working?

Forgetting exact match argument in VLOOKUP.

Does this work in Google Sheets?

Yes, this syntax is fully compatible with Google Sheets.

What is the most common mistake?

Lookup column containing hidden spaces.

Related Guides

How to Use XLOOKUP with Multiple CriteriaHow to VLOOKUP to the LeftHow to Use INDEX and MATCHExcel Error Hub

Need Complete Playbooks?

Download our verified manuals covering ERP Cleanup and Modeling.

Explore Playbook Series (From ₹199)