برای جستجوی دقیق در اکسل توابع مختلفی وجود دارد و تابع VLOOKUP یکی از پرکاربردترین توابع در این مورد می باشد . اما با معرفی تابع XLOOKUP این سوال مطرح می‌شود که کدام یک بهتر است و چه تفاوتی با هم دارند و از کدام استفاده کنیم ؟ در این مقاله به بررسی و مقایسه این دو تابع قدرتمند می‌پردازیم.
فهرست محتوا
بررسی امکانات Vlookup
VLOOKUP که مخفف Vertical Lookup (جستجوی عمودی) است، وظیفه نمایش داده‌ها متناسب با مقدار جستجو شده را دارد. این تابع در دسته توابع جستجو قرار می‌گیرد.
ساختار تابع VLOOKUP: تابع VLOOKUP از آفیس 2003 به بالا در دسترس است و ساختار کلی آن به صورت زیر است: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
- lookup_value: مقداری یا آدرسی است که باید جستجو شود.
- table_array: محدوده‌ای است که جستجو در ستون اول آن انجام می‌شود.
- col_index_num: شماره ستونی است که در صورت یافتن مقدار برای جستجوی دقیق در اکسل در ستون اول، باید داده آن نمایش داده شود. شمارش ستون‌ها از عدد 1 شروع می‌شود.
- [range_lookup]: این آرگومان اختیاری است، اما تأثیر زیادی بر نتیجه دارد. تعیین می‌کند که آیا جستجو باید دقیق (Exact Match) باشد یا تقریبی (Approximate Match).
نکات مهم و محدودیت‌های VLOOKUP:
- جستجو فقط در ستون اول: VLOOKUP همیشه مقدار lookup_value را در ستون اول آرگومان table_array جستجو می‌کند.
- حرکت به راست: این تابع فقط می‌تواند داده‌های موجود در ستون‌هایی که بعد از ستون جستجو قرار دارند را برگرداند , و این خود به تنهایی چالشی برای جستجوی دقیق در اکسل است و نمی‌تواند داده‌های قبل از ستون جستجو را لوکاپ کند.
- توقف در اولین نتیجه: VLOOKUP به محض یافتن اولین نتیجه منطبق در ستون اول، متوقف شده و داده مربوط به آن ردیف را برمی‌گرداند. اگر مقدار جستجو شده چند بار تکرار شده باشد، فقط اولین مورد را نمایش می‌دهد.
- آرگومان آخر (range_lookup): این آرگومان بسیار مهم است و معمولاً برای جستجوی دقیق باید 0 یا FALSE وارد شود. اگر این آرگومان وارد نشود یا TRUE (یا 1) باشد، تابع به دنبال یک تطابق تقریبی می‌گردد. در حالت تطابق تقریبی، داده‌های ستون جستجو باید به صورت صعودی مرتب شده باشند. در غیر این صورت، نتایج نادرست خواهد بود.
- انطباق نوع داده: نوع داده وارد شده در lookup_value باید با نوع داده در ستون اول table_array همخوانی داشته باشد، در غیر این صورت ارور می‌دهد.
خطاهای متداول ویلوکاپ در جستجوی دقیق در اکسل
#N/A: این خطا معمولاً زمانی رخ می‌دهد که مقدار lookup_value در ستون اول table_array یافت نشود. همچنین اگر table_array هنگام کپی کردن فرمول جابجا شود (در صورت عدم استفاده از رفرنس مطلق $F4$) یا نوع داده‌ها مطابقت نداشته باشد، این خطا دیده می‌شود. وجود فاصله‌های اضافی (Space) در انتهای متن نیز می‌تواند باعث عدم تطابق دقیق و بروز خطای #N/A شود.
برای مدیریت خطای #N/A در VLOOKUP می‌توان از توابعی مانند IFERROR یا IFNA استفاده کرد.
نیاز به رفرنس مطلق: هنگام کپی کردن فرمول VLOOKUP به پایین، برای جلوگیری از جابجایی محدوده table_array، باید از رفرنس مطلق (با استفاده از کلید F4 برای اضافه کردن علامت $) استفاده کرد.
پیچیدگی برای سناریوهای خاص: برای انجام جستجو با چند شرط یا نمایش داده‌های تکراری یا جستجو در ستون‌های قبل از ستون جستجو، استفاده از VLOOKUP به تنهایی ممکن نیست و نیاز به ترکیب با توابع دیگر (مانند INDEX/MATCH, CHOOSE, IF, TEXTJOIN, LEN, INT) دارد. توابع INDEX/MATCH اغلب جایگزین‌های بهتری برای غلبه بر محدودیت‌های VLOOKUP هستند. تابع FILTER نیز برای نمایش نتایج تکراری پیشنهاد می‌شود. جستجوی دقیق در اکسل توسط تابع Filter
بررسی تابع XLOOKUP
تابع XLOOKUP یک تابع جدیدتر است که در واقع نسخه بسیار پیشرفته و انعطاف‌پذیرتر VLOOKUP محسوب می‌شود و برای رفع محدودیت‌های آن در آفیس 2021 و مایکروسافت 365 اضافه شده است. این تابع به خوبی از پس وظایف خود برآمده است. و می تواند پاسخگوی جستجوی دقیق در اکسل باشد .
ساختار تابع XLOOKUP: XLOOKUP ساختاری متفاوت و ساده‌تر نسبت به VLOOKUP دارد: =XLOOKUP(lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode]).
- lookup: مقداری است که باید جستجو شود. (معادل lookup_value در VLOOKUP).
- lookup_array: محدوده‌ای است که می‌خواهید مقدار lookup در آن جستجو شود. (معادل ستون جستجو در table_array در VLOOKUP، اما فقط ستونی که در آن جستجو انجام می‌شود).
- return_array: محدوده‌ای است که مقادیر مورد نظر برای بازگرداندن در آن قرار دارد. (معادل ستونی که با col_index_num در VLOOKUP مشخص می‌شد).
آرگومان‌های اختیاری XLOOKUP:
- [not_found]: این آرگومان برای مدیریت خطا تعبیه شده است. اگر جستجو نتیجه‌ای نداشته باشد، مقداری که در این آرگومان وارد شده، نمایش داده می‌شود، به جای خطای #N/A. این یک قابلیت داخلی است که نیاز به ترکیب با IFERROR/IFNA را از بین می‌برد.
- [match_mode]: شیوه تطابق را مشخص می‌کند.
- 0: تطابق دقیق (Exact Match). این پیش‌فرض XLOOKUP است.
- -1: تطابق دقیق یا اولین مقدار کوچکتر بعدی.
- 1: تطابق دقیق یا اولین مقدار بزرگتر بعدی.
- 2: تطابق با استفاده از وایلدکارت‌ها (wildcard characters مانند * و ?).
- [search_mode]: جهت جستجو را مشخص می‌کند.
- 1: جستجو از بالا به پایین یا چپ به راست. این پیش‌فرض است.
- -1: جستجو از پایین به بالا یا راست به چپ. برای یافتن آخرین تکرار یک آیتم مفید است.
- 2: جستجو با مرتب‌سازی صعودی (از کوچک به بزرگ).
- -2: جستجو با مرتب‌سازی نزولی (از بزرگ به کوچک).
مزایای XLOOKUP نسبت به VLOOKUP:
- انعطاف‌پذیری در جهت جستجو: XLOOKUP هم به صورت عمودی و هم افقی جستجو می‌کند.
- بازگرداندن از هر ستون: می‌تواند مقادیر را از ستون‌هایی که قبل یا بعد از lookup_array قرار دارند، برگرداند. موقعیت ستون نتیجه نسبت به ستون جستجو اهمیتی ندارد و مانند Vlookup می توان از آن برای جستجوی دقیق در اکسل استفاده کرد
- تطابق دقیق به صورت پیش‌فرض: حالت پیش‌فرض جستجو در XLOOKUP تطابق دقیق است. این برعکس VLOOKUP است که پیش‌فرض آن تطابق تقریبی است و اغلب باعث خطا می‌شود.
- مدیریت خطای داخلی: آرگومان [not_found] به صورت داخلی خطاهایی مانند #N/A را مدیریت کرده و پیام یا مقدار دلخواه شما را نمایش می‌دهد.
- جستجوی چندگانه و پیشرفته: XLOOKUP می‌تواند به راحتی چند ستون نتیجه را همزمان برگرداند. همچنین انجام جستجو با چند شرط با XLOOKUP راحت‌تر است (مانند استفاده تو در تو). می‌تواند در چند محدوده مختلف جستجو کند.
- جستجو از انتها: قابلیت [search_mode] به شما امکان می‌دهد از انتهای لیست به دنبال مقدار بگردید (مثلاً برای یافتن آخرین خرید یک کالا).
- پشتیبانی از وایلدکارت: XLOOKUP به صورت داخلی از وایلدکارت‌ها پشتیبانی می‌کند.
- ساده‌سازی وظایف پیچیده: وظایفی مانند جستجوی دو طرفه (Two-way lookup) که با VLOOKUP و INDEX/MATCH پیچیده بود، با XLOOKUP آسان‌تر انجام می‌شود.
- عدم نیاز الزامی به مرتب‌سازی برای تطابق تقریبی: بر خلاف VLOOKUP، در XLOOKUP برای تطابق تقریبی (match_mode -1 یا 1) نیازی نیست که حتماً داده‌ها از قبل مرتب شده باشند.
محدودیت‌ها و نکات XLOOKUP:
- سازگاری (Compatibility): اصلی‌ترین محدودیت XLOOKUP این است که فقط در نسخه‌های جدید اکسل (Office 2021 و مایکروسافت 365) موجود است. اگر فایل اکسل شما حاوی تابع XLOOKUP باشد و آن را برای کاربری که از نسخه قدیمی‌تر اکسل استفاده می‌کند ارسال کنید، فرمول‌ها با خطای #NAME! مواجه می‌شوند.
- طول آرایه‌ها: lookup_array و return_array باید طول (تعداد ردیف یا ستون) یکسانی داشته باشند، در غیر این صورت با خطای #VALUE! مواجه خواهید شد.
- آدرس دهی بین فایل‌ها: اگر از XLOOKUP برای آدرس دهی بین دو فایل اکسل مجزا استفاده کنید، هر دو فایل باید باز باشند، در غیر این صورت خطای #REF! رخ می‌دهد.
نتیجه گیری ، Vlookup یا Xlookup ؟
با توجه به مقایسه بالا، مشخص است که تابع XLOOKUP از نظر امکانات، انعطاف‌پذیری و سهولت استفاده به مراتب برتر از VLOOKUP است. و البته هردو آنها در صورت استفاده صحیح می توانند پاسخگوی جستجوی دقیق در اکسل باشند .
- اگر شما یا کاربرانی که فایل اکسل را با آنها به اشتراک می‌گذارید، از نسخه‌های جدید اکسل (Office 2021 یا Microsoft 365) استفاده می‌کنید، اکیداً توصیه می‌شود از XLOOKUP استفاده کنید. این تابع بسیاری از مشکلات و محدودیت‌های VLOOKUP را حل کرده و فرمول‌نویسی را ساده‌تر می‌کند.
- اگر نیاز به حفظ سازگاری با نسخه‌های قدیمی‌تر اکسل دارید، چاره‌ای جز استفاده از VLOOKUP (یا جایگزین قدرتمند دیگری مانند ترکیب INDEX و MATCH) نخواهید داشت.
به عبارت دیگر، XLOOKUP به عنوان “قهرمان جدید توابع جستجو” مطرح است و در صورت در دسترس بودن، جایگزین مناسب برای VLOOKUP است، اما سازگاری با نسخه‌های قدیمی همچنان VLOOKUP را برای بسیاری از کاربران ضروری نگه می‌دارد.
برای درک بهتر و تسلط بر توابع اکسل، تماشای ویدئوهای آموزشی و تمرین با مثالهای متنوع بسیار مفید است. میتوانید فایل‌های تمرینی مرتبط را نیز دانلود کنید.
یادگیری اصولی اکسل مسیر شما را هموار می‌کند. برای یادگیری بیشتر، میتوانید از منابع آموزشی جامع استفاده کنید.
