آموزش تابع XLOOKUP در اکسل

تابع XLOOKUP از توابع جستجوگر (Lookup & Reference) بوده و یک جایگزین مدرن و انعطاف‌پذیر برای توابع قدیمی چون LOOKUP، HLOOKUP و VLOOKUP ‌می‌باشد. کارکرد XLOOKUP به این صورت است که مقداری را گرفته و در ستون یا ردیف مدنظر جستجو می‌کند و سرانجام داده متناظر آن را در ستون/ردیف هدف برمی‌گرداند.

هدف تابع

جستجوی مقادیر در جداول عمودی/افقی

خروجی

مقدار(های) متناظر داده ورودی

ساختار

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

آرگومان‌ها

lookup_value: مقداری که در محدوده/آرایه به دنبال آن هستیم.
lookup_array: محدوده/آرایه‌ای که جستجو در آن صورت می‌گیرد.
return_array: محدوده/آرایه‌ای که داده متناظر از آن استخراج می‌شود.
if_not_found [اختیاری]: مقداری که باید در زمان عدم تطابق برگردانده شود.
match_mode [اختیاری]: نوع مطابقت (دقیق، دقیق یا بزرگتر، دقیق یا کوچکتر و یا جزئی)
search_mode [اختیاری]: شیوه جستجو (از بالا، از پایین، از بزرگترین و یا از کوچکترین)

کاربرد

همانطور که در بالا گفته شد، تابع XLOOKUP هم بصورت عمودی و هم افقی می‌تواند مقدار متناظر داده وارد شده را بازیابی کند. برای مثال در جدول مقایسه استهلاک سه سال اخیر، می‌خواهیم با جستجو عمودی هزینه استهلاک “تاسیسات” سال ۱۴۰۰ را بازگردانیم:

=XLOOKUP(H5,C5:C9,D5:D9)

ترجمه: مقدار سلول H5 را در محدوده C5 تا C9 پیدا کن و داده متناظر آن را در ستون D5 تا D9 بازگردان.

این تابع قابلیت برگرداندن داده‌های قبل را دارد و مانند تابع VLOOKUP نیاز به ستون کمکی ندارد. برای مثال جهت نمایش کد هزینه “تاسیسات” کافیست فرمول فوق را به این شکل اصلاح کنیم:

=XLOOKUP(H5,C5:C9,B5:B9)---> 853

حال اگر بخواهیم بصورت افقی هزینه استهلاک اثاثیه و منصوبات را در سال “۱۴۰۲” جستجو کنیم بایستی فرمول زیر را بنویسیم:

=XLOOKUP(H9,C4:F4,C8:F8)

ترجمه: مقدار سلول H9 را در محدوده C4 تا F4 پیدا کن و داده متناظر آن را در ردیف C8 تا F8 بازگردان.

علاوه بر موارد یاد شده، XLOOKUP نیز همانند تابع INDEX توانایی بازگرداندن ردیف و یا ستون را نیز دارد. برای مثال اگر بخواهیم هزینه استهلاک سه سال اثاثیه و منصوبات را برگردانیم کافیست فرمول فوق را به صورت زیر ویرایش کنیم:

=XLOOKUP(C4:F4,C4:F4,C8:F8)

تطابق دقیق

تابع XLOOKUP دارای چهار حالت تطبیق «تطابق دقیق، تطابق دقیق یا کوچک‌ترین بعدی، تطابق دقیق یا بزرگ‌تر بعدی و تطابق با کارکترهای wildcard» است که توسط آرگومان پنجم (match_mode) تعیین می‌شود. این آرگومان اختیاری بوده و در صورت نادیده گرفته شدن، حالت پیشفرض (تطابق دقیق) را اعمال خواهد کرد.

معمولا زمانیکه از XLOOKUP استفاده می‌کنیم، انتظار تطبیق دقیق داریم. به همین دلیل اکسل این نوع جستجو را حالت پیشفرض این تابع قرار داده و در مواقعی که مانند مثال‌های فوق به دنبال تطابق دقیق باشیم می‌توان این آرگومان را نادیده گرفت و یا مقدار ۰ را وارد کرد. برای مثال می‌خواهیم عدد ۵۰۰۰ را در آرایه {۱۵۶۹,۲۳۷۵,۳۳۵۱,۴۲۷۰,۵۰۰۰} جستجو کنیم. بدین منظور می‌توان هر یک از فرمول‌های زیر را بنویسیم:

