آموزش تابع VLOOKUP در اکسل

تابع VLOOKUP از توابع جستجوگر (Lookup & Reference) بوده و در جداولی که بصورت عمودی مرتب شده‌اند کاربرد دارد. کارکرد VLOOKUP به این صورت است که مقداری را گرفته و در ستون اول جستجو می‌کند و سرانجام داده دیگری در همان ردیف (داده متناظر آن) را برمی‌گرداند.

هدف تابع

جستجو در جداول عمودی

خروجی

مقدار متناظر داده ورودی در n ستون بعد

ساختار

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

آرگومان‌ها

lookup_value: مقداری که باید در ستون اول جدول جستجو شود.
table_array: جدول یا محدوده‌ای که جستجو در آن صورت می‌گیرد.
col_index_num: شماره ستونی که داده متناظر از آن استخراج می‌شود.
range_lookup [اختیاری]: نوع مطابقت (دقیق یا تقریبی).

*مقدار TRUE در آرگومان آخر برای جستجوی تقریبی و مقدار FALSE و یا صفر برای جستجوی دقیق استفاده می‌شود (مقدار پیشفرض این آرگومان TRUE می‌باشد).

کاربرد

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

=VLOOKUP(F6,B5:D11,3,0)

ترجمه: مقدار سلول F6 را در محدوده B5 تا D11 پیدا کن و داده متناظر آن را که در ستون سوم وجود دارد بازگردان.

در صورتی که به دنبال نمایش نام محصول باشیم، کافیست در فرمول بالا به جای عدد ۳، عدد ۲ را در آرگومان سوم قرار دهیم.

تابع VLOOKUP فقط به سمت جلو حرکت می‌کند و در مواردی مثل تصویر زیر، اگر بخواهیم از “کد کالا” استفاده کنیم، فقط ستون‌های نام و نرخ در دسترس می‌باشد و به مقادیر ستون “کد انبار” دسترسی نداریم. برای رفع این محدودیت باید ستون‌های جدول را جابجا کرده و یا از ستون کمکی (مثل زیر) استفاده کنیم:

همانطور که در تصویر مشاهده می‌کنید به دلیل اینکه “کد انبار” قبل از “کد کالا” بوده، یک ستون کمکی ایجاد کردیم و آن را برابر سلول متناظر در ستون “کد کالا” (C4) قرار دادیم و سپس فرمول زیر را نوشتیم:

=VLOOKUP(G4,A3:E9,2,0)

*در نسخه ۲۰۲۱ اکسل، تابع XLOOKUP اضافه شده که به کمک آن می‌توان در ستون‌های قبل هم جستجو کرد.

این تابع از کارکترهای wildcard نیز پشتیبانی می‌کند. فرض کنید می‌خواهیم نرخ را بر اساس نام محصول بیابیم.

برای اینکار فرمول را به این شکل می‌نویسیم (به دلیل متنی بودن آرگومان اول، قبل و بعد آن از ” استفاده شده است):

=VLOOKUP("*فله*",C2:D8,2,0)

ترجمه: سلولی را که حاوی عبارت “فله” است را پیدا کن و ستون بعد آن را بازگردان.

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

تطابق تقریبی

VLOOKUP دارای دو حالت تطبیق دقیق و تقریبی است که توسط آرگومان چهارم (range_lookup) اجرا می‌شود. وقتی بهترین تطابق را می‌خواهید، نه لزوما مطابقت دقیق، باید از حالت تقریبی استفاده کنید. برای مثال می‌خواهیم طبق محدوده G2 تا H7 تصویر زیر، نرخ کمیسیون افراد را تعیین کنیم (از ۵۰هزار تا ۷۰هزار، ۳درصد؛ از ۷۰هزار تا ۱۰۰هزار، ۴درصد و … و بالای ۲۰۰هزار، ۷درصد). همانطور که ملاحظه می‌کنید به دنبال عددی خاص نیستیم و داده‌ای را که در محدوده موردنظر وجود دارد را جستجو می‌کنیم.

=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) به صورت صعودی مرتب شده باشد.

*در مثال فوق از کارکتر $ برای ثابت کردن محدوده استفاده گردید.

تطابق دقیق

زمانی که به دنبال عددی خاص می‌گردیم، باید آرگومان آخر را برابر ۰ یا FALSE قرار دهیم تا جستجو دقیق انجام گیرد. فرض کنید لیست طولانی از کدها داریم و برای اطمینان از وجود کد ۱۰۲۶ آن را فراخوانی می‌کنیم:

همانطور که در تصویر مشخص است فرمول با خطای #N/A مواجه شده که به معنای عدم وجود این کد در لیست می‌باشد. در صورتی که بخواهید بجای این ارر عبارت دلخواهی نمایش دهد (بطور مثال “چنین عددی وجود ندارد”)، می‌توانید از تابع IFNA استفاده کنید.

جستجوی دو طرفه

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

=VLOOKUP(I3,B2:F8,MATCH(I4,B2:F2,0),0)

ترجمه: مقدار سلول I3 را در جدول جستجو کن و داده متناظر ستونی که با سلول I4 مطابقت دارد را بازگردان.

معیارهای چندگانه

تابع VLOOKUP نمی‌تواند چندین معیار را جستجو کند. برای رفع این محدودیت می‌توان از ستون کمکی برای چسباندن معیارها به هم و ایجاد یک معیار جدید استفاده کرد.

در این مثال، ستون‌های C و D را با کارکتر & بهم چسباندیم.

ورژن مورد نیاز

اکسل ۲۰۰۳ و بالاتر

پی‌نوشت

*دقت کنید که نوع مقدار وارد شده در آرگومان اول باید با نوع داده در ستون اول یکسان باشد. مثلا اگر مقدار آرگومان عدد باشد و داده‌های ستون اول جدول با فرمت متنی ذخیره شده باشد، با خطای #N/A مواجه خواهید شد.
*VLOOKUP به حروف کوچک و بزرگ حساس نیست.
*تابع VLOOKUP از کاراکترهای wildcards برای تطابق پشتیبانی می‌کند.
*اکسل همه اعداد بجز صفر را TRUE برمی‌گرداند.