توی قسمت های قبل راجع به توابع VLOOKUP، INDEX و MATCH آموزش های مقدماتی ارائه شد. که در انتهای این پست می تونید به آن ها دسترسی داشته باشید.وقتی بخواهید تصمیم بگیرید از چه توابعی برای جستجو (lookup) استفاده کنید، اکثر متخصصان اکسل اتفاق نظر دارند که ترکیب INDEX و MATCH بهتر از VLOOKUP و توابع مشابه است. اما به دو دلیل اکثر افراد از تابع VLOOKUOP استفاده می کنند:

1) سادگی استفاده از VLOOKUP

2) عدم آشنایی و شناخت مزیت های ترکیب INDEX و MATCH

برای آشنایی با تابع index و تابع match در اکسل می توانید این مطلب را مطالعه کنید:

تابع index در اکسل | مثال ها و کاربردها

تابع match در اکسل | مثال ها و کاربردها

تابع vlookup در اکسل | مثال ها و کاربردها

و بدون آگاهی از مزیت های یک موضوع افراد علاقه و رغبتی برای آموختن ترکیبات پیچیده تر ندارند. در این مطلب قسمت داریم همه مزایای استفاده از ترکیب INDEX و MATCH به جای VLOOKUP را برای شما بیان کنیم و شما را متقاعد خواهید کرد که برای همه جستجوهای (lookup) خود از ترکیب توابع INDEX و MATCH استفاده کنید. اگر با تابع VLOOKUP آشنایی ندارید این آموزش را ببینید. (کلیک کنید)

اگر با استفاده ترکیبی توابع INDEX و MATCH آشنایی ندارید، این آموزش ویدئو را مشاهده کنید، لطفا برای مشاهده این آموزش کلیک کنید. بعد از اینکه تابع INDEX و MATCH را آموختید و همواره از این ترکیب استفاده کردید، به دلیل کارایی بالاتر این ترکیب خطاهای شما در اکسل کاهش پیدا خواهد کرد و توانایی شما در رایجاد مدل‌های پیچیده‌تر اکسل، که داده‌هایی با جزئیات بیشتری هستند، به طور قابل توجهی بهبود خواهد یافت.

پیشنهاد مطالعه: 

تابع xlookup در اکسل قابلیت هایی را معرفی کرده است که بسیاری از محدودیت های تابع vlookup را حل کرده است و در بسیاری از موارد دیگر نیاز به ترکیب تابع index و match ندارید، این مطلب را حتما مطالعه کنید:

تابع xlookup در اکسل (تایع جدید در اکسل 2021)

جستجو به سمت راست و چپ

یکی از مزایای کلیدی استفاده از ترکیب توابع INDEX و MATCH این است که می‌توانید ستون مورد جستجوی خود را در سمت راست یا چپ داده‌های خود قرار دهید. (با استفاده از ترکیب VLOOKUP و CHOOSE نیز می‌توانید این کار را انجام دهید.) با استفاده از ترکیب INDEX و MATCH ستون مورد جستجوی جدید بر اساس محاسبات در هر کدام از ستون‌های مجموعه داده‌های شما قابل انجام است. از این رو وقتی شما از ترکیب INDEX و MATCH استفاده می‌کنید، این ستون مورد جستجو می‌تواند در سمت راست یا چپ داده‌های شما باشد.

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

ستون مرجع داینامیک به خطاهای کمتری منجر می‌شود

یکی از تفاوت های اصلی ترکیب INDEX و MATCH با VLOOKUP در این است که تابع VLOOKUP نیازمند یک ستون مرجع ثابت است در حالی که با ترکیب INDEX و MATCH می توانید از ستون مرجع داینامیک استفاده کنید. (برای رفع این مشکل می‌توانید از ترکیب تابع VLOOKUP و MATCH نیز استفاده کنید، برای مشاهده ویدئو این آموزش کلیک کنید.) در استفاده از تابع VLOOKUP اکثر افراد یک عدد مشخص و ثابت که نشان‌دهنده شماره ستونی است که می‌خواهند مقدار مورد نظر را از آن بازگردانند، انتخاب می‌کنند. زمانی که شما از ترکیب INDEX و MATCH استفاده می‌کنید، فرمول به شما اجازه می‌دهد که ستون مورد نظر را به صورت دستی انتخاب کنید.

این کار سبب می‌شود که در هنگام استفاده از ترکیب INDEX و MATCH در کار خود خطای کمتری داشته باشید، زیرا به صورت مستقیم ستون مورد نظر خود را انتخاب می‌کنید.

با استفاده از تابع VLOOKUP، شما کل بازه مورد نظر خود را مشخص می‌کنید، و سپس ستون مرجعی که می‌خواهید مقدار مورد نظر از آن را بازگردانید مشخص می‌شود.

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

