تابع VLOOKUP جزو توابع جستوجوی اکسل است. این تابع یک مقدار را از اولین ستون یک جدول جستوجو میکند و جواب مربوطه را از یک ستون معین دیگر برمیگرداند.
◄ Vlookup معادل Vertical lookup به معنای جستوجوی عمودی است.
در ادامهی این مطلب به طور مفصل با جزئیات و کاربرد تابع Vlookup در اکسل آشنا خواهیم شد.
آرگومانها یا پارامترهای ورودی
آرگومان اول (lookup_value): مقدار یا ارزشی که قرار است جستوجو شود و میتواند به صورت عدد، متن، تاریخ، آدرس سلول و … باشد.
آرگومان دوم (table_array): این آرگومان بیانگر محل جستوجو است و باید بصورت یک جدول باشد که حکم دیتابیس را دارد. مقدار آرگومان اول از درون این جدول جستوجو میشود.
آرگومان سوم (col_index_num): در این آرگومان باید عدد بنویسید. این عدد بیانگر شماره ستونی است که میخواهید جواب از آن ستون نمایش داده شود.
آرگومان چهارم ([range_lookup]): این آرگومان اختیاری و تعیین میکند تابع در صورتی که مقدار دقیق آرگومان اول (lookup_value) را نیافت، چه چیزی را برگرداند. این آرگومان دو گزینه دارد و میتواند TRUE یا FALSE باشد:
- TRUE: با انتخاب این گزینه تطبیق تقریبی انجام میشود؛ یعنی در صورتی که مقدار دقیق آرگومان اول (lookup_value) از اولین ستون جدول یافت نشد، اکسل نزدیکترین حد پایین مقدار مورد نظر را نمایش میدهد (به جای واژه True از عدد یک نیز میتوانید استفاده کنید).
توجه: در این حالت حتماً باید ستون اول جدول صعودی باشد.
- FALSE: با انتخاب این گزینه تطبیق کامل انجام میشود، یعنی در صورتی که مقدار دقیق آرگومان اول (lookup_value) از اولین ستون جدول یافت نشد، دیگر بحث تقریب در کار نیست و تابع یک خطا برمیگرداند (به جای واژه False از عدد صفر نیز میتوانید استفاده کنید).
نمایی از Vlookup به صورت شماتیک
مثالها
پیدا کردن نام براساس کد پرسنلی
با توجه به شکل زیر میخواهیم فرمولی در سلول I2 بنویسیم که براساس کد پرسنلی واردشده در سلول H2، نام شخص مربوطه را بازگرداند.
فرمول سلول I2:
=VLOOKUP( H2 , C1:F5 , 2 , 0 )
ترجمهی فارسی تابع فوق:
اکسل برو محتویات سلول H2 را (که در آن مقدار 1201 وجود دارد) بگرد از اولین ستون جدول ناحیه C1:F5 و جواب را از دومین ستون این جدول برای ما نمایش بده!
زمانی که مقدار مورد جستجو از اولین ستون یافت شد اکسل متناظر با این مقدار را از ستون دوم برای ما نمایش میدهد.
اگر همین فرمول را برای بدست آوردن سمت بنویسیم به صورت زیر خواهد بود:
=VLOOKUP( H2 , C1:F5 , 3 , 0 )
توجه: سایر مثالهای تابع Vlookup را میتوانید در انتهای صفحه دانلود نمایید.
سوال: از بین سه تابع Lookup، Vlookup، Hlookup چرا تابع Vlookup از همه مهمتر است و بیشترین استفاده را دارد؟
همانطور که در قسمت بالا بیان شد تابع Vlookup به معنای جستوجوی عمودی میباشد. منظور از عمودی یا افقی بودن، نوع طراحی دیتابیس است. معمولاً در اکثر مواقع دیتابیسها یا جداول داده بصورت عمودی طراحی میشوند به همین خاطر ما مجبور به استفاده از تابع Vlookup هستیم.
برای آشنایی بیشتر با تابع Hlookup اینجا کلیک کنید.
نکات مهم تابع
نکته 1) Vlookup از اولین ستون جدول داده (نه اولین ستون اکسل) کار جستوجو را انجام میدهد.
همانطور که در شکل بالا میبینید اکسل ستون C را به عنوان اولین ستون برای Vlookup درنظر میگیرد نه ستون A.
نکته 2) Vlookup مقدار تکراری را پیدا نمیکند؛ در صورت تکراری بودن، اولین مقدار را نمایش میدهد.
همانطور که در شکل بالا میبینید کد پرسنلی 1201 در ستون اول (ستون C) تکرار شده است بنابراین انتظار داریم اکسل هنگام جستوجوی 1201، اولین مقدار را که مربوط به شخص اول (علی) است برای ما نمایش دهد نه شخص بعدی (احمد).
پس توجه داشته باشید از این تابع برای زمانی استفاده کنید که عبارت مورد جستوجوی شما یونیک یا به عبارت دیگر منحصربهفرد باشد مانند: کد پرسنلی، کد ملی، کد محصول و … .
نکته 3) بزرگ یا کوچک بودن حروف در جستوجوی این تابع مهم نیستند. مانند: “Reza” = “reza”
نکته 4) تابع Vlookup از وایلدکاردها (Wildcard) نیز پشتیبانی میکند. مانند جستوجوی واژه “محمد*” به جای “محمد”.
نکته 5) درصورتی که آرگومان آخر را وارد نکنید و تابع را با 3 آرگومان بنویسید، اکسل با فرض True بودن آرگومان آخر تابع Vlookup را اجرا میکند، پس توجه داشتە باشید در اکثر مواقع آرگومان آخر عدد صفر یا واژه False نوشته شود.
نکته 6) اگر عبارتی که میخواهید جستوجو کنید به صورت عددی باشد، توجه داشته باشید آن را داخل دابلکوتیشن ننویسید.
زمانی که عددی داخل دابل کوتیشن نوشته میشود، آن عدد حالت متنی به خود میگیرد و از حالت عدد خارج میشود. در شکل بالا حالت الف درست است و حالت ب اشتباه میباشد.
نکته 7) درصورتی که جدول اکسل شما خروجی یک نرم افزار مانند همکاران سیستم و … باشد به حروف “ی” و “ک” توجه کنید؛ معمولاً در خروجی نرمافزارها این حروف به صورت عربی نوشته میشود بخاطر همین تابع Vlookup نمیتواند عبارت مورد جستوجو را بیابد.
یکی از مشکلاتی که کاربران فارسی زبان در اوایل شروع استفاده از کامپیوترهای شخصی داشتند، نبود فونت فارسی روی سیستمها و به طور کلی قابل استفاده نبودن خط فارسی بود. اولین بار بعضیها اقدام به فارسیسازی با استفاده از فونتهای عربی نمودند و به همین دلیل اکثر قریب به اتفاق سیستمها و برنامهها برگردان از عربی بود… .
یکی از تفاوتهای خط عربی با فارسی نداشتن چهار حرف ( گ چ پ ژ ) در عربی است و همه این چهار حرف هم در آن موقع به خط فارسی اضافه شد ولی کسانی که این کار را انجام میدادند گاهی اوقات فراموش میکردند که خط عربی دو تفاوت دیگر هم با خط فارسی دارد و آن در حروف ( ی و ک ) است. ی و ک عربی به این صورت است: ي و ك
برای مشاهده ویدئوی ي و ك عربی اینجا کلیک کنید.
نکته 8) Vlookup فقط رو به جلو جستوجو میکند و رو به عقب نمیتواند عملیات جستوجو را انجام دهد.
جستوجو رو به جلو
در حالت جستوجو رو به جلو، جهت شیت اکسل با جهت جدول داده هم راستا است. بنابراین تابع Vlookup میتواند عملیات جستوجو را از اولین ستون (ستون C) انجام دهد. چون محتویات ستون C با مقداری که میخواهد جستوجو شود (H2) همجنس است، بنابراین عملیات جستوجو به راحتی انجام میشود.
جستوجو رو به عقب
در حالت جستوجو رو به عقب، جهت شیت اکسل با جهت جدول داده هم راستا نیست. بنابراین تابع Vlookup عملیات جستوجو را از اولین ستون جدول که ستون F است انجام میدهد و چون محتویات این ستون از جنس حقوق میباشد و مقداری که قرار است جستوجو شود از جنس کد پرسنلی است، اکسل خطای N/A# را نمایش میدهد، یعنی موجود نیست (Not Available).
خطاهای تابع
خطای N/A#: این خطا به معنای موجود نبودن (Not Available) یا پیدا نکردن است و زمانی نمایش داده میشود که مقداری که در حال جستوجوی آن هستیم، موجود نباشد.
در شکل بالا چون مقدار 1205 در جدول داده ها یافت نشد، تابع Vlookup خطای N/A# را نمایش میدهد.
خطای !REF#: این خطا به معنای اشتباه وارد شدن رفرنس (Reference) میباشد و زمانی نمایش داده میشود که آرگومان سوم تابع Vlookup عددی بزرگتر از تعداد ستونهای جدول داده باشد.
در شکل بالا چون عددی که در آرگومان سوم (عدد 5) وارد شده بزرگتر تعداد ستونهای جدول (4 ستون) است، Vlookup خطای رفرنس را نمایش میدهد.
خطای !VALUE#: این خطا برای زمانی است که در آرگومان سوم عددی منفی وارد کنید.
دانلود فایل
مشاهدهی ویدئو
آموزش ویدئویی تابع Vlookup را در زیر میتوانید مشاهده کنید. در صورتی که مایل به تماشای ویدئو نیستید، آموزش کامل نوشتاری در پایین آمده است.
ویدئوهای آموزشی زیر را نیز میتوانید در صورت تمایل در کانال آپارات ما مشاهده کنید:
ممنون
ویدئوی خوبی بود فقط اگر بشه چند تا مثال دیگه بهش اضافه بشه عالیه
عالی بود استاد مثل همیشه واضح و روشن ممنون
slm
aliiiiiiiiiiiiiiiii boooood
mamnoon
سلام بر ادمین سایت اکسل یوتی
فوق العاده کاربردی بود مطلب و بسیار ساده و زیبا بیان شد
ممنون از سایت خوبتون
شما گروه تلگرامی ندارید؟
Make a more new posts please!
عالی بود تا حالا اینقد VLOOKUP رو کامل یجا ندیده بودم
ممنون از سایت خوبتون
سلام. در مورد vlookup خیلی سرچ کردم و از خیلی سایت ها در موردش مطلب خوندم ولی هیچ کدوم به اندازه سایت شما کامل و جامع نبود تازه فهمیدم که vlookup چیه. واقعا ممنونم از سایت خوبتون
با تشکر از مطالب مفید سایت
سلام واقعا عالی و کاربردی بود . فقط سوال اینکه اگر جستجو برای داده های تکراریی باشه از چه ترکیبی باید استفاده کرد؟
خیلی عالی و کاربردی دمتون گرم عجب سایتی دارین. تازه امروز vlookup رو درک کردم مرسیییییییی
عالییییییییی بود
از توضیحات کامل و نکات مهمی که مطرح کردید ممنونم
ممنون واقعا کامل و گویا بود
عالی بود خدا خیرت بده کلی کارم راحت شد
با تشکر از زحمات ما
اگه امکان داره در مورد پیوت تیبل ،توضیحاتی ارایه فرمایید
سلام. در لینکهای زیر میتوانید در مورد پیوتتیبل بیشتر بدایند:
https://excelut.com/معرفی-پیوت-تیبل/
https://excelut.com/category/پیوت-تیبل/
عااااااااااالی
من واقعاً نمیدانم به چه زبانی از شما تشکر کنم. بسیار عالی، منظم و دقیق تمام مبحثها را توضیح میدهید. امیدوارم موفق باشید.
سلام . عالی بود و کامل و کاربری . خیلی ممنون از شما
عالی
بسیار عالی بود استاد توضیح کامل و روان و کاربردی
موفق و پیروزباشید
عالی بود
ممنون از سایت خوبتون
خواهش میکنم موفق باشید
خدا قوت
بسیار ممنون