چگونه از تابع VLOOKUP در اکسل استفاده کنیم؟ + مثال

تابع VLOOKUP در اکسل: فرض کنید هزاران ردیف داده از حقوق و پست سازمانی کارمندان یک شرکت را در اختیار دارید و رییس سازمان شما به طور ناگهانی، از شما اطلاعاتی در خصوص حقوق و پست کارمندی با نام آقا/خانم اکبری را می خواهد. یا بدتر از این، او لیستی از صد کارمند را به شما می دهد و از شما می خواهد مشخصات این کارمندان را به ریز به او تحویل دهید.

شما چه می کنید؟ به هیچ وجه این امکان برای شما وجود ندارد که بخواهید دستی این کار را انجام دهید. نه تنها ممکن نیست، بلکه هوشمندانه هم نیست. اما اگر با تابع VLOOKUP آشنا باشید، حتماً هوشمندانه کار می کنید. اگر می خواهید بیشتر با توابع در اکسل آشنا شوید، می توانید به دوره آموزشی فرمولها و توابع در اکسل مراجعه کنید.

چرا ما به تابع VLOOKUP در اکسل نیاز داریم؟

VLOOKUP چیست؟ VLOOKUP مخفف Vertical Lookup است.

تابع VLOOKUP در اکسل، عمل جستجو کردن را برای ما انجام می دهد. در واقع این تابع در اولین ستون یک جدول، به دنبال ردیفِ مقدارِ داده شده می گردد و نهایتاً در آن ردیف، مقدار موجود در ستونی که مدنظر ما است را به ما باز می گرداند.

سینتکس نوشتاری تابع VLOOKUP به این صورت است:

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

Lookup_value: مقداری است که می خواهید در ستون اولِ جدول، بودن یا نبودن آنرا بررسی کنید و جستجو را براساس آن انجام دهید.

Table_array: جدولی است انتخابی که می خواهید در آن، کارِ جستجو را انجام دهید. 

Col_index_number: شماره ستونی از آن جدول، که می خواهید نتایج از آن واکشی شود. 

[range_lookup]: برای این پارامتر، دو مقدار FALSE و TRUE مناسب است. اگر بخواهید دقیقاً مقدار موردنظرتان را جستجو کند، گزینه FALSE و اگر بخواهید به صورت تقریبی این جستجو انجام شود، گزینه TRUE را انتخاب می کنیم. 

مثال اول VLOOKUP:

در یک کاربرگ اکسل، داده های برخی کارمندان را جمع آوری کرده ایم. در ستون اول (ستون A)، نام کارمندان، در ستون دوم پست و سمت آنها و در دو ستون باقی مانده نیز تاریخ استخدام و میزان حقوق و مزایای آنها را می نویسیم.

رییس سازمان شما، لیست زیر را به شما داده و از شما خواسته اطلاعات این دو کارمند را کامل کنید و در اسرع وقت برای ایشان ارسال کنید:

در این درخواست، گفته شده که سمت و میزان حقوق آقا/خانم اکبری و مرادی را بنویسید. شما برای جستجوی این مقادیر چه می کنید؟ هم می توانید به صورت دستی جستجو کنید و خودتان را خسته کنید و یا اینکه از ویژگی قدرتمند VLOOKUP استفاده کنید.

برای استفاده از VLOOKUP تنها کافی است که این فرمول را به این صورت در سلول H2 بنویسید:

VLOOKUP(G3,$A$3:$D$11,۴,FALSE)=

نتیجه کار به این صورت خواهد شد:

توضیحات مثال:

در اینجا ما به اکسل گفتیم که در ستونِ اول (A) جدول نشان داده شده، به دنبال نام اکبری (G3) بگردد. دقت کنید برای انتخاب جدول به شکل زیر عمل می کنیم:

در صورتی که نام اکبری پیدا شد، اکسل مقدار متناظر این نام را از همان ردیف و از ستون چهارم (۴) به ما برمی گرداند.

در فرمول بالا ما از گزینه FALSE استفاده کردیم و این بدان معنی است که ما میخواهیم دقیقاً همین عبارت را برای ما پیدا کند.

در این مثال، اکسل نام “اکبری” را از ستون A برای ما پیدا می کند و میزان حقوق و مزایای او را به ما برمی گرداند.

همین فرآیند را برای یافتن نام “ایران طلب” انجام می دهیم. چون اکسل این نام را در جدول پیدا نمی کند، خطای N/A# را باز می گرداند.

