تابع XMATCH در اکسل — معرفی، پارامترها و کاربردها

تابع XMATCH یکی از توابع جدید در Excel (Office 365 / Excel 2021 به بعد) است که برای پیدا کردن «موقعیت» یک مقدار در یک آرایه یا محدوده به کار می‌رود. XMATCH جانشین قدرتمندتر و منعطف‌تری برای تابع قدیمی MATCH است و امکاناتی مثل جستجوی معکوس، جستجوی دودویی (برای داده‌های مرتب) و پشتیبانی از حروفِ جایگزین (wildcards) را ارائه می‌دهد.

سینتکس و پارامترها

سینتکس پایه XMATCH به صورت زیر است:

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

توضیح پارامترها:

• lookup_value: مقدار قابل جستجو (عدد، متن یا مرجع).
• lookup_array: محدوده‌ای که XMATCH در آن جستجو می‌کند.
• match_mode (اختیاری): نوع تطبیق — پیش‌فرض 0 (تطبیق دقیق). مقادیر ممکن:
  • 0 — تطبیق دقیق (Exact)
  • -1 — تطابقِ دقیق یا نزدیک‌ترین مقدار کوچک‌تر (Exact or next smaller)
  • 1 — تطابقِ دقیق یا نزدیک‌ترین مقدار بزرگ‌تر (Exact or next larger)
  • 2 — تطبیق با استفاده از wildcardها (مثل * یا ?)
• search_mode (اختیاری): جهت/روش جستجو — پیش‌فرض 1. مقادیر ممکن:
  • 1 — جستجو از ابتدا تا انتها (First-to-last)
  • -1 — جستجو از انتها به ابتدا (Last-to-first)
  • 2 — جستجوی دودویی برای داده مرتب صعودی (Binary search ascending)
  • -2 — جستجوی دودویی برای داده مرتب نزولی (Binary search descending)

مقایسه کوتاه با MATCH و XLOOKUP

XMATCH مشابه MATCH موقعیت (index) را برمی‌گرداند، در حالی که XLOOKUP مقدار خودِ سلول را برمی‌گرداند. XMATCH امکاناتی که در XLOOKUP وجود دارد (match_mode و search_mode) را به شکل ساده برای یافتن موقعیت فراهم می‌کند. برای گرفتن مقدار قابل استفاده در INDEX می‌توان XMATCH را با INDEX ترکیب کرد.

مثال‌های عملی

1) پیدا کردن موقعیت یک مقدار دقیق

=XMATCH("Apple", A2:A10, 0, 1)

این فرمول جایگاه اولین سلولی که دقیقاً برابر “Apple” است در محدوده A2:A10 را برمی‌گرداند (جستجو از ابتدا به انتها).

2) گرفتن مقدار مرتبط با INDEX + XMATCH

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

در این حالت XMATCH شماره سطر نسبت به محدوده A2:A10 را برمی‌گرداند و INDEX مقدار متناظر را از ستون B بازیابی می‌کند. این الگو جایگزین مرسوم VLOOKUP می‌شود و از مزایای انعطاف‌پذیری و عملکرد بهتر برخوردار است.

3) یافتن آخرین وقوع یک مقدار

=XMATCH("Apple", A2:A100, 0, -1)

با تعیین search_mode برابر -1، XMATCH از انتهای محدوده جستجو می‌کند و در نتیجه موقعیت آخرین سلول دارای “Apple” را برمی‌گرداند. بسیار مفید برای گزارش‌گیری‌هایی که نیاز به مقدار یا تاریخ آخرین رخداد دارند.

4) جستجوی تقریبی

=XMATCH(52, C2:C100, -1)

این فرمول در یک ستون مرتب‌شده صعودی، بزرگ‌ترین مقدار ≤ 52 را پیدا کرده و موقعیت آن را برمی‌گرداند. اگر مجموعه مرتب نیست، نتایج معناداری نخواهید داشت؛ در این حالت باید از گزینه‌های جستجوی دودویی فقط برای داده مرتب استفاده شود.

5) جستجوی با wildcard

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

با match_mode برابر 2 می‌توانید از علامت‌های wildcard مثل “*” و “?” استفاده کنید. مثال بالا اولین سلولی که با “App” شروع می‌شود را می‌یابد.

6) استفاده عملی با مدیریت خطا

=IFERROR(INDEX(B2:B10, XMATCH("Apple", A2:A10, 0)), "Not Found")

ترکیب IFERROR باعث می‌شود در صورت عدم یافتن مقدار، پیغام قابل فهمی نمایش یابد به جای خطای #N/A.

نکات و بهترین روش‌ها (Expert Tips)

• برای یافتن آخرین مقدار از search_mode = -1 استفاده کنید — بسیار سریع‌تر و ساده‌تر از ترکیب‌های پیچیده‌ای که قبلاً لازم بود.
• اگر داده مرتب دارید و به عملکرد بالا نیاز دارید، از جستجوی دودویی (search_mode = 2 یا -2) استفاده کنید؛ اما دقت داشته باشید که ترتیب داده باید دقیقاً صعودی یا نزولی باشد.
• برای جستجوی متن با الگوها از match_mode = 2 بهره ببرید تا wildcardها فعال شوند.
• XMATCH مقدار موقعیت را برمی‌گرداند؛ برای دسترسی به مقدار واقعی معمولاً آن را با INDEX ترکیب کنید.
• در نسخه‌های قدیمی Excel (قبل از Office 365 / 2021) که XMATCH دردسترس نیست، می‌توانید از ترکیب MATCH و INDEX یا XLOOKUP (در نسخه‌های جدیدتر) استفاده کنید. اما XMATCH خواناتر و انعطاف‌پذیرتر است.

جدول خلاصه پارامترها

موارد استفاده واقعی (Use Cases)

• گزارش‌گیری از آخرین تاریخ عملیات یک مشتری (آخرین وقوع یک شناسه)
• جایگزین کردن VLOOKUP برای بازیابی مقدار با ترکیب INDEX و XMATCH
• ایجاد جستجوی پیشرفته با پشتیبانی wildcard برای فهرست محصولات یا مشتریان
• سرعت بخشیدن به محاسبات روی داده‌های بزرگ با استفاده از جستجوی دودویی در ستون‌های مرتب

جمع‌بندی

تابع XMATCH ابزار قدرتمندی برای یافتن موقعیت مقادیر در اکسل است که ترکیبی از راحتی، سرعت و انعطاف‌پذیری را ارائه می‌دهد. با درک پارامترهای match_mode و search_mode و ترکیب مناسب با INDEX و IFERROR می‌توانید بسیاری از نیازهای جستجوی خود را با کدهای کوتاه، خوانا و سریع برطرف کنید.

لطفاً از کمبود ها و مشکلات این محتوا برای ما بنویسید

ارسال