=XLOOKUP(5000,{1569,2375,3351,4270,5000},{1569,2375,3351,4270,5000})--->5000
=XLOOKUP(5000,{1569,2375,3351,4270,5000},{1569,2375,3351,4270,5000},,0)--->5000

تطابق تقریبی

زمانیکه قصد داشته باشیم در صورت عدم تطابق، بهترین تطبیق بازگردانده شود در آرگومان match_mode عدد ۱ را برای مقدار بزرگتر و -۱ را برای مقدار کوچکتر وارد می‌کنیم. برای مثال می‌خواهیم عدد ۲۵۰۰ را در آرایه مثال قبل در دو حالت کوچکترمساوی و بزرگترمساوی جستجو کنیم:

=XLOOKUP(2500,{1569,2375,3351,4270,5000},{1569,2375,3351,4270,5000},,-1)--->2375

ترجمه: عدد ۲۵۰۰ را در آرایه وارد شده پیدا کن و در صورت شکست، نزدیک‌ترین داده کوچکتر از ۲۵۰۰ را نشان بده.

=XLOOKUP(2500,{1569,2375,3351,4270,5000},{1569,2375,3351,4270,5000},,1)--->3351

*XLOOKUP مانند تابع MATCH نیاز به داده‌های سورت شده ندارد.

تطابق جزئی

XLOOKUP نیز همانند تابع VLOOKUP از کارکترهای wildcard پشتیبانی می‌کند؛ با این تفاوت که باید به آن بفهمانیم به دنبال تطابق جزئی هستیم. بدین منظور می‌بایست در آرگومان match_mode مقدار ۲ را وارد کنیم. این ویژگی زمانی کاربرد دارد که بخواهیم با وارد کردن قسمتی یا کل شرح، مقدار متناظر آن را بیابیم. برای مثال می‌خواهیم هزینه استهلاک ماشین آلات و تجهیزات را در سال ۱۴۰۱ بازگردانیم. برای اینکار فرمول را به این شکل می‌نویسیم (به دلیل متنی بودن آرگومان اول، قبل و بعد آن از ” استفاده شده است):

=XLOOKUP("*ماشین*",C4:C9,E4:E9,,2)

ترجمه: سلول حاوی عبارت “ماشین” را در ستون C4 تا C9 سرچ کرده و داده متناظر آن را در ستون E4 تا E9 نشان بده.

*در زمان استفاده از این قابلیت در نظر داشته باشید که XLOOKUP اولین مطابقت را بازمی‌گرداند.
*در تطابق جزئی ستون مورد جستجو باید متن و یا فرمت سلول‌ها متنی باشد.

پیام عدم تطابق

وقتی XLOOKUP نتواند مطابقتی پیدا کند، مانند سایر توابع مشابه خطای #N/A برمی‌گرداند. با این تفاوت که XLOOKUP یک آرگومان اختیاری با عنوان if_not_found جهت مدیریت خطای داخلی ارائه می‌کند که می‌توان جهت نمایش مقداری در زمان مواجه با خطا استفاده کرد. این مقادیر می‌توان عدد، علائم و یا متنی چون «یافت نشد»، «عدم مطابقت»، «بدون نتیجه» و غیره باشد. لازم به ذکر است به دلیل متنی بودن این آرگومان، میبایست مقدار مدنظر را بین دو علامت ” قرار دهید.

=XLOOKUP(2500,{1569,2375,3351,4270,5000},{1569,2375,3351,4270,5000})--->#N/A
=XLOOKUP(2500,{1569,2375,3351,4270,5000},{1569,2375,3351,4270,5000},"یافت نشد")--->یافت نشد

ترجمه: مقدار “۲۵۰۰” را در ارائه وارد شده پیدا کن و در صورت عدم تطبیق، متن “یافت نشد” را نشان بده.

شیوه جستجو

