تابع VLOOKUP از توابع جستجوگر (Lookup & Reference) بوده و در جداولی که بصورت عمودی مرتب شده‌اند کاربرد دارد. کارکرد VLOOKUP به این صورت است که مقداری را گرفته و در ستون اول جستجو میکند و سرانجام داده دیگری در همان ردیف (داده متناظر آن) را برمی‌گرداند.تابع vlookup
هدف تابع
جستجو در جداول عمودی
خروجی
مقدار متناظر داده ورودی در n ستون بعد
ساختار
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
آرگومان‌ها
lookup_value: مقداری که باید در ستون اول جدول جستجو شود.
table_array: جدول یا محدوده‌ای که جستجو در آن صورت می‌گیرد.
col_index_num: شماره ستونی که داده متناظر از آن استخراج می‌شود.
range_lookup [اختیاری]: نوع مطابقت (دقیق یا تقریبی).vlookup
*مقدار TRUE در آرگومان آخر برای جستجوی تقریبی و مقدار FALSE و یا صفر برای جستجوی دقیق استفاده می‌شود (مقدار پیشفرض این آرگومان TRUE می‌باشد).
کاربرد
همانطور که در بالا گفته شد، تابع VLOOKUP برای یافتن داده متناظر مقداری خاص بکار میرود. برای روشن‌تر شدن موضوع فرض کنید در جدولی که داده‌های آن بصورت ردیف‌های زیر هم مرتب شده‌اند (جدول عمودی)، می‌خواهیم نرخ فروش محصولی را که کد آن در سلول F6 وارد شده را بیابیم. برای اینکار فرمول زیر را می‌نویسیم:
=VLOOKUP(F6,B5:D11,3,0)
ترجمه: مقدار سلول F6 را در محدوده B5 تا D11 پیدا کن و داده متناظر آن را که در ستون سوم وجود دارد بازگردان.
در صورتی که به دنبال نمایش نام محصول باشیم، کافیست در فرمول بالا به جای عدد 3، عدد 2 را در آرگومان سوم قرار دهیم.
تابع VLOOKUP فقط به سمت جلو حرکت میکند و در مواردی مثل تصویر زیر، اگر بخواهیم از “کد کالا” استفاده کنیم، فقط ستون‌های نام و نرخ در دسترس می‌باشد و به مقادیر ستون “کد انبار” دسترسی نداریم. برای رفع این محدودیت باید ستون‌های جدول را جابجا کرده و یا از ستون کمکی (مثل زیر) استفاده کنیم:فرمول vlookup
به دلیل اینکه “کد انبار” قبل از “کد کالا” بوده، یک ستون کمکی ایجاد کردیم و آن را برابر سلول متناظر در ستون “کد کالا” (C4) قرار دادیم و سپس فرمول زیر را نوشتیم:
=VLOOKUP(G4,A3:E9,2,0)
*در نسخه 2021 اکسل، تابع XLOOKUP اضافه شده که به کمک آن می‌توان در ستون‌های قبل هم جستجو کرد.
این تابع از کارکترهای wildcard نیز پشتیبانی میکند. فرض کنید می‌خواهیم نرخ را بر اساس نام محصول بیابیم.تابع vlookup
برای اینکار فرمول را به این شکل می‌نویسیم (به دلیل متنی بودن آرگومان اول، قبل و بعد آن از ” استفاده شده است):
=VLOOKUP("*فله*",C2:D8,2,0)
ترجمه: سلولی را که حاوی عبارت “فله” است را پیدا کن و ستون بعد آن را بازگردان.
*در زمان استفاده از این تابع در نظر داشته باشید که VLOOKUP اولین مطابقت را بازمی‌گرداند. مثال بالا سه جواب داشته که این تابع اولین پاسخ را نشان داده است.
تطابق تقریبی
VLOOKUP دارای دو حالت تطبیق دقیق و تقریبی است که توسط آرگومان چهارم (range_lookup) اجرا می‌شود. وقتی بهترین تطابق را می‌خواهید، نه لزوما مطابقت دقیق، باید از حالت تقریبی استفاده کنید. برای مثال می‌خواهیم طبق محدوده G2 تا H7 تصویر زیر، نرخ کمیسیون افراد را تعیین کنیم (از 50هزار تا 70هزار، 3درصد؛ از 70هزار تا 100هزار، 4درصد و … و بالای 200هزار، 7درصد). همانطور که ملاحظه می‌کنید به دنبال عددی خاص نیستیم و داده‌ای را که در محدوده موردنظر وجود دارد را جستجو می‌کنیم.
تابع vlookup
=VLOOKUP(C3,$G$3:$H$7,2)
ترجمه: مقدار سلول C3 را در محدوده G3 الی H7 پیدا کن [و اگر پیدا نشد مقدار کمتر آن را جستجو کن] و داده متناظر ستون بعد آن (نرخ) را برگردان.
حالت پیشفرض آرگومان چهارم این تابع TRUE می‌باشد، به همین دلیل آن را به صورت خالی رها کردیم. می‌توان فرمول فوق را به این دو شکل نیز نوشت:
=VLOOKUP(C3,$G$3:$H$7,2,1)
=VLOOKUP(C3,$G$3:$H$7,2,TRUE)
*برای دریافت نتیجه بهتر در تطابق تقریبی، لازم است ستون اول جدول (ستون G) به صورت صعودی مرتب شده باشد.
*در مثال فوق از کارکتر $ برای ثابت کردن محدوده استفاده گردید.
تطابق دقیق
زمانی که به دنبال عددی خاص می‌گردیم، باید آرگومان آخر را برابر 0 یا FALSE قرار دهیم تا جستجو دقیق انجام گیرد. فرض کنید لیست طولانی از کدها داریم و برای اطمینان از وجود کد 1026 آن را فراخوانی می‌کنیم:تابع vlookup
همانطور که در تصویر مشخص است فرمول با خطای #N/A مواجه شده که به معنای عدم وجود این کد در لیست می‌باشد. در صورتی که بخواهید بجای این ارر عبارت دلخواهی نمایش دهد (بطور مثال “چنین عددی وجود ندارد”)، می‌توانید از تابع IFNA استفاده کنید.
جستجوی دو طرفه
معمولا در آرگومان سوم، یک عدد ثابت نوشته می‌شود. با این حال، می‌توانید با استفاده از تابع MATCH، شماره ستون مورد نظر را تعیین کنید. این تکنیک به شما این امکان را می‌دهد تا یک جستجوی دو طرفه پویا ایجاد کنید که در هر دو سطر و ستون مطابقت انجام گیرد. در تصویر زیر، فرمول برای انجام جستجو بر اساس کد و فصل نوشته شده است.تابع vlookup
=VLOOKUP(I3,B2:F8,MATCH(I4,B2:F2,0),0)
ترجمه: مقدار سلول I3 را در جدول جستجو کن و داده متناظر ستونی که با سلول I4 مطابقت دارد را بازگردان.
معیارهای چندگانه
تابع VLOOKUP نمی‌تواند چندین معیار را جستجو کند. برای رفع این محدودیت می‌توان از ستون کمکی برای چسباندن معیارها به هم و ایجاد یک معیار جدید استفاده کرد.تابع vlookup
در این مثال، ستون‌های C و D را با کارکتر & بهم چسباندیم.
ورژن مورد نیاز
اکسل 2003 و بالاتر
در آیدت،
فایل‌های
خود را به فروش بگذارید و یا
مقالات‌تان
را منتشر کنید👋
پاسخ ها