آموزش گام به گام تابع VLOOKUP در اکسل | از ساختار تا نکات طلایی

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

تابع VLOOKUP در اکسل چیست؟

به زبان ساده می توانیم بگوییم که تابع VLOOKUP داده مورد نظر ما را در ناحیه ای که برای آن مشخص می کنیم یافته و داده متناظر (هم ردیف) با آن را در ستونی که مشخص کرده ایم، به عنوان نتیجه نمایش می دهد.

نحوه استفاده از تابع VLOOKUP

ساختار تابع به صورت زیر است:

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

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

• lookup_value: مقداری که قصد جستجوی آن را دارید.
• table_array: جدول داده ها که جستجو در آن انجام می شود. داده ای که قصد جستجوی آن را دارید باید در ستون اول این ناحیه باشد.
• col_index_num: شماره ستونی که باید مقدار نهایی از آن خوانده شود (شماره گذاری از 1)
• range_lookup: مقدار TRUE برای تطبیق تقریبی و FALSE برای تطبیق دقیق (اختیاری)

مثال کاربردی استفاده از تابع VLOOKUP

همان طور که در مثال زیر مشاهده می کنید، در یک جدول، لیستی از محصولات به همراه کد، تعداد فروش و مبلغ فروش آن ها را آورده ایم.

حال فرض کنید می خواهیم مبلغ فروش یک کد محصول و تعداد فروش یک نام محصول را پیدا کنیم.

پیدا کردن مبلغ فروش یک کد محصول:

• برای این کار در تابع VLOOKUP ابتدا سلول G5 که حاوی کد محصول 176 است را انتخاب می کنیم.
• سپس از ستونی که کد محصول در آن است یعنی ستون کد محصول (ستون A) تا ستونی که مطمئن باشیم مبلغ فروش نیز در ناحیه قرار می گیرد (حداقل ستون D) را به عنوان محدوده انتخاب می کنیم.
• در قدم بعد چون ستون “مبلغ فروش” نسبت به ستون “کد محصول” ستون 4 ام است، عدد 4 را به عنوان ستون نتیجه در تابع قرار می دهیم.
• در آخر نیز عبارت FALSE را می نویسیم.

پیدا کردن تعداد فروش یک نام محصول:

• برای این کار در تابع VLOOKUP ابتدا سلول G8 که حاوی نام محصول “مانیتور” است را انتخاب می کنیم.
• سپس از ستونی که کد محصول در آن است یعنی ستون نام محصول (ستون B) تا ستونی که مطمئن باشیم تعداد فروش نیز در ناحیه قرار می گیرد (حداقل ستون C) را به عنوان محدوده انتخاب می کنیم.
• در قدم بعد چون ستون “تعداد فروش” نسبت به ستون “نام محصول” ستون 2 ام است، عدد 2 را به عنوان ستون نتیجه در تابع قرار می دهیم.
• در آخر نیز عبارت FALSE را می نویسیم.

محدودیت های تابع VLOOKUP

1) عدم توانایی جستجو در خلاف جهت اکسل:

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

2) سرعت پایین در جداول بزرگ:

در فایل هایی با داده های حجیم، استفاده زیاد از VLOOKUP ممکن است باعث کند شدن اکسل شود.

3) وابستگی به شماره ستون:

اگر ستونی به جدول اضافه یا حذف شود، شماره گذاری col_index_num تغییر می کند و فرمول ممکن است به درستی کار نکند.

4) بازگرداندن تنها اولین مقدار تطابق:

اگر مقدار جستجو شده چند بار تکرار شده باشد، تابع فقط اولین مقدار تطبیق را بازمی گرداند.

5) ناکارآمد در جستجوی چند شرطی:

تابع VLOOKUP فقط با یک مقدار ورودی کار می کند و برای شرایط ترکیبی (مثلاً جستجوی بر اساس نام و تاریخ) باید از روش های پیشرفته تر مانند INDEX + MATCH یا Power Query استفاده کرد.

ترکیب VLOOKUP با توابع دیگر

با IFERROR برای مدیریت خطاها:

=IFERROR(VLOOKUP(D2, A2:C100, 3, FALSE), "مقدار یافت نشد")

=IFERROR(VLOOKUP(D2, A2:C100, 3, FALSE), "مقدار یافت نشد")

با DATA VALIDATION برای جستجوی دینامیک:

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

با CONCATENATE یا TEXTJOIN برای ترکیب شرط ها:

در شرایط خاص، می توان دو ستون را ترکیب کرد و از VLOOKUP برای جستجوی ترکیبی استفاده کرد.

تفاوت VLOOKUP و HLOOKUP

VLOOKUP: برای جستجو در ستون ها (عمودی)

HLOOKUP: برای جستجو در ردیف ها (افقی)

مقایسه VLOOKUP با INDEX + MATCH

در جدول زیر مقایسه تابع VLOOKUP با توابع ترکیبی INDEX و MATCH را مشاهده می نمایید:

نکات کاربردی برای استفاده از VLOOKUP

1) همیشه ستون اول مهم است:

تابع VLOOKUP فقط می تواند مقدار جستجو در ستون اول را table_array بیابد. اگر داده هایتان در ستون وسط هستند، باید جدول را بازچینی کنید یا از تابع ترکیبی INDEX + MATCH استفاده کنید.

2) تطبیق دقیق در بیشتر مواقع بهتر است:

همیشه پیشنهاد می شود از FALSE برای تطبیق دقیق استفاده شود، مگر اینکه جدول شما به طور خاص برای تطبیق تقریبی مرتب شده باشد.

3) جلوگیری از خطا با IFERROR یا IFNA:

برای جلوگیری از بروز خطا در صورت نبود مقدار، بهتر است از ترکیب IFERROR استفاده شود:

=IFERROR(VLOOKUP(105, A2:C5, 3, FALSE), "یافت نشد")

=IFERROR(VLOOKUP(105, A2:C5, 3, FALSE), "یافت نشد")

جمع بندی

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

اگر به دنبال انعطاف پذیری بیشتر هستید، توصیه می شود حتماً با تابع INDEX و MATCH و ترکیب آن ها هم آشنا شوید:

آموزش تابع INDEX در اکسل

آموزش تابع MATCH در اکسل

امیدواریم این آموزش از سایت اهداف سبز برای شما مفید بوده باشد.

اگر سوال یا نظری درباره آموزش تابع VLOOKUP در اکسل دارید، خوشحال می شویم در بخش دیدگاه ها با ما به اشتراک بگذارید.