تابع VLOOKUP و نحوه رفع محدودیت این تابع
تابع VLOOKUP پرکاربردترین تابع جستجوگر می باشد و با جستجو در ستون اول جدول داده ها ، مقدار مشخصی را یافته و سپس مقدار متناظر با آن را در ستون های بعدی پیدا کرده و به ما نمایش می دهد.
ساختار تابع:
= VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )
اجزاء:
lookup_value
داده ای که میخواهید در محدوده جدول به دنبال آن بگردید. دقت کنید که این
داده باید در اولین ستون محدوده انتخابی مرحله بعد وجود داشته باشد.
table_array
جدولی که میخواهید درون آن کد یا عبارت مورد جستجو را پیدا کنید. عبارت
مورد جستجو باید اولین ستون این جدول باشد و ستون نتیجه نیز در محدوده جدول
انتخاب شده موجود باشد.
col_index_num
شماره ستون در جدول که میخواهید مقدار آن را به ما نمایش دهد.
range_lookup
این قسمت تابع اختیاری می باشد. در صورتی که این قسمت را وارد نکنید مقدار پیش فرض (TRUE) در نظر گرفته خواهد شد.
اگر مقدار TRUE یا ۱ را وارد کنید و عبارت مورد جستجو یافت نشد نزدیکترین عدد یا عبارت را نشان خواهد داد. در صورتی که جدول مرتب (سورت) نباشد حتی اگر عبارت جستجو وجود داشته باشد اولین نتیجه مشابه را نشان خواهد داد.
اگر مقدار FALSE یا ۰ را وارد کنید دقیقا همان عبارت مورد جستجو را پیدا می کند و در صورتی که نتیجه ای در برنداشت خطای #N/A را نشان می دهد.
رفع محدودیت تابع Vlookup:
در اینکه تابع VLOOKUP یکی از قوی ترین و مفیدترین فرمول هاست شکی نیست. اما این تابع دارای یک اشکال اساسی است، شما نمی توانید توسط این تابع عملیات جستجو به سمت راست را انجام دهید. (اگر جهت صفحه فارسی باشد جستجو به سمت راست و اگر انگلیسی باشد جستجو به سمت چپ امکان پذیر نیست). به طور مثال اگر ستون نتیجه قبل از ستون lookup_value باشد این تابع نمیتواند اینکار را انجام دهد.
یکی از راه حل هایی که شما در مواجهه با این مشکل میتوانید انجام دهید جابجایی ستونی که lookup_value درون آن وجود دارد به ابتدای محدوده جدول انتخابی است اما این راه حل نیز چندان مناسب نیست و در مواردی که ما قصد این جابجایی را نداریم ما را با مشکل روبرو می کند.
راه حل دوم استفاده از ترکیب تابع index و match می باشد که البته بسیار راه حل مناسبی بوده و حتی می تواند جایگزین مناسبی برای Vlookup باشد آموزش این دوتابع را در آینده در سایت قرار خواهیم داد.
اما راه حلی که ما میخواهیم در اینجا ارائه دهیم شامل کنار گذاشتن تابع Vlookup و استفاده از تابع دیگر نیست بلکه شما می توانید با ترکیب تابع Vlookup با تابع Choose به طور کلی محدودیت مذکور را از بین ببرید و همچنان طرفدار این تابع پرکاربرد باقی بمانید.
روش کار:
با استفاده از تابع Choose به صورت آرایه ای می توانید یک جدول را در حافظه تابع ذخیره کنید، این جدول می تواند شامل دو ستون باشد که باید طوری این کار را انجام دهیم که ستون اول جدول ستونی که کد جستجوی ما درون آن قرار دارد باشد و ستون دوم ستون نتیجه ما باشد سپس ما نتیجه این تابع که یک جدول شامل دو ستون می باشد را در قسمت Table_array قرار میدهیم و به این صورت محدودیت تابع Vlookup به طور کلی از بین می رود.
=choose({1,2},lookup column,result column)
در فرمول فوق بجای lookup column ستونی که کد مورد جستجو درون آن قرار دارد را انتخاب می کنید و بجای result column ستونی که نتیجه درون آن قرار دارد را انتخاب می کنید.
نکته: اگر تابع فوق را به تنهایی بنویسید با توجه به اینکه نتیجه آن یک آرایه (جدول) شامل دو ستون می باشد در یک سلول قابل نمایش نخواهد بود و نتیجه خطا مشاهده خواهید کرد اما وقتی آن را درون Vlookup قرار می دهید نتیجه درستی به ما خواهد داد.
پس از این کار ما یک جدول دو ستونه در حافظه تابع Choose داریم و کافیست این تابع را درون تابع vlookup قرار دهیم.
=vlookup(lookup_value,choose({1,2},lookup column,result column),2,0)
در نتیجه ما هیچ محدودیتی برای اینکه ستون مورد جستجو قبل از ستون نتیجه باشد را نخواهیم داشت.
امیدوارم توضیحات واضح بوده و از این آموزش لذت ببرید.