پرکاربردترین تابع، پرخطاترین تابع
VLOOKUP یک مقدار را در ستون اول یک جدول پیدا می‌کند و مقدار ستون n ام همان سطر را برمی‌گرداند. میلیون‌ها فایل اداری با آن ساخته شده‌اند و باید آن را کامل بشناسید، حتی اگر در فایل‌های جدید XLOOKUP بنویسید.
=VLOOKUP(مقدار_جست‌وجو, جدول, شماره_ستون, [نوع_تطبیق])
قیمت کالای کد G2 از جدول کالا (A=کد، B=نام، C=واحد، D=قیمت):
=VLOOKUP(G2, $A$2:$D$500, 4, FALSE)پنج دام VLOOKUP
| دام | نتیجه | پیشگیری |
|---|---|---|
| آرگومان چهارم فراموش شود | پیش‌فرض TRUE (تقریبی) است؛ روی داده‌ی نامرتب نتیجه‌ی غلط بدون خطا | همیشه FALSE یا 0 بنویسید |
| شماره‌ی ستون ثابت (4) | درج یک ستون در جدول، ستون اشتباه برمی‌گرداند | MATCH برای شماره‌ی ستون، یا Table |
| ستون کلید باید اول باشد | جست‌وجو به سمت «چپ» ممکن نیست | INDEX/MATCH یا XLOOKUP |
| نوع داده‌ی ناهمسان | کد 1001 (عدد) با "1001" (متن) برابر نیست ← #N/A | یکدست‌سازی نوع، یا G2&"" و --G2 |
| سلول نتیجه خالی باشد | 0 برمی‌گرداند نه خالی | VLOOKUP(…)&"" برای متن |
INDEX/MATCH: جایگزین قابل‌اعتماد در همه‌ی نسخه‌ها
MATCH جای یک مقدار را در یک ستون پیدا می‌کند (شماره‌ی سطر) و INDEX از یک ستون دیگر، عنصر همان سطر را برمی‌گرداند. چون ستون جست‌وجو و ستون نتیجه جدا هستند، درج ستون چیزی را خراب نمی‌کند و جهت هم مهم نیست.
یک‌طرفه:
=INDEX($D$2:$D$500, MATCH(G2, $A$2:$A$500, 0))
جست‌وجوی دوطرفه (سطر = نماینده، ستون = ماه):
=INDEX($B$2:$M$50, MATCH(G2, $A$2:$A$50, 0), MATCH(H2, $B$1:$M$1, 0))
چند شرط (در 2016/2019 با Ctrl+Shift+Enter وارد شود):
=INDEX(E2:E500, MATCH(1, (A2:A500=G2)*(C2:C500=H2), 0))
VLOOKUP با شماره‌ی ستون پویا:
=VLOOKUP(G2, $A$1:$M$50, MATCH(H2, $A$1:$M$1, 0), FALSE)تطبیق تقریبی؛ وقتی درست به کار رود
حالت تقریبی برای جدول‌های پلکانی است: نرخ پورسانت، ضریب تخفیف یا مالیات پلکانی. جدول باید بر اساس ستون اول صعودی مرتب باشد؛ آن‌گاه تابع بزرگ‌ترین مقداری را که از مقدار جست‌وجو کوچک‌تر یا برابر است پیدا می‌کند. =VLOOKUP(C2, $K$2:$L$5, 2, TRUE) با جدولی شامل 0، 2E9 و 5E9 در ستون K و نرخ‌ها در ستون L، همان پورسانت پلکانی درس ۱۱ است بدون IF.
نکته‌هایی که کمتر کسی می‌داند
- VLOOKUP و MATCH در حالت دقیق wildcard می‌پذیرند:
VLOOKUP("*ماهی*", …)اولین طرحی را که «ماهی» دارد پیدا می‌کند؛ اگر مقدار شما خودش * یا ? دارد، باید با ~ خنثی شود. - جست‌وجوی تقریبی روی داده‌ی مرتب با الگوریتم دودویی انجام می‌شود و روی صدها هزار سطر هزاران برابر سریع‌تر از حالت دقیق است. ترفند حرفه‌ای‌ها: یک VLOOKUP تقریبی برای گرفتن مقدار، و یک IF که بررسی کند کلید پیداشده واقعاً برابر است.
- MATCH با آرگومان سوم 0 اولین تطابق را برمی‌گرداند؛ برای یافتن آخرین تطابق در نسخه‌های قدیمی از
LOOKUP(2, 1/(A2:A500=G2), D2:D500)استفاده کنید — یک ترفند قدیمی که بدون Ctrl+Shift+Enter کار می‌کند. - اگر VLOOKUP با جدولی در فایل دیگر کار می‌کند و آن فایل بسته باشد، فقط مقادیر ذخیره‌شده‌ی آخرین بار را می‌بیند؛ INDIRECT در همین حالت اصلاً کار نمی‌کند و #REF! می‌دهد.
- برای جست‌وجوی حساس به حروف بزرگ و کوچک (کدهای لاتین مثل ab12 و AB12) از
MATCH(TRUE, EXACT(A2:A500, G2), 0)استفاده کنید.