دو مزیت بعدی استفاده از ترکیب INDEX و MATCH نیز به این ویژگی تابع VLOOKUP مرتبط است که نیاز به ورود یک ستون مرجع ثابت که می‌خواهید از آن مقدار مورد نظر را بازگردانید، دارد. توجه داشته باشید که می‌توانیم با استفاده از یک فرمول ترکیبی از تابع VLOOKUP و MATCH (این ویدئو آموزشی را مشاهده کنید) یا ترکیب تابع VLOOKUP و HLOOKUP یک فرم ستون مرجع داینامیک ایجاد کنید. با این حال، یادگیری ترکیب توابع INDEX و MATCH بسیار ساده‌تر است و می‌توانید علاوه بر ستون مرجع پویا مزایای بیشتری داشته باشید.

جواز تغییر در تعداد ستون‌ها

بزرگترین مزیت استفاده از ترکیب توابع INDEX و MATCH نسبت به تابع VLOOKUP توانایی اضافه کردن یا حذف کردن ستون در بازه داده‌های مورد بدون تحریف (اشتباه) در نتایج جستجو شماست. هر موقع شما با مجموعه داده زیاد کار می‌کنید، به احتمال خیلی زیاد شما نیاز خواهید داشت ستون‌های خود را ویرایش ‌کنید و به صورت بالقوه ستونی جدیدی در بین داده‌های خود اضافه کنید. با استفاده از VLOOKUP، هر افزایش یا کاهش در تعداد ستون‌ها، که بین ستون مورد جستجو (ستون اول بازه انتخابی در تابع VLOOKUP) و ستونی که می‌خواهیم مقدار مورد نظر را از آن برگردانیم (col_index) نتیجه فرمول شما را تغییر خواهد داد.

به مثال زیر توجه کنید. فرمول ما مقدار نام خانوادگی را از جدول داده های مورد نظر باز می‌گرداند که مقدار «پیروزان» هست. چون از تابع VLOOKUP استفاده کردیم، سومین ستون را به عنوان مقدار بازگشتی، انتخاب کردیم.

اگر ستونی در بین این ستون‌ها اضافه کنیم، مقدار بازگشتی به «رضا» تغییر خواهد کرد. در این صورت مقدار درست برای نام خانوادگی را باز نمی‌گرداند و باید ستون مرجع (بازگشتی) را تغییر دهیم.

اما زمانی که از ترکیب تابع INDEX و MATCH استفاده می‌کنید، شما بدون نگرانی از تغییر در ستون بازگشتی، مجوز تغییر در تعداد ستون‌های بازه انتخابی را دارید.

سادگی در کشیدن و کپی فرمول‌ها

وقتی با مجموعه داده بزرگ کار می‌کنید، شما نیاز دارید که با کشیدن و کپی کردن، فرمول خود را برای چندین سلول و برای جستجو در چندین ستون بنویسید و فقط یک فرمول جستجو نمی نویسید. برای مثال، برای یک شماره پرسنلی می‌خواهیم چندین مقدار از جدول خود در یک ردیف را بازگردانیم. اگر فرمول استاندارد VLOOKUP را بکشیم و کپی کنیم (در حالی که مقدارهای مورد جستجو (lookup_value) و جدول مورد جستجو (table_area) ثابت شده باشند.) جستجو عمل نخواهد کرد، زیرا فقط یک مقدار برای همه ورودی‌ها باز می‌گرداند. این مشکل به دلیل ثابت بودن عدد ستون مرجع (col_num) برای مقدار بازگشتی است. (برای حل این مشکل می‌توانید از ترکیب توابع VLOOKUP و COLUMNS استفاده کنید.)

در ترکیب INDEX و MATCH از آنجایی که می‌توانید ستون بازگشت را به صورت شناور تعریف کرد، با کشیدن و کپی کردن فرمول، ستون بازگشت تغییر خواهد کرد.

عدم محدودیت در بازه انتخابی

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

اگر ما یک ستون جدید به مجموعه داده‌های خود اضافه کنیم، نمی‌توانیم از فرمولی که در ابتدا نوشتیم استفاده کنیم. اگر مقدار ستون بازگشت را به «6» تغییر دهیم با خطای #REF! مواجه می‌شود، زیرا بازه انتخابی ما 5 ستون دارد. برای اینکه فرمول کار کند باید بازه انتخابی خود را هر زمان که ستون جدیدی اضافه می‌کنیم به روز کنیم.

با استفاده از ترکیب توابع INDEX و MATCH شما نیازی ندارید که بازه خاصی را در table_array انتخاب کنید، پس با این مشکل مواجه نخواهید شد.

نیاز به محاسبات کمتر

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

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

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

عدم محدودیت در تعداد کاراکترهای مقدار مورد جستجو

اگر از تابع VLOOKUP استفاده می‌کنید باید دقت داشته باشید که مقدار مورد جستجو نمی‌تواند بیشتر از 255 کاراکتر باشد، در این صورت با خطای #VALUE مواجه خواهید شد. اگر مقدار مورد جستجوی شما بیش از 255 کاراکتر بود، تنها راه استفاده از ترکیب توابع INDEX و MATCH است.