ویلوکاپ در اکسل چیست؟ – نحوه استفاده و کار با فرمول VLOOKUP

اکسل توابع زیادی را در اختیار کاربر قرار می‌دهد تا کار با انواع داده را در هر حجمی آسان‌تر کند. ویلوکاپ یکی از این توابع است. این تابع برای بازیابی و جستجوی داده‌ها در اکسل استفاده می‌شود. در ادامه به معرفی تابع ویلوکاپ در اکسل نحوه استفاده از آن، به تنهایی و در کنار سایر توابع، می‌پردازیم. در ابتدای این مطلب از مجله فرادرس، سینتکس تابع ویلوکاپ و عملکرد آن را توضیح می‌دهیم. در ادامه، روش‌های استفاده از تابع را برای مطابقت دقیق و تقریبی آموزش می‌دهیم و در انتها نیز جستجوی دوسویه و چندشرطی با به کمک این تابع بیان می‌کنیم.

سینتکس تابع ویلوکاپ در اکسل

ویلوکاپ (VLOOKUP) از محبوب‌ترین توابع اکسل است. این تابع داده‌ها را اسکن کرده و مقدار نظیر مد نظر شما را پیدا می‌کند. عملکرد ساده این تابع باعث شده در اکثر پروژه‌های اکسل کاربردی باشد. در فرمول زیر، سینتکس تابع ویلوکاپ در اکسل را می‌بینیم:

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

1. lookup_value آرگومان اول مقداری است که قصد جستجوی آن را در جدول دارید.
2. table_array آرگومان دوم آدرس سلول‌هایی (جدولی) که قصد جستجو در آن‌ها را دارید مشخص می‌کند.
3. col_index_number آرگومان سوم شماره ستونی از جدول (آرگومان قبلی) است که قصد برگرداندن داده از آن را دارید.
4. [range_lookup] آرگومان چهارم دقت جستجو را تعیین می‌کند اگر این آرگومان در حالت «False» قرار بگیرد تابع دقیقاً به جستجوی داده‌ای می‌پردازد که در آرگومان اول مشخص کرده‌اید. اگر این آرگومان در حالت «True» قرار بگیرد نتیجه تابع مقادیر مشابه آرگومان اول خواهد بود. پیش فرض تابع ویلوکاپ برای این آرگومان، «True» یا جستجو با تطابق تقریبی است.

توضیح عملکرد تابع ویلوکاپ در اکسل با مثال

تابع VLOOKUP در اکسل جستجوی ستونی انجام می‌دهد. حرف «V» در ابتدای اسم تابع به معنی عمودی (Vertical) است.

برای مثال در جدول بالا می‌توانید از ویلوکاپ برای پیدا کردن یک داده، مشابه تصویر زیر، استفاده کنید.

برای مقدار جستجوی «1005» خروجی فرمول «125» دلار خواهد بود. تابع ویلوکاپ به صورت ستونی، از ستون B به دنبال مقدار جستجو (آرگومان اول) می‌گردد و داده نظیر آن را در سومین ستون می‌یابد.

=VLOOKUP(I4,B5:F9,3,FALSE)

طبق فرمول مقدار جستجو در سلول I4 مشخص شده، اگر محتوای این سلول تغییر کند، مقدار جستجو نیز تغییر خواهد کرد. در فرمول سلول B5 تا F9 برای جستجو انتخاب شده، که در این مثال کل جدول را شامل می‌شود. تابع در ستون اول مقدار جستجوی «105» را می‌یابد و در ستون سوم سلول نظیر آن را با محتوای 125، به عنوان خروجی، در سلول I5 نمایش می‌دهد.

همانطور که گفتیم تابع ویلوکاپ در اکسل جستجوی ستونی انجام می‌دهد. در جدولی که قصد جستجو در آن را دارید (آرگومان دوم)، شماره‌ای برای ستون‌ها از سمت چپ به راست، به ترتیب در نظر بگیرید. از این شماره برای آدرس دهی ستونی که قصد بازگرداندن نتیجه جستجو از آن را دارید، در آرگومان سوم تابع، استفاده کنید. برای مثال، طبق فرمول‌های زیر می‌توانیم نام، نام خانوادگی و ایمیل کاربر «622» را به صورت جداگانه بیابیم.

=VLOOKUP(H3,B4:E13,2,FALSE) // first name
=VLOOKUP(H3,B4:E13,3,FALSE) // last name
=VLOOKUP(H3,B4:E13,4,FALSE) // email address

