تابع XLOOKUP در اکسل تابعی جدید است که برای جستجوی افقی و عمودی استفاده می­شود. برای انجام جستجو از سمت چپ (با استفاده از ترکیب index و match)، پیدا کردن اخرین مقدار (ترکیب توابع و توابع آرایه ای)، vlookup با چندین شرط و بسیاری موارد دیگر نیاز به دانش بالا و استفاده از توابع ترکیبی و آرایه ای بود اما حالا با استفاده از این تابع کار به آسانی آب خوردن شده است.

وقتی نیاز به جستجو در اکسل دارید از کدام تابع استفاده می­کنید؟ آیا این تابع سنگ بنای Vlookup است یا خواهر HLOOKUP؟ در یک جستجوی پیچیده تر، آیا به ترکیب توابع INDEX و MATCH اعتماد می­کنید یا کار را به پاورکوئری می سپارید؟ خبر خوب این است که دیگر نیاز نیست از بین این چند گزینه یک مورد را انتخاب کنید. همه این موارد با یک جانشین قدرتمند و همه کاره، یعنی تابع XLOOKUP اکسل قابل انجام است.

شاید بپرسید از چه جهت XLOOKUP بهتر است؟ از بسیاری جهات! این تابع می­تواند به صورت افقی، عمودی و به سمت بالا به همراه چندین شرط جستجو کند و حتی به جای یک مقدار، یک ستون یا یک ردیف از داده را بازگرداند. سه دهه طول کشید اما بالاخره مایکروسافت موفق به طراحی تابعی قوی شد که بر بسیاری از خطاها و نقاط ضعفی vlookup غلبه کرد.

در چه نسخه ای از اکسل می توان از تابع XLOOKUP استفاده کرد؟  توی نسخه جدید آفیس یعنی نسخه 2021. دیگر غصه اینکه این تابع فقط برای مشترکان mocrosoft 365 در دسترس هست را نخورید.

فیلم آموزش نصب نسخه آفیس 2021

فیلم آموزشی معرفی تابع xlookup در اکسل

شاید برای خیلی از افراد دیدن یک فیلم آموزش فهم یک مطلب رو ساده تر میکنه. در این قسمت قصد داریم تابع xlookup رو به شما معرفی کنیم و ببینید این تابع چطور جایگزین تابع vlookup در اکسل و hlookup و ترکیب توابع index و match خواهد شد.

فیلم آموزشی تنظیم مقدار پیش فرض خطا تابع xlookup در اکسل

یکی از ویژگی های فوق العاده تابع xlookup تعیین مقدار پیش فرض خطا هست. اگر از تابع vlookup و match و سایر توابع جستجو استفاده کرده باشید، حتما با خطای #N/A رو به رو شدید. این خطا موقعی رخ میده که مقدار مورد نظر پیدا نشده باشه. در قدیم شما باید از تابع iferror استفاده می کردید تا در صورت خطا دادن این توابع مقدار مورد نظر شما رو نمایش بده. اما در این تابع به صورت درونی می تونید مقدار پیش فرض خطا رو تعیین کنید. آموزش های بیشتر تابع xlookup رو می تونید در لینک زیر ببینید:

تابع XLOOKUP در اکسل- ساختار و کاربردهای اساسی

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

ساختار این تابع به شکل زیر است:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

این تابع 6 آرگومان دارد. سه آرگومان اول اجباری و سه آرگومان آخر اختیاری هستند. در ادامه به معرفی این آرگومان ها می­پردازیم:

lookup_value: مقداری که می­خواهید جستجو کنید.

Lookup_array: محدوده یا آرایه ای که میخواهید در آنجا جستجو انجام شود.

Return_array: محدوده یا آرایه ای که مقادیر از آن بازگردانده می­شوند.

If_not_found: [اختیاری] اگر مقداری یافت نشد، مقداری که میخواهیم برگردانده شود را تعیین می کنید. در واقع اگر مقدار پیدا نشود به جای خطای #N/A نمایش داده می‌شود.

Match_mode: [اختیاری] نوع تطابق:

