تابع XLOOKUP در اکسل — راهنمای جامع و کاربردی

تابع XLOOKUP یکی از قدرتمندترین و منعطف‌ترین توابع جستجو در نسخه‌های جدید اکسل است. این تابع جایگزینی کامل برای توابع قدیمی‌تر مثل VLOOKUP و HLOOKUP و حتی ترکیب INDEX/MATCH محسوب می‌شود. در این مقاله به صورت گام‌به‌گام سینتکس، پارامترها، مثال‌های واقعی، نکات پیشرفته و بهترین شیوه‌ها را بررسی می‌کنیم.

سینتکس تابع XLOOKUP

شکل کلی تابع به این صورت است:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

پارامترها:

• lookup_value: مقدار مورد جستجو.
• lookup_array: برداری که در آن جستجو انجام می‌شود (ستون یا ردیف).
• return_array: برداری که مقادیر بازگشتی از آن گرفته می‌شود.
• if_not_found (اختیاری): مقدار جایگزین در صورت عدم یافتن (به‌جای #N/A).
• match_mode (اختیاری): نوع تطابق — 0 = مطابقت دقیق (پیش‌فرض)، -1 = دقیق یا کوچک‌ترین کمتر، 1 = دقیق یا کوچک‌ترین بزرگ‌تر، 2 = تطابق با الگو (wildcard).
• search_mode (اختیاری): جهت جستجو — 1 = اول تا آخر (پیش‌فرض)، -1 = از آخر به اول، 2 و -2 برای جستجوی باینری روی داده‌های مرتب.

مثال پایه — جستجوی دقیق

=XLOOKUP("محصولA", A2:A100, C2:C100, "پیدا نشد")

این فرمول مقدار “محصولA” را در ستون A جستجو می‌کند و متناظر آن مقدار را از ستون C برمی‌گرداند. اگر پیدا نشود، “پیدا نشد” نمایش داده می‌شود.

بازگرداندن چند ستون (Spill)

=XLOOKUP("محصولA", A2:A100, B2:D100, "پیدا نشد")

در این مثال XLOOKUP تمام ستون‌های B تا D مرتبط با ردیف پیدا شده را به صورت یک آرایه بازمی‌گرداند و در سلولی که فرمول نوشته شده پخش می‌شود (spill). این امکان جایگزین توابع پیچیده‌تر و چندین VLOOKUP می‌شود.

جستجو از پایین به بالا (Reverse Lookup)

=XLOOKUP(1001, A2:A100, C2:C100, "پیدا نشد", 0, -1)

پارامتر آخر (-1) باعث می‌شود جستجو از آخرین سطر به سمت بالا انجام شود. این حالت وقتی مفید است که آخرین رخداد یک کلید را بخواهیم (مثلاً آخرین وضعیت سفارش).

استفاده از الگوها (Wildcard)

=XLOOKUP("کتاب*", A2:A100, B2:B100, "پیدا نشد", 2)

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

چند شرطی بودن جستجو

=XLOOKUP(1, (A2:A100=G1)*(B2:B100=H1), C2:C100, "پیدا نشد")

در این روش، با ضرب دو آرایه بولی (True/False) یک آرایه منطقی ساخته می‌شود که فقط زمانی مقدار 1 (True) دارد که هر دو شرط برقرار باشد. سپس XLOOKUP به دنبال مقدار 1 می‌گردد و مقدار متناظر از ستون C را برمی‌گرداند. این تکنیک بسیار کاربردی برای جستجوی چندمعیاره است.

مقایسه با VLOOKUP و INDEX/MATCH

• XLOOKUP می‌تواند مقادیر سمت چپ را هم بازگرداند؛ مشکل بزرگ VLOOKUP را حل کرده است.
• XLOOKUP برای بازگرداندن چند ستون مناسب‌تر و ساده‌تر است؛ نیازی به توابع کمکی نیست.
• عملکرد و خوانایی فرمول‌ها بهتر شده و خطاهای ناشی از جابجایی ستون کمتر می‌شود.

نمونه جدول برای تمرین

مدیریت خطا و پیام سفارشی

پارامتر if_not_found به شما اجازه می‌دهد پیام معناداری به کاربر نمایش دهید، مثلاً “اطلاعات موجود نیست” به‌جای #N/A که برای گزارش‌گیری مناسب‌تر است.

نکات پیشرفته و بهینه‌سازی

• برای حجم‌های بزرگ داده، اگر می‌توانید آرایه را مرتب کنید از search_mode = 2 یا -2 (جستجوی باینری) استفاده کنید تا سرعت افزایش یابد.
• از match_mode = 0 برای مطابقت دقیق استفاده کنید مگر اینکه نیاز به تقریب یا wildcard داشته باشید.
• در فایل‌هایی که باید در نسخه‌های قدیمی‌تر اکسل باز شوند، XLOOKUP در همه نسخه‌ها در دسترس نیست؛ برای سازگاری از INDEX/MATCH یا استفاده از Microsoft 365 استفاده کنید.
• برای جلوگیری از محاسبات زائد، بردارهای lookup را تا حد امکان کوچک و محدوده‌ها را به رنج‌های مشخص محدود کنید.

نمونه‌های خطا و رفع آن‌ها

• خطای #N/A: معمولاً به دلیل پیدا نشدن مقدار است؛ از if_not_found استفاده کنید یا ورودی را بررسی کنید.
• خطای #VALUE!: ممکن است به دلیل ناهمخوانی ابعاد lookup_array و return_array باشد؛ مطمئن شوید طول هر دو برابر است.

پشتیبانی نسخه‌ها و جایگزین‌ها

XLOOKUP در Microsoft 365 و نسخه‌های جدیدتر اکسل (مانند Excel 2021) موجود است. در اکسل‌های قدیمی‌تر (مثلاً Excel 2016 غیر subscription) ممکن است در دسترس نباشد؛ در این حالت از ترکیب INDEX و MATCH یا VLOOKUP استفاده کنید.

جمع‌بندی سریع

تابع XLOOKUP ابزاری منعطف، قدرتمند و ساده برای انجام جستجوهای دقیق، تقریبی، چندستونه و چندمعیاره در اکسل است. با تسلط به پارامترهای match_mode و search_mode می‌توانید سناریوهای متفاوتی را حل کنید و فرمول‌های شما هم سریع‌تر و خواناتر می‌شوند.

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

ارسال