آموزش فرمول vlookup در اکسل

در این قسمت از مطلب آموزشی قصد داریم به پاسخ این سوال برسیم: “فرمول Vlookup در اکسل چیست و چگونه اجرا می شود؟”
فرمول Vlookup یکی از محبوب ترین و کاربردی ترین توابع در اکسل است. Vlookup مخفف “Vertical Lookup” است که به معنای جستجوی داده ها و لیست کردن آن ها به صورت عمودی است. زمانی که داده های ما در یک شیت متمرکز نیست، تابع Vlookup این جستجو را برای ادغام اطلاعات پراکنده بر اساس یک مقدار منحصر به فرد، انجام می دهد.

در ابتدا بیایید به ساختار و آرگومان های این تابع نگاهی بیندازیم:

شاید در نگاه اول این تابع پیچیده به نظر برسد. اما با دنبال کردن مثال زیر به سادگیِ استفاده از این تابع پی خواهید برد.

داده های جدول زیر را در نظر بگیرید. در واقع دو شیت وجود دارد. Sheet1 شامل اطلاعات مربوط به اطلاعات عملکرد بازیکن های فوتبال است و Sheet2 حاوی داده های شخصی هر بازیکن است. در هر دو شیت ستون مشترکی به نام “شناسه بازیکن” وجود دارد.

به عنوان مثال با نگاه به Sheet1 می خواهیم بدانیم نام بازیکن با شناسه ۲۸۵۶۶ چیست. پس به Sheet2 میرویم این شناسه را پیدا می کنیم و می بینیم که جلوی آن چه اسمی نوشته شده است. این دقیقا همان کاری است که تابع Vlookup انجام می دهد.

در ادامه، یک راهنمای پنج مرحله ای برای انجام این مثال را مشاهده می کنید.

۱- درج تابع Vlookup در اکسل

یک ستون کنار ستون “شناسه بازیکن” اضافه می کنیم (تا فرمول Vlookup  نام بازیکن ها را در این ستون لیست کند). سپس همانطور که روش به کارگیری فرمول ها را در مقدمات فرمول نویسی آموختیم، برای دسترسی به تابع Vlookup در سلول C2 علامت = و سپس چند حرف از این تابع را تایپ می کنیم. سپس با نشان کردن تابع مورد نظر و فشردن کلید Tab فرمول را آماده دریافت آرگومان ها می کنیم.
همچنین می توانیم در تب Formulas، گروه Lookup & Reference را انتخاب و روی Vlookup کلیک کنیم. با این کار پنجره‌ای برای وارد کردن آرگومان ها ظاهر می شود.

۲- آرگومان اول (lookup_value)

اولین پارامتر در فرمول Vlookup مقدار مورد جستجو در جدول داده ها است. ما به این مقدار، مقدار معلوم می گوییم که در این مثال شناسه بازیکن اول است، پس خواهیم نوشت:

برای این آرگومان، هم می توانیم آدرس سلول را بدهیم و هم شناسه بازیکن را بنویسیم. توجه داشته باشید اگر مقدار مورد نظر به صورت متن باشد، باید حتما داخل دابل کوتیشن (“”) قرار گیرد.

۳- آرگومان دوم (table_array)

آرگومان دوم در فرمول Vlookup جدول یا منبع داده هایی است که باید در آن جستجوی عمودی انجام شود. پس باید جدول حاوی مقادیر مجهول یعنی اسامی بازیکن ها را برای جستجو ارائه دهیم. در این مثال جدول مورد نظر ما در برگه Sheet2 قرار دارد. پس ابتدا نام برگه ای که حاوی جدول است را می نویسیم. سپس علامت ! (Exclamation) را قرار می دهیم و بعد جدول را مشخص می کنیم.

توجه داشته باشید که اولین ستون جدول، ستونی باشد که مقادیر معلوم در آن تایپ شده است. با توجه به تصویر چون شناسه بازیکن ها در ستون B وارد شده است، باید جدول ما از ستون B شروع شود.

۴- آرگومان سوم (col_index_num)

بعد از مشخص کردن مقدار معلوم و جدول، فرمول می پرسد که مجهول ما کجاست؟ کافی است که جای ستون مجهول یعنی “نام بازیکن” را به آن بگوییم. یعنی بگوییم در این جدول یعنی (B:F) دومین ستون حاوی مقداری است که ما آنرا لازم داریم.

در این مثال ، پارامتر سوم ۲ است. این بدین معنی است که ستون دوم در جدول جایی است که ما مقدار برگشتی را پیدا خواهیم کرد. بنابراین باید بنویسیم:

۵- آرگومان چهارم (Range_Lookup)

چهارمین پارامتر تعیین می کند که آیا شما به دنبال تطابق دقیق هستید یا تطابق بازه ای. برای پیدا کردن تطابق دقیق (Exact Match)، صفر یا FALSE را وارد کنید. برای پیدا کردن تطابق بازه ای (Approximate Match) هر چیزی غیر از صفر یا TRUE را وارد کنید.
این پارامتر اختیاری است و اگر وارد نشود، به طور پیش فرض TRUE در نظر گرفته می شود.

بنابراین شکل نهایی تابع Vlookup به صورت زیر خواهد شد:

در این مثال، پارامتر چهارم FALSE است که به این معنی است که Vlookup به دنبال یک تطابق دقیق برای مقدار ۲۸۵۶۶ در Sheet1 و Sheet2 است.

در تصویر زیر نمای کلی آرگومان ها در جدول نمونه نمایش داده شده است.

در نهایت با فشردن کلید Enter مشخص می شود که نام بازیکن با شناسه ۲۸۵۶۶، واسیریکی است.

اکنون با دوبار کلیک کردن روی نقطه مشخص شده در تصویر بالا یا Drag کردن آن و استفاده از قابلیت Fill Sequence، می توانیم به سادگی اسامی تمام بازیکن ها را مشاهده کنیم.

خطاهای ممکن هنگام کار با فرمول Vlookup

اگر FALSE را برای آرگومان چهارم تعیین کنید و تطابق دقیقی پیدا نشود، مقدار N/A# برمی گردد.

اگر col_index_num کمتر از ۱ باشد، مقدار VALUE# برمی گردد.

اگر col_index_num از تعداد ستون های جدول بیشتر باشد، مقدار REF# باز خواهد گشت.