• 0 یا omitted (پیش فرض): تطابق دقیق، یعنی به دنبال عین عبارت باش. اگر پیدا نشد، خطای N/A برگردانده می­شود.
• 1-: تطابق دقیق یا کوچکتر بعدی. اگر تطابق دقیق یافت نشد، کوچکترین مقدار بعدی برگردانده می­شود. (محدوده جستجو باید به صورت نزولی مرتب شده باشد.)
• 1: تطابق دقیق یا بزرگتر بعدی. اگر تطابق دقیق یافت نشد، بزرگترین مقدار بعدی برگردانده می­شود. (محدوده جستجو باید به صورت صعودی مرتب شده باشد.)
• 2: استفاده از کاراکترهای وایلدکارد.

Search_mode: [اختیاری] جهت جستجو:

1 یا omitted (پیش فرض): جستجو کردن از اول به آخر.

1-: چستجو از آخر به اول.

2: جستجوی دو دویی (باینری) در داده هایی که به صورت صعودی مرتب شده‌اند.

2-: جستجوی دو دویی (باینری) در داده هایی که به صورت نزولی مرتب شده‌اند.

طبق گفته مایکروسافت، جستجوی باینری برای کاربران پیشرفته است. این یک الگوریتم خاص است که موقعیت مقداری که قرار است جستجو شود را در آرایه مرتب شده پیدا می­کند و آن را با عنصر میانی آرایه مقایسه می­کند. جستجوی باینری سریع تر از جستجوی عادی است. اما این جستجو فقط روی داده هایی به درستی کار میکند، که مرتب شده باشند.

کاربردهای اساسی فرمول XLOOKUP

برای درک بهتر این تابع، بیایید فرمول XLOOKUP را در ساده ترین حالت ممکن برای جستجوی دقیق بسازیم. برای این کار، ما به سه آرگومان اول نیاز داریم.

فرض کنید، یک جدول داریم که در آن خلاصه ای از پنج اقیانوس جهان قرار دارد. شما می­خواهید مساحت یک ورودی اقیانوس خاص را که در سلول F1 (lookup_value) قرار دارد بدست آورید. با نام اقیانوس ها در محدوده A2:A6 (lookup_array) و مساحت ها در محدوده C2:C6 (return_array)، فرمول به صورت زیر خواهد بود:

 

=XLOOKUP(F1, A2:A6, C2:C6)

 

این فرمول به زبان ساده می­گوید: مقدار F1 را در A2:A6 جستجو کرده و مقداری را از C2:C6 در همان ردیف برگردان.

بدون نیاز به شماره ستون، بدون مرتب سازی و نه هیچ چیز دیگه که همیشه vlookup از ما می­خواست. کارشو انجام میده.

مقایسه XLOOKUP و VLOOKUP

در مقایسه با vlookup، تابع XLOOKUP در اکسل مزایای زیادی دارد. در اینجا 10 مورد از بهترین ویژگی های این تابع را در مقایسه با توابع جستجوی دیگر را برایتان لیست کرده ایم:

1. جستجوی افقی و عمودی. نام این تابع برگرفته از توانایی اش در جستجوی افقی و عمودی است.
2. جستجو در تمام جهات: راست، چپ، بالا و پایین. در حالیکه که vlookup فقط می­تواند در ستون سمت چپ، و HLOOKUP در بالاترین ردیف جستجو کند، XLOOKUP چنین محدودیتی ندارد. جستجوی بدنام چپ در اکسل دیگر دردناک نیست!!!!!
3. تطابق دقیق (exact match) به صورت پیش فرض. در بیشتر شرایط، شما به دنبال یک تطابق دقیق هستید و xlookup آن را به صورت پیش فرض بر می­گرداند (برخلاف عملکرد vlookup که به صورت پیش فرض مطابقت تقریبی را در نظر می­گرفت).
4. تطبیق جزئی با wildcards. وقتی فقط بخشی از مقداری که می­خواهید جستجو کنید را به یاددارید، نه همه آن را، این نوع تطبیق بسیار مفید خواهد بود.
5. برعکس جستجو کنید. پیش از این، برای رسیدن به اخرین رویداد، باید ترتیب داده های منبع خود را معکوس می­کردید (یعنی باید ابتدا داده های خود را از اخر به اول مرتب می­کردید). اما اکنون کافیست که شما آرگومان search_mode را بر روی 1- تنظیم کنید تا فرمول Xlookup از اخر جستجو را شروع کند و آخرین تطابق را بازگرداند.
6. مقادیر متعددی را برگردانید. با استفاده از آرگومان return_value، می توانید تمام مقادیر مربوط به مقدار جستجو شده را برگردانید..
7. با چندین شرط جستجو کنید. تابع XLOOKUP آرایه ها را به صورت بومی به کار می­گیرد، که این مورد امکان جستجو با چندین شرط را فراهم می­کند.
8. تابع IF Error. در گذشته، برای برای خطاهای #N/A لازم بود از تابع IFNA استفاده کرد. اما تابع XLOOKUP در اکسل این قابلیت را در آگومان if_not_found گنجانده است که به شما اجازه می­دهد در صورتی عدم تطابق، به جای خطای #N/A متن مورد نظر شما برگردانده شود.
9. اضافه کردن یا حذف کردن ستون. یکی از ناراحت کننده ترین موضوعات مربوط به vlookup این است که اضافه کردن یا حذف کردن ستون ها فرمول را بهم می­ریزاند. اما در مورد Xlookup شما می­توانید ستون مورد نیاز خود را اضافه یا حذف کنید بدون اینکه فرمول بهم بریزد.
10. عملکرد بهتر. Vlookup می­تواند باعث کند شدن صفحه کاری شما در اکسل شود، زیرا هنگام محاسبات کل جدول را شامل می­شود، در نتیجه پردازش بیشتر از آنچه نیاز است انجام می­شود. اما Xlookup فقط آرایه های جستجو و بازگشت را که نیاز است را بررسی می­کند.

