درباره نویسنده
جستجو و فراخوانی داده‌ها توسط تابع Vlookup
جستجو و فراخوانی جزء یک از مسائل حرفه‌ای و کاربردی در اکسل به شمار می‌رود. به همین دلیل یک سری از توابع در دسته Lookup & Reference به این موضوع اختصاص داده شده است. یکی از معروف‌ترین این توابع، تابع Vlookup می‌باشد. این تابع به منظور جستجو یک مقدار در یک محدوده و یافتن داده‌های متناظر با آن به کار برده می‌شود.
تابع Vlookup یا Vertical Lookup جزء توابع جستجو و مرجع می‌باشد که یک مقدار را در اولین ستون یک محدوده جستجو کرده و پس از پیدا کردن مقدار مورد نظر، مقدار سلول هم‌تراز با سلول پیدا شده در هر یک از ستون‌های مجاور با ستون مورد جستجو را به عنوان نتیجه برمی‌گرداند. به همین دلیل به آن Vertical Lookup هم گفته می‌شود، چون این تابع به جستجو در ستون اشاره می‌کند.
ساختار تابع
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])| آرگومان | شرح |
|---|---|
| lookup_value | اختصاص این آرگومان ضروری بوده و می‌تواند شامل مقدار یا آدرس سلول باشد. مقدار مورد نظر برای جستجو در این آرگومان تعیین می‌شود. |
| table_array | اختصاص این آرگومان ضروری بوده و می‌تواند شامل نام یا محدوده باشد. محدوده جستجو در این آرگومان تعیین می‌شود. |
| col_index_num | اختصاص این آرگومان ضروری بوده و شماره ستونی از آرگومان table_array می‌باشد که به عنوان نتیجه تابع برگردانده می‌شود. |
| range_lookup | اختصاص این آرگومان اختیاری بوده و یک مقدار منطقی است که جستجو تقریبی یا دقیق را تعیین می‌کند. |
آرگومان‌های تابع Vlookup
ویژگی‌های تابع
- آرگومان lookup_value باید در اولین ستون از آرگومان table _array وجود داشته باشد.
- اولین ستون از آرگومان table_array باید شامل آرگومان lookup_value باشد.
- آرگومان col_index_num با عدد 1 به عنوان اولین ستون آرگومان table_array شروع می‌شود.
- در آرگومان range_lookup مقدار True یا 1 جستجو تقریبی و مقدار صفر یا False جستجو دقیق را انجام می‌دهد.
- اگر آرگومان col_index_num کمتر از 1 باشد خطای #Value! و اگر بزرگتر از تعداد ستون‌های آرگومان table_array باشد خطای #Ref! به عنوان نتیجه تابع برگردانده می‌شود.
- پیش‌فرض آرگومان range_lookup مقدار True یا 1 می‌باشد.
- اگر آرگومان range_lookup دارای مقدار True باشد:
- ستون اول در آرگومان table_array باید به صورت صعودی مرتب شده باشد.
- اگر مقدار مورد نظر برای جستجو در محدوده جستجو وجود نداشت، بزرگترین مقدار که کوچکتر از مقدار مورد نظر برای جستجو است به عنوان نتیجه تابع برگردانده می‌شود.
- اگر مقدار مورد نظر برای جستجو در محدوده جستجو وجود داشت، بزرگترین مقدار که برابر یا کوچکتر از مقدار مورد نظر برای جستجو است به عنوان نتیجه تابع برگردانده می‌شود.
- اگر آرگومان range_lookup دارای مقدار False باشد:
- ستون اول در آرگومان table_array نیازی به ترتیب صعودی نداشته و در صورت وجود مقادیر تکراری، اولین مقدار فراخوانی شده به عنوان نتیجه تابع برگردانده می‌شود.
- اگر مقدار مورد نظر برای جستجو در محدوده جستجو وجود نداشت، خطای #N/A! به عنوان نتیجه تابع برگردانده می‌شود.
- در جستجو اعداد (تقریبی یا دقیق)، اگر آرگومان lookup_value کوچکتر از کوچکترین مقدار در اولین ستون از آرگومان table_array باشد، خطای #N/A! به عنوان نتیجه تابع برگردانده می‌شود.
تابع Vlookup مقدار مورد نظر برای جستجو را فقط در ستون اول محدوده جستجو یا table_array جستجو می‌کند.
بیشتر بخوانید: فرمول‌نویسی ترکیبی توسط تابع If (مقایسه چندگانه)
مثال
بیشتر اوقات ممکن است بخواهیم از یک بانک اطلاعاتی داده‌ای را جستجو کرده و فراخوانی کنیم. فرض کنید می‌خواهیم از یک بانک اطلاعاتی که شامل اطلاعات کارکنان یک سازمان می‌باشد با وارد کردن شماره پرسنلی هر فرد در سلول A13 داده‌هایی نظیر نام، نام خانوادگی، واحد سازمانی و تاریخ استخدام را جستجو کرده و در سلول‌های B13 تا E13 فراخوانی کنیم. تابع Vlookup را در سلول B13 به صورت زیر تعریف می کنیم.
=VLOOKUP(A13,A2:E11,2,0)
جستجو و فراخوانی اطلاعات کارکنان یک سازمان توسط تابع Vlookup
- آرگومان اول: مقداری است که به دنبال جستجو آن هستیم. بنابراین lookup_value برای این جستجو سلول A13 می‌باشد.
- آرگومان دوم: محدوده‌ای که جستجو در آن انجام می‌شود. بنابراین table_array برای این جستجو محدوده A2:E11 می‌باشد.
- آرگومان سوم: این آرگومان از جنس عدد بوده و تعیین می‌کند که چندمین ستون از محدوده جستجو را به عنوان نتیجه برگرداند. می‌خواهیم نام مربوط به هر کارمند را فراخوانی کنیم، پس باید ببینیم نام در چندمین ستون از محدوده جستجو قرار دارد. بنابراین col_index_num دومین ستون است.
- آرگومان چهارم: چون به دنبال جستجوی دقیق برای اطلاعات کارکنان هستیم، range_lookup را برابر با مقدار صفر یا False قرار می‌دهیم.
در سلول A13 شماره پرسنلی 4109 را وارد می‌کنیم. این فرمول عبارت نیما را به عنوان نتیجه برمی‌گرداند. برای نام خانوادگی، واحد سازمانی و تاریخ استخدام این فرمول را در سلول‌های C13 تا E13 تعریف می‌کنیم، با این تفاوت که مقدار آرگومان col_index_num را مطابق با جدول 2 تغییر می‌دهیم.
| ستون | آرگومان col_index_num | فرمول |
|---|---|---|
| نام خانوادگی | مقدار آرگومان col_index_number را برابر با شماره ستون نام خانوادگی در محدوده جستجو قرار می‌دهیم. | =VLOOKUP(A13,A2:E11,3,0) |
| واحد سازمانی | مقدار آرگومان col_index_number را برابر با شماره ستون واحد سازمانی در محدوده جستجو قرار می‌دهیم. | =VLOOKUP(A13,A2:E11,4,0) |
| تاریخ استخدام | مقدار آرگومان col_index_number را برابر با شماره ستون تاریخ استخدام در محدوده جستجو قرار می‌دهیم. | =VLOOKUP(A13,A2:E11,5,0) |
مقدار آرگومان col_index_num برای نام خانوادگی، واحد سازمانی و تاریخ استخدام
گاهی اوقات ممکن است اعداد در تابع Vlookup فراخوانی نشده و باعث ایجاد خطا #N/A! در خروجی فرمول شوند. یکی از علت‌های آن جنس اعداد می‌باشد که به رشته تبدیل شده‌اند. بنابراین می‌توانیم اعدادی که از جنس عدد نیستند را در عدد 1 ضرب کرده تا به عدد تبدیل شده و در این تابع فراخوانی شوند.
