تابع VLOOKUP [با آموزش ویدئویی و دانلود فایل]

تابع 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 را در زیر می‌توانید مشاهده کنید. در صورتی که مایل به تماشای ویدئو نیستید، آموزش کامل نوشتاری در پایین آمده است.

ویدئوهای آموزشی زیر را نیز می‌توانید در صورت تمایل در کانال آپارات ما مشاهده کنید:

• نحوه‌ی استفاده از Wildcards در تابع VLOOKUP

• جست‌وجوی براساس دو شرط با استفاده از تابع VLOOKUP

• داینامیک کردن آرگومان سوم تابع VLOOKUP با ترکیب MATCH

• ترفند استفاده از تابع VLOOKUP در دیتابیس‌های بسیار بزرگ