تنها فرق این فرمول‌های بالا، در شماره ستونی است که در آرگومان سوم آمده است.

تابع ویلوکاپ جستجو را به طور پیش فرض از اولین ستون سمت چپ آغاز کرده به سمت راست می‌رود. البته دقت کنید که اگر جهت جدول را در «Page Layout» و «Sheet Options» از راست به چپ تنظیم کرده باشید، جستجو از اولین ستون سمت راست شروع شده و به سمت چپ می‌رود.

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

تابع ویلوکاپ و جستجو با مطابقت دقیق

همونطور که در سینتکس تابع توضیح دادیم، آرگومان چهارم دقت جستجو را تعیین می‌کند. اگر آرگومان چهارم در مقدار False قرار گیرد تابع ویلوکاپ با مطابقت دقیق به جستجو می‌پردازد.

در مثال زیر فرمول در سلول H6 سال تولید را بر اساس نام دقیق فیلم پیدا کرده است.

=VLOOKUP(H4,B5:E9,2,FALSE)

تصویر زیر، این مثال را به‌خوبی نشان می‌دهد.

 

تابع ویلوکاپ و جستجو با مطابقت تقریبی

اگر آرگومان چهارم تابع ویلوکاپ مقدار True داشته باشد، تابع ویلوکاپ با مطابقت تقریبی به جستجو خواهد پرداخت. به عنوان مثال تقسیم بندی نمرات را با حروف A تا F در نظر بگیرید. در تصویر زیر نمرات موجود (در ستون C) را، با استفاده از جدول «key» (در ستون F و G)، دسته بندی می‌کنیم. از جایی که نمرات موجود در ستون C با دسته‌بندی‌های جدول Key دقیقاً یکسان نیستند، نیاز داریم از تابعی استفاده کنیم تا نمرات ستون C را به طور تقریبی با حروف ستون F تطبیق دهد. تابع ویلوکاپ با آرگومان چهارم True، دقیقاً همین کار را انجام می‌دهد. فرمول زیر را در ستون D، روبروی هر یک از نمرات ستون C تکرار می‌کنیم. دقت کنید که آرگومان اول آدرس سلولی است که قصد طبقه‌بندی نمره آن را داریم بنابراین آرگومان اول از سلول C5 تا C16 تغییر می‌کند.

=VLOOKUP(C5,$G$5:$H$10,2,TRUE)

تصویر زیر این مثال را به‌خوبی نشان می‌دهد.

در این مثال اگر تابع ویلوکاپ مقدار دقیق نمره از ستون C را در ستون F پیدا کند، حاصل تابع مقدار متناظر در ستون G خواهد بود. اما اگر نمره را در ستون F پیدا نکند، در اولین سلولی که نمره بیشتری دارد متوقف می‌شود، به سلول قبلی برمی‌گردد و خروجی تابع مقدار متناظر با سلول قبلی خواهد بود. مثلا برای نمره 75، در ستون F از ردیف پنجم به پایین پیش می‌رود تا به اولین عدد بزرگتر از 75 برسد، سپس یک خانه به عقب می‌آید و خروجی تابع مقدار متناظر با سلول F7=70 در ستون G، برابر با نمره «C» خواهد بود. دقت کنید برای استفاده از تابع لوکاپ در جستجوی تقریبی، داده‌های موجود در آرگومان دوم باید به ترتیب صعودی مرتب شده باشند. در غیر صورت تابع احتمالا به درستی کار نخواهد کرد.

حالت پیش فرض تابع ویلوکاپ در اکسل

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

=VLOOKUP(F5,B5:D10,3)

همانطور که می‌بینید مقدار 100235 در جدول وجود ندارد بنابراین لوکاپ سلول ها را اسکن می‌کند تا به اولین عدد بزرگ‌تر از 100235 برسد که این مقدار در جدول عدد ۱۰۰۲۳۶ است سپس یک پله به عقب می‌رود و در نتیجه به اشتباه مقدار 12000 دلار را برمی‌گرداند.

برای جلوگیری از این اشتباه دقت کنید که آرگومان چهارم را در حالت False یا 0 قرار دهید.

=VLOOKUP(F5,B5:D10,3,FALSE)

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

وجود دو نتیجه ممکن برای تابع ویلوکاپ

