اگر با VLOOKUP در اکسل کار کرده باشید، احتمالاً میدانید که یکی از محدودیتهای اصلی آن، ثابت بودن شماره ستون است. یعنی هر بار که بخواهید مقدار جدیدی را از جدول بگیرید، باید شماره ستون را بهصورت دستی در فرمول وارد کنید.
اما اگر بخواهیم این فرمول داینامیک باشد و با تغییر عنوان ستونها، خودش بهصورت خودکار ستون درست را پیدا کند، چه باید کرد؟
اینجاست که تابع MATCH به کمک VLOOKUP میآید.
ترکیب VLOOKUP و MATCH چیست؟ در این روش، تابع VLOOKUP وظیفه دارد نام محصول را در جدول پیدا کند و تابع MATCH شماره ستون مربوط به سال انتخابشده را تشخیص میدهد.
به این ترتیب، شما فقط نام محصول و سال را وارد میکنید و اکسل خودش مبلغ فروش درست را برمیگرداند.
مثال واقعی در فایل آموزشی در فایل اکسل این آموزش، یک جدول ساده داریم که شامل:
نام محصول سال 1402 سال 1403 سال 1404 سال 1405 در بخش ورودی، دو سلول در نظر گرفته شده است:
نام محصول در سلول B10 سال انتخابی در سلول C10 و خروجی نهایی در سلول D10 نمایش داده میشود.
فرمول استفادهشده در این فایل به شکل زیر است:
excel =VLOOKUP(B10,B3:F7,MATCH(C10,B3:F3,0),0) این فرمول چه کاری انجام میدهد؟ این فرمول از دو بخش تشکیل شده است:
- VLOOKUP ابتدا نام محصول را در ستون اول جدول جستوجو میکند.
در این مثال، مقدار سلول B10 یعنی اکسل جستوجو میشود.
- MATCH سپس تابع MATCH بررسی میکند که سال انتخابشده در سلول C10، مثلاً سال 1403، در کدام ستون قرار دارد.
در نتیجه، شماره ستون بهصورت خودکار پیدا میشود.
پس اگر کاربر بهجای سال 1403، گزینههای دیگر مثل سال 1402، سال 1404 یا سال 1405 را انتخاب کند، فرمول بدون نیاز به تغییر دستی همچنان درست کار میکند.
مزیت این روش چیست؟ استفاده از VLOOKUP + MATCH چند مزیت مهم دارد:
فرمول داینامیک میشود نیاز به تغییر دستی شماره ستون از بین میرود خطاهای انسانی کمتر میشود برای گزارشگیریهای سالانه و فایلهای مدیریتی بسیار کاربردی است برای ساخت داشبوردها و فایلهای تعاملی عالی است نتیجه مثال در فایل نمونه، وقتی:
نام محصول = اکسل سال = سال 1403 باشد، خروجی فرمول در سلول D10 برابر با 950 خواهد بود.
جمعبندی اگر میخواهید فرمولهای اکسل شما حرفهایتر، هوشمندتر و قابلاستفادهتر باشند، ترکیب VLOOKUP و MATCH یکی از بهترین روشهاست.
این تکنیک به شما کمک میکند تا بدون تغییر دستی فرمول، فقط با تغییر عنوان ستون یا سال، نتیجه درست را دریافت کنید.
