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

معرفی تابع XLOOKUP

تابع XLOOKUP یک مقدار (مقدار معلوم) را در یک ستون یا سطر مشخص (محدوده جستجو) پیدا می‌کند و نتیجه‌ای (مقدار مجهول) را از ستون یا سطر دیگری (محدوده نتیجه) به عنوان خروجی بر می‌گرداند. برخلاف VLOOKUP، در این تابع محدوده جستجو و محدوده نتیجه به‌صورت جداگانه انتخاب می‌شوند؛ بنابراین ستون نتیجه می‌تواند در سمت چپ یا راست ستون جستجو قرار داشته باشد. این تابع به‌طور پیش‌فرض جستجوی دقیق انجام می‌دهد و علاوه بر آن امکان جستجوی بازه‌ای (کوچک‌تر یا بزرگ‌تر بعدی)، جستجو با کاراکترهای عام (Wildcard)، جستجو از انتها به ابتدا و تعیین پیام دلخواه در صورت پیدا نشدن مقدار را نیز دارد.

املا / نوشتار

XLOOKUP(Lookup_valueمقدار معلوم, Lookup_arrayمحدوده جستجو, Return_arrayمحدوده نتیجه (مجهول), [If_not_found]خروجی در صورت پیدا نشدن, [Match_mode]نوع تطابق, [Search_mode] جهت و روش جستجو)

ورودی ها

مقادیر ورودی Match_mode

مقادیر ورودی Search_mode

نوع خروجی

آزاد هر نوعی از داده وابسته به محدوده نتیجه برگردانده می شود. اگر محدوده نتیجه بیش از یک ستون (یا سطر) باشد، خروجی به‌صورت آرایه در سلول‌های مجاور سرریز (Spill) می‌شود.

نحوه کارکرد

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

نکته: محدوده جستجو و محدوده نتیجه باید هم‌اندازه باشند. برای مثال اگر محدوده جستجو B3:B12 است، محدوده نتیجه نیز باید از سطر 3 تا 12 باشد (مانند A3:A12 یا C3:E12). در غیر این صورت تابع خطای !VALUE# برمی‌گرداند.

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

نکته: اگر سلول نتیجه خالی باشد، XLOOKUP به جای سلول خالی عدد 0 برمی‌گرداند.

خطاها

N/A#اگر تابع XLOOKUP مقدار معلوم را پیدا نکند و ورودی If_not_found نیز وارد نشده باشد، خطای N/A# بر می گرداند.

!VALUE#اگر محدوده جستجو و محدوده نتیجه هم‌اندازه نباشند یا در ورودی‌های Match_mode و Search_mode عددی خارج از مقادیر مجاز وارد شود، این تابع خطای !VALUE# بر می گرداند.

!REF#اگر محدوده‌ها به یک فایل اکسل دیگر ارجاع داده شده باشند و آن فایل بسته باشد، این تابع خطای !REF# بر می گرداند.

!SPILL#اگر محدوده نتیجه چند ستونی باشد و سلول‌هایی که خروجی باید در آن‌ها سرریز شود خالی نباشند (یا فرمول داخل یک Table نوشته شده باشد)، این خطا رخ می‌دهد.

?NAME# زمانی که نام تابع را اشتباه وارد کنید، نام محدوده ها تعریف نشده باشند یا فایل در نسخه‌ای از اکسل باز شود که از XLOOKUP پشتیبانی نمی‌کند (اکسل 2019 و قبل از آن)، این خطا رخ می دهد.

نسخه و ملاحظات

تابع XLOOKUP در Microsoft 365، اکسل 2021، اکسل 2024 و Excel for the web در دسترس است و در اکسل 2019 و نسخه‌های قبل از آن وجود ندارد. اگر فایلی که حاوی این تابع است در نسخه‌های قدیمی باز شود، فرمول به شکل _xlfn.XLOOKUP نمایش داده شده و پس از محاسبه مجدد خطای ?NAME# برمی‌گرداند؛ بنابراین اگر فایل شما میان کاربران مختلف (مثلاً واحد مالی و حسابرس) دست‌به‌دست می‌شود، پیش از استفاده از نسخه اکسل آن‌ها مطمئن شوید. در نسخه‌های قدیمی می‌توانید از ترکیب توابع INDEX و MATCH به جای XLOOKUP استفاده کنید.