اگر بیشتر از یک جواب برای تابع ویلوکاپ وجود داشته باشد، تابع اولین یافته را نمایش می‌دهد. برای مثال در تصویر زیر تابع ویلوکاپ باید قیمت رنگ «Green» را پیدا کند در جدول Green دو بار تکرار شده است، در نتیجه تابع ویلوکاپ در این مثال، قیمت متناظر اولین Green یافت شده از بالای ستون را نمایش می‌دهد. فرمول این عملیات در سلول F5 به شرح زیر است:

=VLOOKUP(E5,B5:C11,2,FALSE)

اگر در چنین مثالی قصد یافتن آخرین Green را داشته باشید، باید از تابع «XLOOKUP» استفاده کنید و برای یافتن قیمت تمامی Greenها، تابع «FILTER» استفاده می‌شود.

جستجوی وایلد کارت (Wildcard) با تابع ویلوکاپ

ویلوکاپ از جستجوی وایلد کارت اکسل پشتیبانی می‌کند به این معنا که این تابع توانایی اجرای جستجوی جزئی را دارد. برای استفاده از وایلد کارت در تابع ویلوکاپ، باید آرگومان چهارم روی False یا 0 تنظیم شده باشد. در مثال زیر بعد از اینکه در سلول H4 عبارت «Aya» تایپ شود، ویلوکاپ در سلول H7‌، نام «Michael» را برمی‌گرداند.

در فرمول زیر به جایگاه «*» و «&» دقت کنید.

=VLOOKUP($H$4&"*",$B$5:$E$104,2,FALSE)

جستجوی دوسویه با تابع ویلوکاپ در اکسل

در تابع ویلوکاپ آرگومان سوم معمولا به صورت یک مقدار عددی ثابت نوشته می‌شود. هرچند می‌توانید برای این آرگومان یک بازه عددی متغیر در نظر بگیرید و تابع «Match» استفاده کنید. این ترفند به شما اجازه می‌دهد یک جستجوی دوسویه پویا خلق کنید، که هم در سطر و هم در ستون به جستجو بپردازد. در مثال زیر تابع لوکاپ برای جستجوی نام، به صورت زیر نوشته شده است:

=VLOOKUP(H4,B5:E13,MATCH(H5,B4:E4,0),0)

تابع Match ماه «Feb» را در بازه B4 تا E4 پیدا می‌کند و عدد 3 را به تابع ویلوکاپ به عنوان آرگومان سوم باز می‌گرداند، چراکه «Feb» در ستون سوم جدول قرار دارد.

جستجوی چند شرطی با تابع ویلوکاپ در اکسل

تابع ویلوکاپ به طور پیش فرض، توانایی استفاده از چند شرط در جستجو را ندارد. هرچند با استفاده از یک ستون کمکی می‌توانید بخش‌های مختلف را به هم ارتباط دهید و از آن‌ها به عنوان چند شرط جستجو در ویلوکاپ استفاده کنید. در مثال زیر ستون «B1» ستون کمکی است که و نام خانوادگی شخص را در کنار یکدیگر به عنوان شرط جستجو در فرمول اعمال می‌کند.

=VLOOKUP(H4&H5,B5:E13,4,0)

ارور #N/A و تابع ویلوکاپ

در استفاده از تابع ویلوکاپ در اکسل گاهی با خطای #N/A مواجه خواهید شد. خطا به این معنی است که عبارت نظر یافت نشده است. در مثال زیر عبارت «Toy Story 2» در جدول وجود ندارد. در نتیجه تابع ویلوکاپ خطای #N/A را باز می‌گرداند.

#N/A خطای کارآمدی است، چراکه به شما می‌گوید در داده‌ها نقصی وجود دارد. این خطا می‌تواند در حالات مختلف زیر ایجاد شود:

• مقدار جستجو شده در جدول وجود ندارد.
• مقدار جستجو شده (آرگومان اول فرمول)، خطای تایپی دارد.
• قصد جستجوی دقیق را داشتید، اما فرمول در حالت تقریبی قرار گرفته است.
• آدرس جدول در آرگومان دوم به درستی تعریف نشده است.
• فرمول در سلول جدیدی کپی شده است، در صورتی که آرگومان‌ها برای سلول جدید به درستی تغییر نکردند.

اگر مایلید به جای خطای #N/A عبارت دلخواه دیگری نمایش داده شود، می‌توانید از تابع «IFNA» استفاده کنید.

در مثال بالا فرمول به شرح زیر خواهد بود:

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"Not found")

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

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"") 

در مثال بالا از مجله فرادرس، اگر خطای #N/A را داشته باشیم،‌ سلول موردنظر، خطا را نمایش نمی‌دهد و سلول خالی داریم.