چگونگی استفاده از تابع XLOOKUP در اکسل – مثال های فرمولی

مثال های زیر مفیدترین ویژگی های xlookup را در عمل نشان می­دهد. علاوه بر این، شما چندین استفاده غیر معمول را خواهید دید که مهارت شما در جستجو را به سطح جدیدی می­رساند. پس بزن بریم.

XLOOKUP   جستجوی افقی و عمودی افقی و عمودی

اکسل قبلاً دارای دو تابع بود که برای انواع مختلف جستجو استفاده می­شدند. هر کدام از آن دو تابع قوانین خاص خود را داشتند. تابع vlookup به صورت عمودی در یک ستون و تابع HLOOKUP به صورت افقی در یک ردیف جستجو را انجام می­دادند.

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

برای جستجو عمودی ( جستجو مانند vlookup)، داده ها را در ستون ها قرار دهید. به مثال زیر دقت کنید:

=XLOOKUP(E1, A2:A6, B2:B6)

برای جستجوی افقی (جستجو مانند hlookup)، داده ها را به صورت ردیفی وارد کنید. به مثال زیر دقت کنید:

=XLOOKUP(I1, B1:F1, B2:F2)

فرمول XLOOKUP جستجو در سمت چپ

در نسخه های قبلی اکسل، فرمول index و match تنها روش قابل اعتماد برای جستجو به سمت چپ یا بالا بود. اکنون دیگر نیازی به ترکیب این دو تابع نیست. فقط آرایه ی جستجو هدف را مشخص کنید و xlookup بدون توجه به موقعیت مکانی که دارد مسئله را حل خواهد کرد.

به عنوان مثال، بیایید ستون Rank را در سمت چپ جدول خود وارد کنیم. هدف این است که رتبه اقیانوسی را که در سلول F1 مشخص شده است پیدا کنیم.  vlookup برای حل این مسئله گیر می­کند زیرا فقط می تواند مقداری از یک ستون  راکه در سمت راست ستون جستجو است برگرداند. اما فرمول Xlookup این مسئله را به راحتی حل می­کند. به فرمول و تصویر زیر دقت کنید:

=XLOOKUP(F1, B2:B6, A2:A6)

به همین ترتیب، هنگام جستجو افقی در ردیف ها، می توانید به سمت بالا را در نظر بگیرید.

تابع XLOOKUP در اکسل با تطبیق دقیق و تقریبی (exact and approximate match)

نوع تطبیق توسط آرگومان پنجم (match_mode) مشخص می­شود. به طور پیش فرض، «مطابقت دقیق» انجام می­شود.

لطفا توجه داشته باشید که حتی هنگامی که مطابقت تقریبی را انتخاب می­ کنید (حالت match_mode را روی 1 یا -1 تنظیم می­کنید.)، این تابع در ابتدا مطابقت دقیق را جستجو می­کند. تفاوت در آن چیزی است که وقتی مقدار جستجوی دقیق پیدا نشود، برگردانده می­شود.

آرگومان match_mode:

0 یا omitted: مطابقت دقیق؛ اگر پیدا نشود خطای #N/A برگردانده می­شود.

1 – : مطابقت دقیق، اگر پیدا نشود، آیتم کوچکتر بعدی را برمی­گرداند.

1 : مطابقت دقیق، اگر پیدا نشود، آیتم بزرگتر بعدی را بر می­گرداند.

مطابقت دقیق در تابع XLOOKUP

احتمالاً 99٪ از مواردی را که در اکسل جستجو می کنید ، از این گزینه استفاده می کنید. از آنجا که یک مطابقت دقیق رفتار پیش فرض XLOOKUP است ، می توانید match_mode را حذف کرده و فقط 3 آرگومان اولیه این تابع را وارد کنید.

در برخی از موقعیت ها مطابقت دقیق کار نخواهد کرد. یک سناریو معمول این است که  جدول جستجوی شما حاوی تمام مقادیر نباشد. بلکه شامل «نقطه عطف» یا «حدود» باشد مانند تخفیف هایی که بر اساس کمیت اعمال می­شوند یا کمیسیون های مبتنی بر فروش و بسیاری از موارد دیگر.

جدولی که در زیر می­بینید، همبستگی میان نمرات امتحان و رتبه را نشان می­دهد. همان طور که در شکل زیر می­بینید تطابق دقیق فقط زمانی کار می کند که نمره یک دانش آموزش خاص دقیقا با مقدار جدول جستجو مطابقت داشته باشد (مانند Christian در ردیف 3). در تمام موارد دیگر خطای #N/A برگردانده می­شود.

=XLOOKUP(F2, $B$2:$B$6, $C$2:$C$6)

برای به دست آوردن رتبه به جای خطای #N/A، باید به دنبال مطابقت تقریبی باشم. در قسمت بعدی مثالی این مورد را بررسی خواهیم کرد.

مطابقت تقریبی تابع XLOOKUP

برای انجام جستجوی تقریبی، با توجه به سازماندهی داده های ما ، آرگومان match_mode را روی 1- یا 1 تنظیم می­کنیم.

در مثال ما، جدول جستجو رتبه ها و نمره ها  از بزرگ به کوچیک مرتب شده اند. بنابراین، آرگومان match_mode را روی 1- تنظیم می­کنیم تا در صورتی که مطابقت دقیق وجود نداشت برای مقدار کمتر بعدی جستجو را انجام دهد. در نتیجه فرمول ما به صورت زیر خواهد بود:

=XLOOKUP(F11, $B$11:$B$15, $C$11:$C$15, ,-1)

برای مثال، امتیاز Brain، عدد 98 (سلول F2) است.فرمول این مقدار را در ستون B2:B6 جستجو می­کند اما آن را پیدا نمی­کند. سپس مورد کوچکتر بعدی را جستجو می­کند  و 90 را پیدا می­کند. که رتبه A مربوط به آن است:

اگر در جدول جستجو رتبه ها از کوچک به بزرگ مرتب شده باشند، ما باید آرگومان match_mode را روی 1 تنظیم کنیم، تا در صورتی که مطابقت دقیق وجود نداشت، برای مقدار بزرگتر بعدی جستجو را انجام دهد:

=XLOOKUP(F2, $B$2:$B$6, $C$2:$C$6, ,1)

فرمول به دنبال 98 می­گردد و مجددا آن را پیدا نمی­کند. حالا، تلاش میکند تا مورد بزرگتر بعدی را پیدا کند، 100 را پیدا می کند که رتبه مربوط به آن A می باشد:

نکته: هنگامی که فرمول xlookup را در چندین سلول کپی می کنید، محدوده جستجو یا بازگشت را ثابت کنید (برای ثابت کردن محدوده از علامت دلار استفاده کنید؛ مانند $B$2:$B$6) به این ترتیب محدوده ثابت می ماند و تغییر نمی­کند.

تابع XLOOKUP در اکسل به همراه کاراکترهای ویلدکارد

برای انجام یک جستجوی جزئی، آرگومان match_mode را روی 2 تنظیم کنید، که به تابع xlookup دستور می­دهد تا کاراکترهای ویلدکارد را پردازش کند:

ستاره (*)- نشان دهنده توالی کاراکترها است.

علامت سؤال (?)- نشان دهنده هر کاراکتر است.

