XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
تابع XLOOKUP چه کاری می‌کند؟
تابع XLOOKUP یک مقدار را در یک ستون (یا ردیف) پیدا می‌کند و مقدار متناظر آن را از ستون دیگری برمی‌گرداند. این تابع برای رفع محدودیت‌های VLOOKUP ساخته شده است: لازم نیست شمارهٔ ستون بنویسید، ستون نتیجه می‌تواند چپ یا راست ستون جستجو باشد، و تطابق پیش‌فرض آن دقیق است.
آرگومان‌ها
| آرگومان | الزامی؟ | توضیح |
|---|---|---|
lookup_value |
بله | مقداری که دنبالش می‌گردید، مثلاً کد پرسنلی. |
lookup_array |
بله | ستون یا ردیفی که مقدار در آن جستجو می‌شود. |
return_array |
بله | ستون یا ردیفی که نتیجه از آن برگردانده می‌شود. باید هم‌اندازهٔ lookup_array باشد. |
if_not_found |
خیر | متنی که اگر مقدار پیدا نشد نمایش داده شود، به‌جای خطای #N/A. |
match_mode |
خیر | نوع تطابق. 0 دقیق (پیش‌فرض)، -1 دقیق یا اولین مقدار کوچک‌تر، 1 دقیق یا اولین مقدار بزرگ‌تر، 2 تطابق با نویسه‌های جایگزین (* و ?). |
search_mode |
خیر | جهت جستجو. 1 از اول به آخر (پیش‌فرض)، -1 از آخر به اول، 2 و -2 جستجوی دودویی روی داده‌های مرتب. |
مثال
همان جدول کارمندان را در نظر بگیرید که در سلول‌های A1 تا D5 قرار دارد:
| کد (A) | نام (B) | واحد (C) | حقوق (D) |
|---|---|---|---|
| 101 | علی رضایی | فروش | 25,000,000 |
| 102 | سارا احمدی | مالی | 30,000,000 |
| 103 | رضا کریمی | انبار | 22,000,000 |
| 104 | مینا صادقی | فروش | 27,000,000 |
۱) جستجوی ساده: کد در G2 است و نام فرد را می‌خواهیم:
=XLOOKUP(G2, A2:A5, B2:B5)اگر در G2 عدد 102 باشد، نتیجه «سارا احمدی» است. نیازی به نوشتن FALSE یا شمارهٔ ستون نیست.
۲) جستجو به سمت چپ: نام در H2 است و کد پرسنلی را می‌خواهیم. با VLOOKUP این کار ممکن نیست:
=XLOOKUP(H2, B2:B5, A2:A5)۳) پیام دلخواه وقتی مقدار پیدا نشد:
=XLOOKUP(G2, A2:A5, B2:B5, "یافت نشد")۴) برگرداندن چند ستون با یک فرمول: نام، واحد و حقوق را یکجا بگیرید. نتیجه خودش در سلول‌های کناری پخش می‌شود:
=XLOOKUP(G2, A2:A5, B2:D5)۵) آخرین مورد مطابق: وقتی یک مقدار چند بار تکرار شده و آخرین تکرار را می‌خواهید، آرگومان ششم را -1 بگذارید:
=XLOOKUP(G2, A2:A5, B2:B5, "یافت نشد", 0, -1)تفاوت با VLOOKUP
| ویژگی | VLOOKUP | XLOOKUP |
|---|---|---|
| جهت جستجو | فقط به سمت راست | هر دو جهت |
| شمارهٔ ستون | لازم است و با تغییر جدول خراب می‌شود | لازم نیست |
| تطابق پیش‌فرض | تقریبی (TRUE) |
دقیق |
| پیام وقتی پیدا نشد | نیاز به IFERROR | آرگومان چهارم |
| چند ستون نتیجه | خیر | بله |
نکته‌های مهم
- این تابع فقط در اکسل 365، اکسل 2021 و نسخه‌های جدیدتر و در اکسل تحت وب وجود دارد. در اکسل 2019 و قدیمی‌تر کار نمی‌کند. برای آن نسخه‌ها از INDEX و MATCH استفاده کنید.
- اگر فایل را برای کسی می‌فرستید که نسخهٔ قدیمی اکسل دارد، به‌جای نتیجه خطای
#NAME?می‌بیند. lookup_arrayوreturn_arrayباید تعداد ردیف (یا ستون) برابر داشته باشند.- اگر جداکنندهٔ آرگومان‌ها در اکسل شما کاما نیست، به‌جای
,از;استفاده کنید.
خطاهای رایج
| خطا | علت |
|---|---|
#N/A |
مقدار پیدا نشد. فاصلهٔ اضافه یا تفاوت عدد و متن را بررسی کنید، یا آرگومان چهارم را بنویسید. |
#VALUE! |
اندازهٔ lookup_array و return_array برابر نیست. |
#NAME? |
نسخهٔ اکسل شما این تابع را پشتیبانی نمی‌کند. |
#SPILL! |
وقتی چند ستون برمی‌گردانید، سلول‌های کناری خالی نیستند. |
جایگزین‌ها
برای نسخه‌های قدیمی اکسل از INDEX و MATCH استفاده کنید. برای گرفتن همهٔ ردیف‌های مطابق شرط، نه فقط اولین مورد، از FILTER کمک بگیرید. هر دو را در «توابع مرتبط» پایین همین صفحه می‌بینید.