نکته ای که اینجا باید به آن پی برده باشید، کاربرد دیگری از تابع VLOOKUP است و آن این است که تابع VLOOKUP به ما کمک می کند تا متوجه شویم مقدار داده شده در جدول وجود دارد یا خیر.

یعنی حتی از این تابع می توان برای مواقعی استفاده کرد که می خواهیم بدانیم این مقدار در جدول ما وجود دارد یا خیر.

در این مثال، ما از پارامتر FALSE استفاده کردیم، برای اینکه می خواستیم دقیقاً آن عبارت را برای ما جستجو کند. اما اگر بخواهیم مقدار تقریبی را به ما بدهد چه کنیم؟ چه زمانی می توان از امکان Approximate Match در VLOOKUP استفاده کرد؟

با همدیگر این مثال را بررسی می کنیم:

مثال دوم: استفاده از Approximate Match در VLOOKUP

در مثال قبل، توانستیم به درستی به درخواست رییس سازمان پاسخ دهیم. اما دوباره از شما می خواهد که نزدیک ترین حقوق به ۲۵,۰۰۰,۰۰۰ ریال را پیدا کند (فقط حقوق های پایین تر از این رقم).

برای پاسخ به این سوال، مجدداً از تابع VLOOKUP استفاده می کنیم، اما این بار باید از ویژگی مقدار تقریبی کمک بگیریم.

نکته مهم: برای یافتن مقدار تقریبی (Approximate Match)، باید در ابتدا داده ها را مرتب (Sort) کنید. در اینجا ما داده ها را براساس ستون “حقوق” از کم به زیاد، مرتب می کنیم.

در سلول H8 این فرمول را بنویسید و آنرا به سلول زیرین هم بسط دهید:

VLOOKUP(G8,$D$3:$D$11,۱,۱)=

نکته: در اکسل، عدد یک (۱) به عنوان TRUE تفسیر می شود و عدد صفر (۰) به عنوان FALSE. 

در نهایت نتیجه کار به این صورت خواهد شد:

توضیحات مثال:

مقداری که می خواهیم تابع VLOOKUP به عنوان یک مرجع برای جستجو در نظر بگیرد، ۲۵,۰۰۰,۰۰۰ (G8) است؛ و ما می خواهیم نزدیکترین حقوق را به این عدد برای ما پیدا کند.

اما محدوده مدنظر ما تغییر کرده است، خوب دقت کنید:

به این دلیل که می خواهیم از همان ستون، جواب را به ما برگرداند. بنابراین، مقدار col_index_num نیز برابر ۱ می شود. و از آنجایی که ما به دنبال یک مقدار تقریبی هستیم، مقدار ۱ (TRUE) را برای range_lookup انتخاب می کنیم.

دقت داشته باشید که برای کار با approximate match حتماً باید داده های خود را مرتب کنید. در غیر این صورت، تابع VLOOKUP نتایج اشتباهی را به شما نشان خواهد داد.

نکات مهم:

• تابع VLOOKUP همیشه از چپ به راست کار می کند. هیچگاه نمی توان مقداری را از سمت راست ستون اول، واکشی کرد. 
• شماره گذاری ستون از محدوده انتخاب شروع می شود. به عنوان مثال، اگر آرایه جدولی (Table Array) شما برای تابع VLOOKUP به صورت D4:F10 باشد، شمارش ستون با D شروع می شود، نه از نقطه شروع واقعی جدول.
• همیشه با استفاده از علامت $، جدول را قفل کنید تا هنگام کپی کردن فرمول VLOOKUP خطایی رخ ندهد.
• اگر تنها بخشی از مقدار جستجو را به خاطر دارید، می توانید از علامت ستاره (*) و در مقدار جستجو استفاده کنید.  
• اگر می خواهید دقیقاً یک مقدار را جستجو کنید، از مقدار FALSE (0) برای Range_Lookup استفاده کنید.
• اگر می خواهید تابع VLOOKUP نزدیکترین مقدار از پایین را به برای شما جستجو کند، از مقدار TRUE(1) برای Range_Lookup استفاده کنید.
• برای کار با مقدار تقریبی (approximate match)، حتماً داده های خود را به صورت نزولی مرتب کنید. 

الان شما می توانید در کسری از ثانیه، پاسخ مدیر خود را بدهید. می توانید دو لیست را به راحتی با یکدیگر مقایسه کنید.

نظرات و پیشنهادات خودتان را در بخش دیدگاه ها برای ما ارسال کنید.