آموزش تابع VLOOKUP در اکسل همراه با مثال

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

تعریف و کاربرد vlookup در اکسل

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

فرمول VLOOKUP 

فرمول VLOOKUP به‌صورت زیر است:

 

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

 

• lookup_value: این مقدار، مورد جستجوی شما در ستون اول جدول است.
• table_array: این مورد، محدوده سلول‌هایی است که می‌خواهید در آن جستجو انجام دهید. ستون اول این محدوده باید شامل مقادیر مورد جستجو باشد.
• col_index_num: این مورد، شماره ستونی است که قصد بازگردانی مقدار مربوط به آن را دارید. به عنوان مثال، اگر مقدار مورد جستجوی شما در ستون اول جدول است و می‌خواهید مقدار را از ستون سوم برگردانید، باید عدد ۳ را وارد کنید.
• [range_lookup]: این گزینه، یک آرگومان اختیاری است که مشخص می‌کند آیا می‌خواهید یک تطابق دقیق یا تقریبی پیدا کنید. اگر این آرگومان TRUE باشد یا حذف شود، اکسل یک تطابق تقریبی (نزدیک‌ترین تطابق) پیدا خواهد کرد. اگر این آرگومان FALSE باشد، تطابقی دقیق تحویلتان می‌دهد.

استفاده از تابع VLOOKUP

برای استفاده از تابع VLOOKUP در اکسل، کافی‌است به‌صورت زیر اقدام کنید:

• سلولی را که می‌خواهید نتیجه VLOOKUP در آن نمایش داده شود انتخاب کنید. مثلاً سلول H4.
• در سلول انتخاب شده، تایپ کنید =VLOOKUP.
• دوبار روی VLOOKUP کلیک کنید. این کار یک پنجره کوچک برای کمک به شما باز خواهد کرد.
• سلولی را که می‌خواهید مقدار مورد جستجو را در آن وارد کنید، انتخاب کنید. مثلاً سلول H3.
• بعد از انتخاب سلول جستجو، یک ویرگول تایپ کنید.
• محدوده سلول‌هایی را که می‌خواهید در آن جستجو کنید، انتخاب کنید. مثلاً A2:E21. مجددا یک ویرگول تایپ کنید.
• شماره ستونی را که می‌خواهید مقدار مربوط به آن را برگردانید، وارد کنید. این شماره از سمت چپ شروع می‌شود. مثلاً اگر می‌خواهید مقدار از ستون دوم برگردانید، عدد ۲ را وارد کنید.
• اگر می‌خواهید یک تطابق دقیق پیدا کنید، FALSE را وارد کنید. برای پیدا کردن تطابق تقریبی TRUE را بنویسید.
• پس از وارد کردن همه آرگومان‌ها، Enter را بزنید تا نتیجه VLOOKUP نمایش داده شود.

نگران نباشید. کار به این پیچیدگی نیست. با این مثال تمام این مراحل را برایتان به آب خوردن تبدیل می‌کنیم.

مرحله اول: آماده‌ سازی داده‌ ها

فرض کنید جدولی از نام میوه‌ها و قیمت‌های آن‌ها دارید:

 

A	B
میوه	قیمت
سیب	۲۰۰۰
موز	۱۵۰۰
پرتقال	۱۸۰۰
کیوی	۲۵۰۰

 

مرحله دوم: سلول موردنظرتان را برای نمایش نتیجه انتخاب کنید

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

 

 

مرحله سوم: تابع VLOOKUP را وارد کنید

در سلول D2، فرمول زیر را وارد کنید:

 

=VLOOKUP(C2, A2:B5, 2, FALSE)

 

• C2: نام سلولی است که می‌خواهید قیمت آن را پیدا کنید. (مثلاً “سیب”).
• A2:B5: محدوده جدول که شامل نام میوه‌ها و قیمت‌ها است.
• ۲: شماره ستون قیمت در جدول.
• FALSE: برای جستجوی دقیق

مرحله چهارم: نتیجه را مشاهده کنید

