فصل ۴: جست‌وجو و آرایه‌های پویا — از VLOOKUP تا LAMBDA

XLOOKUP کامل: match_mode، search_mode، چند ستون و جست‌وجوی دوطرفه

یک تابع به‌جای VLOOKUP، HLOOKUP و INDEX/MATCH

XLOOKUP در Excel 2021 و 365 موجود است و همه‌ی دام‌های VLOOKUP را حذف می‌کند: پیش‌فرضش تطبیق دقیق است، ستون جست‌وجو و نتیجه جدا هستند، به چپ و بالا هم جست‌وجو می‌کند و پیام «پیدا نشد» را خودش مدیریت می‌کند.

=XLOOKUP(مقدار, آرایه_جست‌وجو, آرایه_نتیجه, [اگر_پیدا_نشد], [match_mode], [search_mode])
آرگومانمقدارمعنا
match_mode0 (پیش‌فرض)دقیق
match_mode-1دقیق، وگرنه بزرگ‌ترین مقدار کوچک‌تر (جدول پلکانی)
match_mode1دقیق، وگرنه کوچک‌ترین مقدار بزرگ‌تر
match_mode2wildcard با * و ?
search_mode1 (پیش‌فرض)از اول به آخر
search_mode-1از آخر به اول (آخرین رخداد)
search_mode2 / -2جست‌وجوی دودویی روی داده‌ی مرتب صعودی / نزولی

الگوهای پرکاربرد

پایه، با پیام:
=XLOOKUP(G2, Products[Code], Products[Price], "کد ناموجود")

جست‌وجو به چپ (کد از روی نام):
=XLOOKUP(G2, Products[Name], Products[Code])

چند ستون یکجا (نام، واحد، قیمت؛ نتیجه spill می‌شود):
=XLOOKUP(G2, Products[Code], Products[[Name]:[Price]])

پورسانت پلکانی بدون مرتب‌سازی و بدون IF:
=C2 * XLOOKUP(C2, {0,2E9,5E9}, {0.01,0.02,0.03}, , -1)

آخرین قیمت فروش یک طرح (آخرین رخداد):
=XLOOKUP(G2, Sales[Design], Sales[UnitPrice], , 0, -1)

دو شرط (طرح و شانه):
=XLOOKUP(1, (Sales[Design]=G2)*(Sales[Reed]=H2), Sales[UnitPrice], "ندارد")

جست‌وجوی دوطرفه (نماینده در سطر، ماه در ستون):
=XLOOKUP(G2, A2:A50, XLOOKUP(H2, B1:M1, B2:M50))

جمع فروش از ماه H2 تا ماه I2 (XLOOKUP مرجع برمی‌گرداند):
=SUM(XLOOKUP(H2, B1:M1, B2:B50):XLOOKUP(I2, B1:M1, M2:M50))

XMATCH

XMATCH نسخه‌ی مدرن MATCH است با همان match_mode و search_mode، و پیش‌فرض دقیق. =INDEX(B2:M50, XMATCH(G2, A2:A50), XMATCH(H2, B1:M1)) برای کسانی که به INDEX عادت دارند.

نکته‌هایی که کمتر کسی می‌داند

  • XLOOKUP یک «مرجع» برمی‌گرداند نه فقط مقدار؛ به همین دلیل می‌توان دو XLOOKUP را با : به هم وصل کرد و یک محدوده‌ی پویا ساخت (مثال آخر بالا).
  • آرگومان if_not_found فقط حالت «پیدا نشد» را پوشش می‌دهد؛ اگر ستون نتیجه خطا داشته باشد، خطا عبور می‌کند. این رفتار از IFERROR امن‌تر است.
  • اگر فایلی با XLOOKUP در Excel 2016 یا 2019 باز شود، فرمول به شکل _xlfn.XLOOKUP و نتیجه‌ی #NAME? دیده می‌شود؛ مقادیر ذخیره‌شده تا اولین محاسبه باقی می‌مانند و بعد خراب می‌شوند. برای فایل‌هایی که بیرون می‌روند، نسخه‌ی گیرنده را بپرسید.
  • در match_mode برابر -1، داده لازم نیست مرتب باشد (برخلاف VLOOKUP تقریبی)؛ اما search_mode برابر 2 حتماً داده‌ی مرتب می‌خواهد و روی داده‌ی نامرتب نتیجه‌ی غلط بی‌صدا می‌دهد.
  • آرایه‌ی جست‌وجو و آرایه‌ی نتیجه باید هم‌اندازه باشند؛ ارجاع A:A و B2:B500 خطای #VALUE! می‌دهد. با Table این مشکل خودبه‌خود حل است.
درس قبلی درس بعدی

برای ذخیره‌ی پیشرفت و شرکت در آزمون، وارد شوید — رایگان است.