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

= INDEX(array,rownum,columnnum)
توجه داشته باشید که در بالا ساختار ساده فرمول INDEX نشان داده شده است اما استفاده از ساختار دیگر فرمول INDEX نیز مشابه با همین روش است

= MATCH(lookup_value,lookup_array,match_type)

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


= INDEX(array, MATCH(lookup_value,lookup_array,match_type),column_num)

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

حال به جای آنکه شماره سطر مورد نظر را به صورت دستی وارد نماییم از فرمول MATCH برای یافتن شماره سطر مربوطه استفاده می‌کنیم، برای این کار در آرگیومنت دوم فرمول INDEX فرمول MATCH را می‌نویسیم:

با توجه به اختیاری بودن آرگیومنت سوم آن را خالی گذارده (البته شما می‌توانید حسب نیاز در آن پارامتر مورد نظرتان را بنویسید) تابع MATCH را می‌بندیم و شماره ستونی را که می‌خواهیم مقدار آن را برگردانیم (در اینجا ستون دوم مد نظر است) می‌نویسیم.:

به این ترتیب شما اولین جستجوی پویای خود را با استفاده از ترکیب دو فرمول INDEX و MATCH به پایان رساندید.
یک قدم به جلو
اگر کمی خلاقانه تر به موضوع نگاه کنیم خواهیم دید که تعیین آرگیومت سوم از فرمول INDEX را نیز به فرمول MATCH محول کرد و به این ترتیب فرمولی به تمام معنا پویا ایجاد نمود.
ساختار فرمول فوق به صورت زیر است:

= INDEX(array, MATCH(lookup_value,lookup_array,match_type), MATCH(lookup_value,lookup_array,match_type))

برای درک آن فرض کنید می‌خواهیم در کاربرگ فوق مقدار L.Name را با استفاده از فرمول ترکیبی فوق برگردانیم روش کار دقیقا به مانند مرحله قبل است با این تفاوت که شماره سنون را نیز با استفاده از MATCH مشخص خواهیم کرد:

به نظر شما با این فرمولها چه ارزشی برگردانده خواهد شد؟

درصورت تمایل می‌توانید کاربرگ مورد استفاده در این پست را از اینجا دانلود نمایید

آخرین ویرایش 23 سپتامبر 2014

samir karamkhani