پس از وارد کردن نام میوه در C2، نتیجه قیمت در D2 نمایش داده می‌شود. اگر «سیب» را وارد کنید، قیمت ۲۰۰۰ نمایش داده می‌شود.

 مثال های تابع VLOOKUP

در ادامه، با سه مثال ساده و کاربردی فهم این تابع را برایتان ساده می‌کنیم:

مثال اول:

فرض کنید جدولی از اطلاعات کارمندان دارید که در ستون A شماره کارمندان، در ستون B نام کارمندان و در ستون C حقوق آنها را ذخیره کردید. اگر می‌خواهید نام کارمندی با ID کارمندی‌اش پیدا کنید، تابع VLOOKUP به کمکتان می‌آید:

 

=VLOOKUP("12345", A2:C10, 2, FALSE)

 

این فرمول در ستون A به دنبال شماره کارمندی “۱۲۳۴۵” می‌گردد و سپس نام مربوط به آن را از ستون B برمی‌گرداند. آرگومان FALSE نشان می‌دهد که می‌خواهید یک مقدار دقیق پیدا کنید.

مثال دوم

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

 

 

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

=VLOOKUP(

اضافه کردن آرگومان‌ ها

حالا آرگومان‌های خود را اضافه می‌کنیم. آرگومان‌ها به VLOOKUP می‌گویند که چه چیزی را جستجو کند و کجا جستجو کند.

• آرگومان اول: نام موردی که به دنبال آن هستید، در اینجا «Photo frame» است. چون این آرگومان متن است، باید آن را در علامت‌ نقل قول قرار دهید:

=VLOOKUP(“Photo frame”

• آرگومان دوم: محدوده سلولی که شامل داده‌هاست. در این مثال، داده‌های ما در A2:B16 قرار دارد. مانند هر تابع دیگری، باید از کاما برای جدا کردن هر آرگومان استفاده کنید:

=VLOOKUP(“Photo frame”, A2:B16

مهم است بدانید که VLOOKUP همیشه در اولین ستون این محدوده جستجو می‌کند. در این مثال، در ستون A به دنبال «Photo frame» می‌گردد. مقداری که برمی‌گرداند (در این مورد، قیمت) همیشه باید در سمت راست آن ستون باشد.

• آرگومان سوم: شماره ایندکس، اولین ستون در محدوده شماره ۱ است، دومین ستون شماره ۲ و غیره. در اینجا ما در حال تلاش برای پیدا کردن قیمت هستیم و قیمت‌ها در ستون دوم قرار دارند. بنابراین آرگومان سوم ما ۲ خواهد بود:

=VLOOKUP(“Photo frame”, A2:B16, 2

• آرگومان چهارم: به VLOOKUP می‌گوید آیا دنبال تطابق‌های تقریبی باشد یا نه. اگر TRUE باشد، به دنبال تطابق‌های تقریبی می‌گردد. به‌طور کلی، این فقط زمانی مفید است که اولین ستون شامل مقادیر عددی مرتب شده باشد. چون ما فقط به دنبال تطابق‌های دقیق هستیم، آرگومان چهارم باید FALSE باشد. این آخرین آرگومان ما است، بنابراین پرانتز را ببندید:

 

=VLOOKUP("Photo frame", A2:B16, 2, FALSE)

 

تمام! وقتی Enter را فشار دهید، باید پاسخ را ببینید که ۹.۹۹ دلار است.

 

 

بیایید نگاهی به نحوه کار این فرمول بیندازیم:

این تابع ابتدا به‌صورت عمودی در اولین ستون جستجو می‌کند (VLOOKUP به معنای جستجوی عمودی است). وقتی «Photo frame» را پیدا کرد، به ستون دوم می‌رود تا قیمت را پیدا کند.

 

 

اگر بخواهیم قیمت یک مورد دیگر را پیدا کنیم، به‌سادگی می‌توانیم آرگومان اول را تغییر دهیم:

 

=VLOOKUP("T-shirt", A2:B16, 2, FALSE)

 

یا

 

=VLOOKUP("Gift basket", A2:B16, 2, FALSE)

 

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

مثال سوم: استفاده از مرجع سلولی برای ساده کردن جستجو

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

 

 

فرمول ما مشابه مثال قبلی خواهد بود، اما باید سه آرگومان اول را تغییر دهیم:

 

=VLOOKUP(E2, A2:B16, 2, FALSE)

 

برای پیدا کردن دسته‌بندی، باید آرگومان‌های دوم و سوم را تغییر دهیم. اول، محدوده را به A2:C16 تغییر می‌دهیم تا شامل ستون سوم شود. سپس شماره ایندکس ستون را به ۳ تغییر می‌دهیم زیرا دسته‌بندی‌ها در ستون سوم قرار دارند:

 

=VLOOKUP(E2, A2:C16, 3, FALSE)

 

وقتی Enter را فشار دهید، خواهید دید که Gift Basket در دسته‌بندی Gift قرار دارد.

 

 

اگر بخواهیم دسته‌بندی مورد دیگری را پیدا کنیم، به‌سادگی می‌توانیم نام مورد را در سلول E2 تغییر دهیم.

 

 

خطا های رایج در نتیجه VLOOKUP

VLOOKUP یک تابع بسیار مفید در اکسل است، اما مانند هر تابع دیگری ممکن است با خطاهایی مواجه شوید. در این بخش، برخی از مشکلات تابع vlookup در اکسل و راهکارهای جلوگیری از آن‌ها را بررسی خواهیم کرد.

خطای #N/A

 

 

این خطا زمانی رخ می‌دهد که مقدار مورد جستجو در ستون اول جدول پیدا نشود و ممکن است به دلایل زیر اتفاق بیفتد:

• مقدار مورد جستجو در داده‌های منبع وجود ندارد.
• ستون جستجو به ترتیب صعودی مرتب نشده است.
• مقدار مورد جستجو کوچکتر از کوچکترین مقدار در جدول است.

راهکار رفع خطای #N/A

• از تابع IFERROR برای مدیریت خطا استفاده کنید.
• مطمئن شوید مقدار مورد جستجو در داده‌های منبع وجود دارد.
• هرگونه فاصله اضافی قبل یا بعد از مقادیر در ستون جستجو را حذف کنید.
• سلول‌های جدول را که می‌خواهید جستجو کنید انتخاب کرده و آن‌ها را با نوع داده مناسب قالب‌بندی کنید.

خطای #VALUE!

این خطا زمانی رخ می‌دهد که یکی از سلول‌های ارجاع شده حاوی بیش از ۲۵۵ کاراکتر باشد. برای رفع این خطا فقط باید مطمئن شوید سلول‌های مورد استفاده حاوی بیش از ۲۵۵ کاراکتر نیستند.

مشکلات تابع vlookup در اکسل

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

۱. استفاده نادرست از مرجع مطلق

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

 

=VLOOKUP(C2, G$3:H$7, 2)

 

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

 

 

۲. گزینه TRUE/FALSE نادرست

در تابع VLOOKUP، گزینه range_lookup برای تعیین نوع جستجو (تقریبی یا دقیق) بسیار مهم است. به طور پیش‌فرض، این گزینه TRUE است که به معنای جستجوی تقریبی است. 

 

 

اگر می‌خواهید جستجوی دقیقی انجام دهید، باید FALSE را وارد کنید.

 

 

۳. فرمت عدد به عنوان متن

اگر مقادیر شما به‌صورت متن فرمت شده باشند، تابع VLOOKUP نمی‌تواند آن‌ها را شناسایی کند. برای حل این مشکل، می‌توانید از گزینه Text to Columns در تب Data استفاده کنید تا مطمئن شوید مقادیر به عنوان عدد شناسایی می‌شوند.

 

 

۴. مقدار جستجو در ستون اول نیست

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

 

 

نکات و قوانین تابع VLOOKUP اکسل

دانستن نکات زیر کمکتان می‌کند تا نهایت استفاده را از تابع VLOOKUP در اکسل ببرید:

۱. محدوده جدول را ثابت نگه دارید

هنگام استفاده از VLOOKUP، سعی کنید محدوده جدول را ثابت نگه دارید تا از بروز خطاها جلوگیری کنید. می‌توانید از مراجع مطلق استفاده کنید تا محدوده جدول را ثابت کنید.

۲. برای تطابق تقریبی، ستون اول را مرتب کنید

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

در مثال زیر، ما از VLOOKUP برای پیدا کردن نام اول استفاده می‌کنیم. VLOOKUP در اکسل برای مثال ما طوری تنظیم شده تا یک تطابق دقیق انجام دهد. اگرچه دو “Janet” در لیست وجود دارد، VLOOKUP فقط اولین مورد را تطبیق می‌دهد.

 

 

۳. از کاراکتر های (Wildcard) استفاده کنید

اگر می‌خواهید بخشی از یک مقدار را جستجو کنید، از کاراکترهایی مانند * (هر تعداد کاراکتر) و ? (یک کاراکتر) استفاده کنید.

۴. فضای اضافی را حذف کنید

قبل از استفاده از تابع VLOOKUP در اکسل مطمئن شوید در مقادیر جستجو و محدوده جدول هیچ فضای اضافی وجود ندارد.

۵. از تابع IFNA() برای جلوگیری از نمایش #N/A استفاده کنید

اگر می‌خواهید درصورت پیدا نکردن مقدار مورد جستجو، به جای پیغام خطای #N/A یک پیام دلخواه نمایش داده شود، از تابع IFNA() استفاده کنید.

۶. نوع داده‌ها باید یکسان باشد

مقدار جستجو و ستون نتیجه باید از یک نوع داده باشند (مثلاً هر دو باید متن یا هر دو عدد باشند).

۷. درصورت وجود چندین رکورد تطبیقی، اولین رکورد برگردانده می‌شود

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

۹. استفاده از INDEX/MATCH را درنظر بگیرید

درحالی که VLOOKUP معمولاً برای جستجوهای عمودی استفاده می‌شود، ترکیب توابع INDEX و MATCH انعطاف پذیری و کارایی بیشتری را به خصوص هنگام کار با داده‌های کلان دراختیارتان می‌گذارد.

نکات مهم تابع VLOOKUP اکسل

VLOOKUP دو حالت عملکردی دارد: تطبیق دقیق و تقریبی. در اکثر موارد، شما احتمالاً می‌خواهید VLOOKUP را در حالت تطابق دقیق استفاده کنید؛ یعنی می‌خواهید اطلاعات را براساس یک کلید منحصربه‌فرد جستجو کنید؛ مانند اطلاعات محصول براساس کد محصول یا داده‌های فیلم بر اساس عنوان فیلم.

 

 

=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact match

 

در مواردی که شما نه براساس یک شناسه منحصربه‌فرد، بلکه دنبال «بهترین تطابق» یا «بهترین دسته» هستید، باید از حالت تقریبی استفاده کنید. به عنوان مثال، ممکن است شما براساس وزن به دنبال پست، بر \اساس درآمد دنبال نرخ مالیات یا براساس تعداد فروش ماهانه دنبال نرخ کمیسیون باشید. در این موارد، احتمالاً مقدار دقیق جستجو را در جدول پیدا نخواهید کرد. در عوض، می‌توانید با VLOOKUP کاری کنید که بهترین تطابق را برای یک مقدار جستجو شده ارائه دهد.

 

 

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

 

در تابع VLOOKUP، نوع تطابق توسط آرگومان چهارم، range_lookup، کنترل می‌شود. اگر می‌خواهید یک تطابق دقیق داشته باشید، باید این آرگومان را به FALSE یا ۰ تنظیم کنید. اگر می‌خواهید یک تطابق تقریبی داشته باشید، آن را TRUE یا ۱ قرار دهید:

 

VLOOKUP(value,table,column,TRUE) // approximate match=
VLOOKUP(value,table,column,FALSE)  // exact match=

 

تابع VLOOKUP: کلید طلایی پیدا کردن  اطلاعات گم‌شده در اکسل 

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

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