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

نکته: حرف “V” که در ابتدای نام این تابع آمده است مخفف کلمه “Vertical” یعنی عمودی است. این حرف برای تمایز تابع VLOOKUP از تابع HLOOKUP استفاده می‌شود. تابع HLOOKUP به جای ستون، مقادیر را در یک ردیف جستجو می‌کند. حرف H در ابتدای نام تابع HLOOKUP مخفف کلمه “Horizontal” یعنی افقی است.

در این آموزش از ترکیب تابع VLOOKUP با توابع دیگر مرجع هم استفاده کرده‌ایم. پیشنهاد می‌کنم برای یادگیری بهتر آموزش‌های زیر را هم مطالعه کنید:

• آموزش تابع INDEX در اکسل
• آموزش تابع MATCH در اکسل
• آموزش تابع OFFSET در اکسل

ساختار تابع VLOOKUP در اکسل

ساختار تابع VLOOKUP در اکسل به شکل زیر است:

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

1. Lookup_value: مقداری که می‌خواهید در اولین ستون جدول جستجو کنید در این قسمت قرار دهید. این مقدار می‌تواند عدد، تاریخ، متن، آدرس سلول و مقدار برگردانده شده توسط یک تابع دیگر باشد. مقادیر متنی همیشه باید در ” ” محصور شوند.
2. Table_array: محدوده موردنظر که می‌خواهید جستجو در آن انجام شود که شامل دو یا چند ستون از داده است. تابع VLOOKUP همیشه در ستون اول جدول، مقدار موردنظر را جستجو می‌کند که ممکن است حاوی متن، اعداد، تاریخ و مقادیر منطقی باشد.
3. Col_index_num: شماره ستونی که می‌خواهید مقدار موردنظر از آن بازگردانده شود را در این قسمت تایپ کنید. شمارش از سمت چپ‌ترین ستون انجام می‌شود. شماره ۱ به سمت چپ‌ترین ستون اختصاص می‌یابد، ستون بعد شماره ۲ تا الی آخر. (وقتی صفحه اکسل چپ به راست باشد.)
4. Range_lookup: در این قسمت تعیین ‌کنید که آیا برای جستجو به مطابقت تقریبی یا دقیق نیاز دارید.

• • TRUE(مطابقت تقریبی): اگر تطابق دقیقی پیدا نشود، فرمول نزدیکترین مطابقت را جستجو می‌کند، یعنی بزرگترین مقدار کوچکتر از مقدار جستجو را پیدا می‌کند. ستون جستجو نیاز به مرتب سازی به ترتیب صعودی دارد.
  • FALSE (مطابقت دقیق): این فرمول مقداری دقیقاً برابر با مقدار جستجو را پیدا می‌کند.

نکته: سه آرگومان اول ضروری هستند و آرگومان چهارم اختیاری است.

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

مثال ۱: عملکرد تابع VLOOKUP در اکسل

فرض کنید یک استاد دانشگاه هستید و نمرات آزمون اولیه، میان‌ ترم و پایان ترم دانشجویان را در یک فایل اکسل وارد کرده‌اید. حالا می‌خواهید بدانید یک دانشجوی خاص مثلا “کاوه ارجمند” یا هر کدام از دانشجویان‌تان چه نمره‌ای را در “آزمون اولیه” درس “ریاضی ۲” کسب کرده‌اند.

برای این کار نمرات دانشجویان را در ۳ کاربرگ (Sheet) به نام‌های “آزمون اولیه”، “آزمون میان ترم” و “آزمون پایان ترم” وارد کنید.