XLOOKUP جایگزین مستقیم توابع VLOOKUP و HLOOKUP است و مشکلات اصلی آن‌ها را برطرف می‌کند: امکان جستجو به سمت چپ (Backward) را دارد، جستجوی پیش‌فرض آن دقیق است، به شماره ستون وابسته نیست و پیام خطای دلخواه را بدون نیاز به IFERROR پشتیبانی می‌کند. حالت Regex (مقدار 3 در ورودی Match_mode) جدیدتر از خود تابع است و فقط در نسخه‌های به‌روز Microsoft 365 دیده می‌شود.

منابع و لینک ها

لینک آموزش تابع XLOOKUP در سایت W3Schools

لینک آموزش تابع XLOOKUP در سایت Microsoft

مثال

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

مثال 1: پیدا کردن نام حساب از روی کد معین (جستجو به سمت چپ)

فرض کنید جدول کدینگ حساب‌های شرکت مطابق تصویر ذیل در محدوده A2:D12 قرار دارد. در این جدول ستون A نام حساب، ستون B کد معین، ستون C گروه حساب و ستون D ماهیت حساب است. هنگام ثبت یا کنترل اسناد حسابداری معمولاً کد معین را در اختیار داریم (سلول G3) و می‌خواهیم نام حساب را در سلول H3 ببینیم. مشکل اینجاست که ستون نام حساب در سمت چپ ستون کد معین قرار گرفته و تابع VLOOKUP قادر به برگرداندن اطلاعات ستون‌های قبل از ستون جستجو نیست؛ مگر اینکه ساختار جدول را تغییر دهیم.

در چنین شرایطی با استفاده از تابع XLOOKUP و بدون هیچ تغییری در جدول، فرمول زیر را در سلول H3 می‌نویسیم:

=XLOOKUP(G3, B3:B12, A3:A12, “کد معین نامعتبر است”)

در فرمول فوق، ورودی اول یعنی سلول G3 مقدار معلوم است که حاوی کد معین 2103 می‌باشد. در ورودی دوم ستون کدهای معین (B3:B12) به عنوان محدوده جستجو و در ورودی سوم ستون نام حساب‌ها (A3:A12) به عنوان محدوده نتیجه انتخاب شده است. همانطور که می‌بینید نیازی به شمردن شماره ستون نیست و قرار گرفتن ستون نتیجه در سمت چپ ستون جستجو هیچ مشکلی ایجاد نمی‌کند. در ورودی چهارم نیز عبارت “کد معین نامعتبر است” را وارد کرده‌ایم تا اگر کاربر کدی وارد کند که در کدینگ حساب‌ها وجود ندارد، به جای خطای N/A# این پیام نمایش داده شود. دو ورودی آخر را وارد نکرده‌ایم، چون حالت پیش‌فرض آن‌ها یعنی جستجوی دقیق از ابتدا به انتها همان چیزی است که نیاز داریم. نتیجه فرمول عبارت «مالیات پرداختنی» خواهد بود.

نکته: برخلاف VLOOKUP که در صورت وارد نکردن ورودی آخر به‌صورت پیش‌فرض جستجوی تقریبی انجام می‌دهد، XLOOKUP به‌طور پیش‌فرض جستجوی دقیق انجام می‌دهد؛ بنابراین خطای رایج «فراموش کردن FALSE» در این تابع وجود ندارد.

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

مثال 2: برگرداندن چند ستون با یک فرمول

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

=XLOOKUP(G3, B3:B12, C3:D12)

چون محدوده نتیجه (C3:D12) دو ستون دارد، تابع دو مقدار برمی‌گرداند و نتیجه به‌طور خودکار در سلول‌های H3 و I3 سرریز (Spill) می‌شود. خروجی برای کد 2103 عبارت «بدهی جاری» و «بستانکار» خواهد بود.

نکته: سلول‌هایی که نتیجه در آن‌ها سرریز می‌شود باید خالی باشند؛ در غیر این صورت تابع خطای !SPILL# برمی‌گرداند.

مثال 3: طبقه‌بندی سنی مطالبات (جستجوی بازه‌ای)