برای اینکه متوجه نحوه کار شوید، به مثال زیر توجه کنید. در ستون A چند مدل گوشی هوشمند و در ستون B ظرفیت باتری آنها مشخص شده است. شما در مورد ظرفیت باتری یک تلفن هوشمند خاص کنجکاو هستید. اما مطمئن نیستید که می­توانید نام مدل را دقیقاً همانطور که در ستون A آمده است، تایپ کنید. برای این کار بخشی را که مطمئن هستید تایپ کنید و باقی مانده آن را با نویسه های ویلدکارد جایگزین کنید.

به عنوان مثال، برای کسب اطلاعات در مورد باتری iPhone X، از این فرمول استفاده کنید:

=XLOOKUP("*iphone X*", A2:A8, B2:B8, ,2)

یا اینکه قسمتی از نام را که می­دانید در یک سلول وارد کنید (در این مثال ما نام را در سلول E1 وارد کرده ایم.) و آرگومان اول فرمول سلول مرجع را به کاراکترهای ویلکارد پیوند دهید:

=XLOOKUP("*"&E1&"*", A2:A8, B2:B8, ,2)

فرمول XLOOKUP اخرین تطبیق (last match)

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

جهت جستجو توسط آرگومان ششم (search_mode) کنترل میشود:

1 یا omitted (پیش فرض)- جستجو از اولین مقدار تا آخرین مقدار انجام می­شود. یعنی به صورت عمودی از بالا به پایین یا به صورت افقی از چپ به راست.

1 – -به صورت معکوس از آخر به اول جستجو می­کند.

به عنوان مثال، بیایید آخرین فروش انجام شده توسط یک فروشنده خاص را برگردانیم. برای این منظور، سه آرگومان اول مورد نیاز است (G1 برای lookup_value، B2:B9 برای lookup_array و D2:D9 برای return_array) و آرگومان پنجم را 1- قرار دهید:

=XLOOKUP(G1, B2:B9, D2:D9, , ,-1)

آسان بود، درسته؟

تابع XLOOKUP در اکسل و برگرداندن چندین ستون یا ردیف

یکی از ویژگی های شگفت انگیز تابع Xlookup اکسل توانایی بازگرداندن بیش از یک مقدار است. نیاز به انجام کار اضافی نیست، با همان روش همیشگی فرمول را می­نویسیم.

فرض کنید از جدول زیر میخواهید تمام جزئیات مربوط به یک فروشنده خاص (که در F2 نامش نوشته شده است) را بازیابی کنید. کاری که باید انجام دهید این است که برای آگرومان return_array باید به جای ستون یا ردیف واحد، یک دامنه تعیین کنید:

=XLOOKUP(F2, A2:A7, B2:D7)

فرمول را در سلول بالا سمت چپ محدوده نتیجه، وارد کنید (در اینجا سلول G2 است). به این ترتیب اکسل به صورت خودکار نتایج را در سلول های خالی مجاور وارد می­کند. در مثال ما آرایه بازگشتی (B2:D7) شامل سه ستون است (تاریخ، مورد و مقدار)، و تمام این سه مقدار در محدوده G2:I2 بازگردانده شده است.

اگر ترجیح می­دهید نتایج به صورت عمودی در یک ستون قرار گیرد، xlookup را در تابع TRANSPOSE قرار دهید تا نتیجه به صورت عمودی در ستون قرار گیرد:

=TRANSPOSE(XLOOKUP(G1, A2:A7, B2:D7))

به شکل مشابه، شما می­توانید کل ستون داده را برگردانید، مثلا ستون amount. برای این کار، سلول F1 را که حاوی “amount” است به عنوان آرگومان lookup_value قرار دهید، محدوده A1:D1 که شامل عنوان ستون های جدول است به عنوان lookup_array و محدوده A2:D7 که شامل تمام داده ها است را به عنوان return_array قرار دهید.

=XLOOKUP(F1, A1:D1, A2:D7)

با توجه به اینکه نتایج در سلول های همسایه وارد می­شوند، اطمینان حاصل کنید که تعداد سلول های خالی در سمت راست یا پایین به اندازه تعداد نتایج که قرار است نمایش داده شوند، می­باشد. اگر تعداد سلول های خالی به اندازه کافی نباشند خطای #SPILL بازگردانده می­شود.

=XLOOKUP(1, (A2:A10<=G1) * (B2:B10=G2) * (C2:C10=G3), D2:D10)