حالا برای راحتی کار ابتدا یک لیست کشویی از اسامی دانشجویان ایجاد کنید. (نحوه ایجاد لیست کشویی را در آموزش تابع CHOOSE به طور کامل توضیح داده‌ام.( برای این کار یک سلول (F1) را انتخاب کنید. سپس از سربرگ Data روی ابزار Data validation کلیک کنید.

پنجره Data Validation باز می‌شود. در قسمت Allow گزینه List را انتخاب کنید و در قسمت Source سلول‌های (A3:A8) را انتخاب کنید. روی OK کلیک کنید.

خوب حالا لیست شما آماده است.

یک سلول را به عنوان سلول نتیجه (F2) در نظر بگیرید. سپس در نوار فرمول نرم‌افزار روی Fx کلیک کنید. (یا اینکه کلیدهای Shift+F3 را فشار دهید.)

 پنجره Inert Function باز می‌شود. از دسته توابع جستجو و مرجع (Lookup & Reference) تابع VLOOKUP را انتخاب کنید و روی OK کلیک کنید.

پنجره Function Argumentes باز می‌شود. در کادر Lookup_value کلیک کرده و سلول F1 (لیست کشویی اسامی دانشجویان را در آن ایجاد کرده‌اید.) را انتخاب کنید. سپس در کادر Table_array کلیک کرده و محدوده موردنظر (A3:C8) را انتخاب کنید. حالا در کادر Col_index_num عدد ۳ (ستون نمرات درس ریاضی ۲) را تایپ کنید. سپس در کادر Range_lookup مقدار FALSE را برای مطابقت کامل تایپ کنید. در نهایت روی OK کلیک کنید.

مثال ۲: استفاده تابع VLOOKUP از داده‌های کاربرگ‌های مختلف

بعضی اوقات مجبور هستید از داده‌های موجود در کاربرگ‌های دیگر استفاده کنید. فرض کنید، می‌خواهید در کاربرگ ۱ نمره “آزمون میان‌ ترم” (که در کاربرگ دیگری قرار دارد.) درس “ریاضی ۱” یک دانشجوی خاص مثلا “کاوه ارجمند” را ببینید. برای این کار مراحل مثال قبل را طی کنید. این بار در پنجره Function Argumentes در کادر Lookup_value کلیک کرده و سلول H1 را انتخاب کنید.

سپس در کادر Table_array کلیک کرده و  به کاربرگ نمرات آزمون میان ترم بروید و محدوده موردنظر را انتخاب کنید (A3:C8) همانطور که می‌بینید بعد از نام کاربرگ علامت ! درج  می‌شود و سپس محدوده موردنظر قرار می‌گیرد. در کادر Col_index_num عدد ۳ (ستون مربوط به نمرات ریاضی) را تایپ کنید. سپس در کادر Range_lookup مقدار FALSE را برای مطابقت کامل انتخاب کنید. در نهایت روی OK کلیک کنید.

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

VLOOKUP(F1,’آزمون میان ترم’!A3:C8,3,FALSE)

در تصویر زیر، تصاویر کاربرگ‌های “آزمون اولیه” و “آزمون میان ترم” را با هم ادغام کرده‌ام تا مطلب را بهتر درک کنید.

مثال ۳: ترکیب تابع VLOOKUP و MATCH و استفاده از دو لیست کشویی

حالا فرض کنید می‌خواهید در کاربرگ نمرات پایان ترم، هم برای اسامی دانشجویان و هم برای اسامی درس‌ها لیست کشویی درست کنید و با تغییر لیست‌ها نمرات دانشجویان را ببینید. برای این کار در سلول F1 لیست کشویی برای اسامی دانشجویان درست کنید.

در سلول E2 یک لیست کشویی برای اسامی درس‌ها درست کنید.

حالا در سلول F2 فرمول زیر را تایپ کنید.

VLOOKUP(F1,A3:C8,MATCH(E2,A2:C2,0)0)

با تغییر اسامی هر لیست نمره موردنظر نمایش داده می‌شود. به همین راحتی!

چند نکته درباره تابع VLOOKUP در اکسل

1. تابع VLOOKUP در حالت چپ به راست صفحه نمی‌تواند به سمت چپ ستون اول محدوده موردنظر شما و در حالت راست به چپ نمی‌تواند به سمت راست ستون اول محدوده موردنظر شما دسترسی پیدا کند. اگر نمی‌خواهید محدودیتی در این مورد داشته باشید، از ترکیب توابع INDEX و  MATCH استفاده کنید. (ترکیب این توابع در آموزش تابع INDEX توضیح داده شده است.)
2. تابع VLOOKUP به حروف بزرگ و کوچک حساس نیست.
3. در مورد آخرین آرگومان تابع VLOOKUP یعنی Rang_lookup به یاد داشته باشید که از مقدار TRUE برای مطابقت تقریبی و از مقدار FALSE برای مطابقت کامل استفاده کنید. هنگام جستجو بر اساس تطابق تقریبی (TRUE)، اطمینان حاصل کنید که داده‌های ستون جستجو به ترتیب صعودی طبقه‌بندی شده‌اند. اگر در تطابق کامل (FALSE) مقدار جستجو یافت نشد، تابع VLOOKUP خطای  N/A# را برمی‌گرداند.
4. از کاراکترهای Wildcard مثل  علامت * و علامت ؟ هم می‌توانید در تابع VLOOKUP استفاده کنید.

جمع‌بندی

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