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

✅ جستجوی دقیق با VLOOKUP (Exact Match)

در بسیاری از مواقع، نیاز داریم یک مقدار خاص را دقیقاً در جدول پیدا کنیم. در این حالت، پارامتر چهارم تابع VLOOKUP باید FALSE باشد.

📌 مثال کاربردی:

=VLOOKUP(53, A2:D10, 4, FALSE)

جستجوی دقیق با VLOOKUP (Exact Match)

این فرمول به دنبال مقدار ۵۳ در ستون اول جدول می‌گردد و مقدار متناظر آن در ستون چهارم را بازمی‌گرداند. اگر این مقدار پیدا نشود، خطای #N/A ظاهر خواهد شد.

🔁 جستجوی تقریبی با VLOOKUP (Approximate Match)

در مواقعی که مقدار دقیق در دسترس نیست، می‌توانید از جستجوی تقریبی استفاده کنید. کافی است مقدار چهارم تابع را برابر با TRUE قرار دهید.

🧠 نکته مهم: ستون اول جدول باید به صورت صعودی مرتب شده باشد.
📌 مثال:

=VLOOKUP(85, A2:B10, 2, TRUE)

جستجوی تقریبی با VLOOKUP (Approximate Match)

در این مثال، اگر عدد ۸۵ موجود نباشد، نزدیک‌ترین مقدار کمتر از آن (مثلاً ۸۰) انتخاب شده و مقدار ستون دوم بازگردانده می‌شود.

لینک پیشنهادی: شما همچنین می توانید برای آشنایی با فرمول های شرطی اکسل، مقاله ” فرمول‌های شرطی در Excel؛ از IF ساده تا ترکیب با AND و OR ” مطالعه نمایید.

👀 چرا VLOOKUP فقط به سمت راست نگاه می‌کند؟

تابع VLOOKUP تنها قادر است مقادیری که در ستون‌های سمت راست ستون جستجو قرار دارند را بازگرداند. یعنی امکان جستجوی معکوس یا برگشتی وجود ندارد.

چرا VLOOKUP فقط به سمت راست نگاه می‌کند؟

📌 مثال:
اگر نام کوچک در ستون اول باشد، می‌توانید نام خانوادگی یا حقوق را که در ستون‌های بعدی هستند استخراج کنید. اما اگر بخواهید برعکس عمل کنید (یعنی بر اساس نام خانوادگی، نام کوچک را پیدا کنید)، باید از ترکیب توابع INDEX و MATCH استفاده کنید.

🧬 VLOOKUP و تکرار مقادیر (First Match Only)

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

VLOOKUP و تکرار مقادیر (First Match Only)

📌 مثال: اگر دو نفر با نام “Mia” در جدول باشند، تابع فقط اطلاعات فرد اول را نمایش می‌دهد.

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

🔠 جستجوی جزئی با Wildcardها

تابع VLOOKUP از کاراکترهای خاص مانند * برای جستجوی جزئی پشتیبانی می‌کند.

جستجوی جزئی با Wildcardها

📌 مثال:

=VLOOKUP("Jess*", A2:D10, 3, FALSE)

این فرمول به دنبال اولین مقداری می‌گردد که با “Jess” شروع می‌شود. برای جستجوی انتهایی یا بین کلمه‌ای می‌توانید از ترکیب * استفاده کنید:

*text برای پایان با
*text* برای شامل بودن

🔡 حساس نبودن به حروف بزرگ و کوچک

VLOOKUP به حروف بزرگ و کوچک حساس نیست. یعنی MIA و mia و حتی MiA برای آن یکسان هستند.
اگر نیاز به جستجوی حساس به حروف دارید، از ترکیب توابع INDEX، MATCH و EXACT استفاده کنید.

حساس نبودن به حروف بزرگ و کوچک

🔗 جستجو بر اساس چند معیار

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

جستجو بر اساس چند معیار

🚫 برخورد با خطای #N/A

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

برخورد با خطای #N/A

📌 مثال:

=IFNA(VLOOKUP(28, A2:B10, 2, FALSE), "یافت نشد")

💡 اگر از نسخه‌های قدیمی Excel استفاده می‌کنید، از IFERROR یا ترکیب IF و ISNA استفاده نمایید.

لینک پیشنهادی: شما همچنین می توانید برای آشنایی با روش های خطایابی در اکسل، مقاله ” خطایابی در فرمول‌های اکسل(Excel)؛ چرا نتیجه اشتباهه؟ ” مطالعه نمایید.

🗂 استفاده از چند جدول جستجو

شما می‌توانید چندین جدول برای جستجو داشته باشید و بسته به شرط، از یکی از آن‌ها استفاده کنید. کافی است با IF مشخص کنید کدام جدول استفاده شود.

استفاده از چند جدول جستجو

📌 مثال:

=VLOOKUP(D4, IF(C4="UK", Table1, Table2), 2, TRUE)

در این مثال، اگر مقدار بازار “UK” باشد از جدول اول، در غیر این صورت از جدول دوم استفاده می‌شود.

🔧 جایگزینی قدرتمند: ترکیب INDEX و MATCH

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

جایگزینی قدرتمند: ترکیب INDEX و MATCH

🪄 استفاده از جداول (Tables) برای ساده‌سازی فرمول‌ها

اگر داده‌های خود را به یک Table تبدیل کنید، استفاده از VLOOKUP بسیار ساده‌تر می‌شود. با استفاده از نام‌های جدول و ستون، دیگر نیازی به نگرانی درباره محدوده‌ها ندارید.

استفاده از جداول (Tables) برای ساده‌سازی فرمول‌ها

✨ از VLOOKUP به XLOOKUP مهاجرت کنید!

اگر از Excel 365 یا 2021 استفاده می‌کنید، توصیه می‌شود از تابع XLOOKUP استفاده کنید. این تابع جدیدتر، ساده‌تر و بسیار قدرتمندتر از VLOOKUP است و محدودیت‌های آن را ندارد.

از VLOOKUP به XLOOKUP مهاجرت کنید!