به طور پیش فرض، XLOOKUP از اولین مقدار داده شروع به تطبیق می‌کند اما با استفاده از آرگومان ششم این تابع با عنوان search_mode می‌توان شیوه جستجو را کنترل کرد. این آرگومان اختیاری بوده و زمانی که نادیده گرفته شود از حالت پیشفرض خود استفاده می‌کند. عدد ۱ از اولین داده، عدد -۱ از آخرین داده، عدد ۲ از کوچکترین داده و عدد -۲ از بزرگترین داده جستجو را آغاز می‌کند.

=XLOOKUP(3,{4,3,1,3,2},{"1st","2nd","3rd","4th","5th"},,,1)--->2nd
=XLOOKUP(3,{4,3,1,3,2},{"1st","2nd","3rd","4th","5th"},,,-1)--->4th
=XLOOKUP(3,{4,3,1,3,2},{"1st","2nd","3rd","4th","5th"},,,2)--->4th
=XLOOKUP(3,{4,3,1,3,2},{"1st","2nd","3rd","4th","5th"},,,-2)--->2nd

جستجو دوطرفه

XLOOKUP برای ایجاد جستجو دو طرفه پویا، نیازی به ادغام تابع دیگر نداشته و با ترکیب دو تابع XLOOKUP مطابقت همزمان سطر و ستون امکان‌پذیر می‌گردد. برای روشن شدن موضوع، مثال جستجو دوطرفه تابع VLOOKUP را اینبار با استفاده از تابع XLOOKUP حل می‌کنیم:

=XLOOKUP(I3,B3:B8,XLOOKUP(I4,C2:F2,C3:F8))

معیارهای چندگانه

تابع XLOOKUP راه داخلی برای جستجو با معیار چندگانه ارائه نمی‌دهد اما از آن پشتیبانی می‌کند. برای درک بهتر این موضوع میخواهیم مثال VLOOKUP را با استفاده از تابع XLOOKUP بازنویسی کرده و مبلغ فروش محصول “پنیر” توسط بازاریاب “۱۰۰۵” را بازگردانیم:

=XLOOKUP(1,(C3:C8=H2)*(D3:D8=H3),E3:E8)

مقایسه XLOOKUP و VLOOKUP

XLOOKUP یک جایگزین ارتقا یافته برای تابع VLOOKUP است. هر دو تابع مقداری را جستجو کرده و در نهایت داده متناظر آن را برمی‌گردانند. همچنین از تطابق دقیق، تقریبی و جزئی پشتیبانی می‌کنند و در صورت عدم تطابق، خطای #N/A برمی‌گردانند. ۹ تفاوت اصلی بین توابع XLOOKUP و VLOOKUP عبارتند از:

1. XLOOKUP به طور پیش‌فرض مطابقت دقیق انجام می‌دهد؛ اما مطابقت پیشفرض تابع VLOOKUP، تطابق تقریبی است.
2. XLOOKUP نیازی به مرتب‌سازی مقادیر در هنگام انجام یک تطابق تقریبی ندارد.
3. در تطابق تقریبی، تابع XLOOKUP می‌تواند مورد بزرگتر بعدی یا کوچکتر بعدی را پیدا کند؛ درحالیکه VLOOKUP موقعیت یک داده قبل را بازمی‌گرداند.
4. تابع XLOOKUP برای تطابق جزئی نیاز به دستور دارد.
5. XLOOKUP خطای عدم تطابق را در داخل خود مدیریت می‌کند.
6. در تابع XLOOKUP می‌توان جستجوی معکوس انجام داد.
7. XLOOKUP می‌تواند یک جستجوی باینری انجام دهد که به طور خاص سرعت پردازش را بهینه می‌کند (مقدار ۲ و -۲ در آرگومان جستجو).
8. تابع XLOOKUP از معیارهای چندگانه پشتیبانی می‌کند.
9. برخلاف VLOOKUP، تغییرات در ساختار جدول (درج یا حذف ستون‌ها) نتیجه نهایی تابع XLOOKUP را تحت تاثیر قرار نمی‌دهد.

ورژن مورد نیاز

اکسل ۲۰۲۱ و بالاتر

پی‌نوشت

*اندازه آرایه‌های جستجو (lookup_array) باید با آرایه‌های نتیجه (return_array) یک اندازه و سازگار باشند، در غیر اینصورت XLOOKUP خطای #VALUE! برمی‌گرداند.