یکی از گزارش‌های پرکاربرد واحد مالی، گزارش سنی مطالبات (Aging) است که در آن هر فاکتور بر اساس تعداد روزهای گذشته از سررسید در یک طبقه قرار می‌گیرد. فرض کنید فهرست فاکتورهای فروش در ستون‌های A تا D قرار دارد (شماره فاکتور، نام مشتری، مبلغ، روزهای گذشته از سررسید) و جدول طبقه‌بندی سنی در محدوده G3:H7 به شکل زیر تعریف شده است: 0 جاری، 31 بین 31 تا 60 روز، 61 بین 61 تا 90 روز، 91 بین 91 تا 180 روز و 181 بیش از 180 روز (مشکوک‌الوصول).

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

=XLOOKUP(D3, $G$3:$G$7, $H$3:$H$7, , -1)

در این فرمول ورودی پنجم (Match_mode) برابر 1- است؛ یعنی اگر مقدار دقیق پیدا نشد، نزدیک‌ترین مقدار کوچک‌تر در نظر گرفته شود. برای مثال برای فاکتوری با 75 روز گذشته از سررسید، عدد 75 در جدول وجود ندارد و تابع نزدیک‌ترین عدد کوچک‌تر یعنی 61 را انتخاب کرده و عبارت «61 تا 90 روز» را برمی‌گرداند. ورودی چهارم را خالی گذاشته‌ایم (دو ویرگول پشت سر هم) چون در این سناریو همیشه یک طبقه پیدا می‌شود. همین روش را می‌توان برای محاسبه درصد پورسانت فروشندگان، نرخ‌های پلکانی مالیات و جدول تخفیف حجمی نیز به کار برد.

نکته: در جستجوی بازه‌ای با VLOOKUP، ستون اول جدول حتماً باید به‌صورت صعودی مرتب باشد، اما XLOOKUP با Match_mode برابر 1- یا 1 حتی روی داده‌های نامرتب نیز نتیجه درست برمی‌گرداند.

مثال 4: پیدا کردن آخرین تراکنش یک مشتری (جستجو از انتها)

فرض کنید دفتر معین اشخاص که به ترتیب تاریخ مرتب شده، در ستون‌های A تا E قرار دارد (تاریخ، شماره سند، طرف حساب، شرح، مبلغ) و برای پیگیری وصول مطالبات می‌خواهیم تاریخ آخرین تراکنش مشتری وارد شده در سلول H3 را به دست آوریم. چون یک مشتری در چندین ردیف تکرار شده است، جستجوی معمولی اولین تراکنش او را برمی‌گرداند، در حالی که ما به آخرین تراکنش نیاز داریم.

برای این منظور فرمول‌های زیر را به ترتیب در سلول‌های H4 و H5 می‌نویسیم:

=XLOOKUP(H3, C3:C17, A3:A17, “تراکنشی ثبت نشده”, 0, -1)

=XLOOKUP(H3, C3:C17, E3:E17, 0, 0, -1)

در این فرمول‌ها ورودی ششم (Search_mode) برابر 1- است؛ یعنی جستجو از آخرین ردیف به سمت اولین ردیف انجام می‌شود و اولین موردی که پیدا می‌شود، در واقع آخرین تراکنش مشتری است. توجه کنید که برای مقداردهی ورودی ششم، باید ورودی‌های چهارم و پنجم را نیز وارد کنیم؛ به همین دلیل ورودی پنجم را برابر 0 (جستجوی دقیق) قرار داده‌ایم. از همین روش می‌توان برای پیدا کردن آخرین قیمت خرید یک کالا یا آخرین نرخ ارز ثبت شده استفاده کرد.

مثال 5: جستجوی دوبعدی در جدول مانده ماهانه حساب‌ها

فرض کنید جدول مانده حساب‌ها به‌صورت ماتریسی در محدوده A2:G12 قرار دارد؛ به این شکل که در ستون A کد معین و در سطر 2 نام ماه‌ها (فروردین تا شهریور) آمده است. می‌خواهیم با وارد کردن کد معین در سلول J3 و نام ماه در سلول K3، مانده حساب مربوطه را در سلول L3 به دست آوریم.

=XLOOKUP(J3, A3:A12, XLOOKUP(K3, B2:G2, B3:G12))

در این فرمول ابتدا XLOOKUP داخلی نام ماه را در سطر عنوان (B2:G2) جستجو می‌کند و کل ستون مربوط به آن ماه را برمی‌گرداند؛ این همان قابلیت جستجوی افقی است که در VLOOKUP وجود ندارد. سپس XLOOKUP بیرونی کد معین را در ستون A جستجو کرده و از ستونی که تابع داخلی برگردانده، مقدار هم‌ردیف را به عنوان خروجی نمایش می‌دهد. نتیجه، مانده حساب‌های دریافتنی تجاری در مرداد ماه خواهد بود.

مثال 6: جستجو با چند شرط

در شرکت‌هایی که چند شعبه دارند، شماره فاکتورها معمولاً در هر شعبه از عدد 1 شروع می‌شود و یک شماره فاکتور ممکن است در چند شعبه تکرار شود. فرض کنید فهرست فاکتورها در ستون‌های A تا D (شعبه، شماره فاکتور، نام مشتری، مبلغ) قرار دارد و می‌خواهیم با وارد کردن نام شعبه در G3 و شماره فاکتور در H3، مبلغ فاکتور را به دست آوریم.

=XLOOKUP(1, (A3:A40=G3)*(B3:B40=H3), D3:D40, “فاکتور یافت نشد”)

در این فرمول هر یک از عبارت‌های داخل پرانتز برای تمام ردیف‌ها یک مقدار منطقی (TRUE یا FALSE) تولید می‌کند. حاصل‌ضرب این دو آرایه فقط در ردیفی برابر 1 می‌شود که هر دو شرط همزمان برقرار باشند. بنابراین با جستجوی عدد 1 در این آرایه، ردیف مورد نظر پیدا شده و مبلغ فاکتور از ستون D برگردانده می‌شود. با اضافه کردن عبارت‌های بیشتر به حاصل‌ضرب، می‌توان تعداد شرط‌ها را افزایش داد.

مثال 7: جستجو با بخشی از نام حساب (کاراکترهای عام)

گاهی نام کامل حساب را به خاطر نداریم و فقط بخشی از آن را می‌دانیم. با استفاده از کدینگ حساب‌های مثال 1، فرض کنید کاربر در سلول G3 فقط کلمه «مالیات» را وارد کرده و می‌خواهیم کد معین آن را در H3 به دست آوریم.

=XLOOKUP(“*”&G3&”*”, A3:A12, B3:B12, “حسابی یافت نشد”, 2)

در این فرمول ورودی پنجم برابر 2 است تا علامت * به عنوان «هر تعداد کاراکتر» تفسیر شود. با قرار دادن * در دو طرف مقدار معلوم، تابع اولین حسابی را پیدا می‌کند که کلمه «مالیات» در هر جای نام آن آمده باشد و کد معین آن یعنی 2103 را برمی‌گرداند.

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

کاربردهای تابع XLOOKUP

کاربردهای تابع XLOOKUP بسیار گسترده است و در هر جایی که پیش‌تر از VLOOKUP، HLOOKUP یا ترکیب INDEX و MATCH استفاده می‌شد، می‌توان از این تابع با فرمولی ساده‌تر و خواناتر بهره برد. در واحدهای مالی و حسابداری، این تابع در مغایرت‌گیری بانکی، تطبیق حساب‌های فی‌مابین، گزارش سنی مطالبات، کنترل اسناد بر اساس کدینگ حساب‌ها و پیدا کردن آخرین تراکنش اشخاص کاربرد فراوان دارد. در ادامه بخشی از کاربردهای تابع XLOOKUP بصورت فهرست وار قید شده اند:

1- جستجوی دقیق یک مقدار و استخراج اطلاعات مرتبط از جداول

2- جستجو به سمت چپ بدون تغییر ساختار جدول

3- جستجوی بازه‌ای برای پورسانت، مالیات پلکانی و طبقه‌بندی سنی

4- پیدا کردن آخرین تراکنش، آخرین قیمت یا آخرین وضعیت

5- جستجوی افقی و دوبعدی در جداول ماتریسی

6- جستجو با چند شرط

7- بازگرداندن چند ستون اطلاعات با یک فرمول

8- مغایرت‌گیری و مقایسه جداول مختلف

9- تبدیل داده‌های کدگذاری‌شده به اطلاعات قابل‌فهم

توابع مشابه

VLOOKUP

HLOOKUP

LOOKUP

XMATCH

INDEX

MATCH

FILTER

توابع مرتبط

IF

IFERROR

IFNA

LET

SUMIFS

UNIQUE

TRIM

VALUE