تابع XLOOKUP در اکسل ۲۰۲۱ اضافه شد. احتمالا X در ابتدای نام این تابع مخفف Extended است و بیانگر آن می‌باشد که این تابع نسخه «توسعه یافته توابع Lookup» از جمله تابع VLOOKUP اکسل است.
توجه ۱) این مقاله از روی هیچ منبع فارسی یا انگلیسی ترجمه و یا اقتباس نشده است و بر اساس روشی تدریس دوره‌های آموزشی اکسل پیشرفته فرساران، توسط مهندس میــدانی نوشته شده است. استفاده از ایده‌ها و روش تدریس این مقاله در محتواهای عمومی (مانند وب سایت‌ها و یا اینستاگرام و ... )، بدون ارجاع به منبع آن سرقت علمی محسوب می‌شود.
توجه ۲) از آنجایی که ممکن بود فرمول‌های این مقاله در هنگام نمایش به شما بهم ریخته شوند، در تمامی مثال‌ها از نام‌های انگلیسی استفاده شده و بدیهی است که هیچ محدودیتی در اکسل بین فارسی و انگلیسی نخواهیم داشت.
در فایل پیوست، حل مثال‌های این مقاله برای شما قرار داده شده است.
شباهت XLOOKUP با پدربزرگش VLOOKUP
تابع XLOOKUP مانند تابع VLOOKUP قرار است چیزی را جستجو کند و سپس بعد از آنکه آن چیز را یافت، به شما بگوید که چه مقداری در «جلو» و یا «پشت» آن چیز، وجود دارد و از این لحاظ کاملا شبیه VLOOKUP است. البته واقعا این تابع نسل جدید و دارای توانایی بسیار بیشتری است که پدر بزرگش ندارد. با این تفاوت‌ها در ادامه این مقاله آشنا خواهید شد.
همچنین تابع XLOOKUP می‌تواند جایگزین مناسبی برای کارهایی باشد که ما پیش از این با HLOOKUP, LOOKUP انجام می‌داده‌ایم.
بگذارید قبل از شروع آموزش XLOOKUP چند نکته را روشن کنم تا بهتر بتوانید برای استفاده کردن/نکردن از آن تصمیم بگیرید:
- اگر قرار است که فایل اکسل خود را برای افرادی ارسال کنید که احتمالا نسخه اکسل آنها ۲۰۱۹ و قبل‌تر است، توصیه می‌کنم که از این تابع استفاده نکنید.
- توجه کنید که همه ما باید تا سالها بر تابع VLOOKUP اکسل کاملا مسلط باشیم. زیرا در سازمان‌ها از VLOOKUP و INDEX و MATCH در هزاران فایل استفاده شده است. به همین دلیل شما را تشویق می‌کنیم که مقاله‌های زیر را پس از این خواندن این مقاله حتما با حوصله مطالعه کنید:
جدول خاله رویا
جدول زیر را خاله رویا اول هر سال تهیه می‌کند و در آن اسم بچه‌ها، تاریخ تولد و اسباب بازی که دوست دارند به عنوان کادو تولد بگیرند را می‌نویسید.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | نام | ماه تولد | کادو | ایموجی |
| 2 | baran asali | tir | microscope | 🔬 |
| 3 | kaveh | azar | skate board | 🛼🛹 |
| 4 | abtin | mordad | badbadak | 🪁 |
| 5 | ghandom | farvardin | tir o kaman | 🏹 |
خاله رویا می‌خواهد بر روی این جدول جستجو‌یی انجام دهد و از تابع XLOOKUP کمک می‌گیرد زیرا این تابع کارش جستجو است و البته بعد از جستجو، تابع ما مقداری را که در جلو یا پشت آن مورد است را به ما نشان خواهد داد.
پرسش ۱) آبتین/ abtin چه اسباب بازی را دوست دارد که کادو بگیرد؟
پاسخ)
=XLOOKUP("abtin", A:A, C:C)و خروجی این فرمول badbadak خواهد شد. حال می‌خواهیم یک به یک ورودی‌های تابع XLOOKUP را بررسی کنیم:
ورودی ۱) دنبال چی بگرد، در این مثال دنبال abtin می‌گردیم و چون یک متن است باید داخل "" آن را بگذاریم. بدیهی است که می‌تواند این ورودی آدرس هر سلولی از اکسل باشد.
ورودی ۲) در کدام ستون بگرد، در این مثال می‌دانیم که اسامی بچه‌ها در ستون A اکسل تایپ شده است.
توجه) این ورودی حتما باید ۱ بعدی باشد یعنی فقط یک ستون (یا یک سطر) و نمی‌توانید از اکسل بخواهید که در ستون‌های A:D به دنبال abtin بگردد.
ورودی ۳) اگر پیدا کرد، مقدار کدام ستون را می‌خواهید که تابع برگرداند و این ورودی باید آدرسی در اکسل باشد.
توجه) در ورودی سوم، XLOOKUP تابع مانند VLOOKUP نیست که از ما یک عدد صحیح بخواهد، بلکه در XLOOKUP باید یک محدوده از اکسل را باید مشخص کنیم که در این مثال کل ستون C است.
بگذارید قبل از ادامه این فرمول را یک بار دیگر ببینید.
=XLOOKUP("abtin", A:A, C:C)و اگر بخواهیم به صورت یک جمله فارسی این فرمول را بخوانیم، اینگونه خواهیم خواند:
دنبال abtin بگرد در ستون A بگرد و اگر پیدا شد مقدار ستون C را برایم بیار.
اگر نگاهی دقیق‌تر به تابع XLOOKUP بیندازیم، شکل ساده ورودی‌های این تابع در اکسل اینگونه است:
= XLOOKUP(lookup_value, lookup_array, return_array)نکته) باید بدانید که کلمه array را در فارسی آرایه ترجمه می‌شود. در کتاب‌های درسی به آن ماتریس هم می‌گوییم. در اکسل به صورت کلی هر آدرسی که بیش از یک سلول باشد، یک array خواهد بود. یادآوری می‌کنم که lookup_array فقط باید یک آرایه یک بعدی باشد.
تابع XLOOKUP آینه عقب دارد
ما با XLOOKUP می‌توانیم به دنبال چیزی بگردیم و سپس بگوییم که «در پشت آن»، چه مقداری وجود دارد. مانند همان کاری که راننده ماشین با آینه عقب انجام می‌دهد.
پرسش ۱) چه کسی دوست دارد کادوی تولدش بادبادک/ badbadak باشد؟
پاسخ)
=XLOOKUP("badbadak", C:C, A:A)و خروجی این فرمول abtin خواهد شد.
پرسش ۱) آیا اکسل «پشت» و «جلو» دارد؟
پاسخ) بله. ترتیب ستون‌های اکسل مشخص می‌کند که چه چیزی، در جلو/پشت چه چیز دیگری قرار دارد. بدیهی است که ستون A در پشت ستون C قرار دارد.
پرسش ۲) چرا این موضوع مهم است؟
پاسخ) چون تا قبل از اکسل نسخه ۲۰۲۱، برای یافتن چیزی که پشت سَر مقدار معلوم بود، باید از ترکیب توابع INDEX, MATCH استفاده می‌کردیم که برای اکثر کاربران اکسل دشوار است. ما در دوره اکسل پیشرفته فرساران، این توابع را آموزش می‌دهیم و همچنین مقاله «تابع INDEX و تابع MATCH» را می‌توانید مطالعه کنید.
تابع XLOOKUP و چند هندوانه با یک دست
بیایید با پرسش زیر شروع کنیم:
پرسش) ماه تولد و نام کادوی باران/baran را بدست آورید؟
پاسخ) می‌توانید تابع XLOOKUP را ۲ بار بنویسید. یکبار برای یافتن ماه تولد و یکبار دیگر برای یافتن نام کادو. خوشبختانه تابع XLOOKUP می‌تواند چندین مقدار را برای ما برگرداند. یعنی با یک دست چند هندوانه را بردارد و نیازی به نوشتن چندین XLOOKUP نیست و فرمول ما اینگونه خواهد شد:
=XLOOKUP("abtin", A:A, B:C)و خروجی این فرمول ۲ مقدار tir و microscope خواهد شد که چون ۲ مقدار است در ۲ سلول به صورت خودکار «سَـــر ریز» خواهد شد. به این اتفاق در اکسل Spill شدن می‌گویند.
توجه مهم) هیچ محدودیتی در ورودی سوم، یعنی تعداد ستون‌هایی (یا همانطور که در ادامه خواهید دید، سطرها) که تابع XLOOKUP قرار است برگرداند، نیست و همه حالت‌های زیر صحیح هستند:
=XLOOKUP( , , B:C)
=XLOOKUP( , , A:C)
=XLOOKUP( , , A:Z)تابع XLOOKUP زیر و زبر ندارد
تا اینجای این مقاله مثال‌هایی را دیدیم که به دنبال چیزی در ستونی بودیم یا به زبان دیگر ما «پشت و یا جلوی» مقدار را می‌خواستیم جستجو کنیم. اما XLOOKUP فقط محدود به این حالت نیست بلکه می‌تواند مقداری را در پایین و بالای (یا به قول مولانا زیر و زبر) چیزی را بیابد و برگرداند. بیایید جدول زیر که پول توجیبی پرداخت شده به بچه‌ها درماه‌های پاییز است را در نظر بگیریم:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | mehr | aban | azar | |
| 2 | baran | 10 | 12 | 13 |
| 3 | kaveh | 16 | 17 | 18 |
می‌خواهیم بدانیم که زیر ماه aban، در سطر 3 اکسل چه مقداری نوشته شده است. فرمول آن اینگونه می‌شود:
= XLOOKUP("aban", 1:1, 3:3)نکته) اگر بخواهیم بدانیم که به کاوه/kaveh در ماه آبان/aban چقدر پول توجیبی داده‌ایم، فرمول آن به شکل زیر می‌شود:
=XLOOKUP("aban", B1:D1, XLOOKUP("kaveh", A:A,B:D))(فقط این فرمول را نوشتم اما توضیح آن را فعلا نخواهم داد.)
تابع XLOOKUP در بن بست گیر نمی‌کند
می‌دانیم که همه توابع جستجو اگر در یافتن مقدار معلوم موفق نشوند، خطای NA را خواهند داد و برای مدیریت خطا‌ها در اکسل باید از تابع IFERROR استفاده کنیم. خوشبختانه برنامه نویسان اکسل تصمیم گرفته‌اند که مدیریت خطا را به عنوان چهارمین ورودی تابع XLOOKUP پیاده سازی کنند تا نیازی به نوشتن IFERROR نباشد. فرض کنید که در مثال قبل دنبال اسمی به نام bahar می‌گردیم و اگر این نام یافت نشد، به ما خطا ندهد و فقط بنویسید که «نیست». فرمول ما اینگونه خواهد شد:
=XLOOKUP("bahar", A:A, C:C, "nist!")شکل این تابع با ورودی چهارم در اکسل اینگونه است:
= XLOOKUP( ..., ...., ..., [if_not_found] )توجه ۱) ورودی چهارم می‌تواند هر چیزی که اکسل می‌فهمد -یعنی متن، عدد یا هر فرمولی- باشد.
توجه ۲) ورودی چهارم اختیاری است و به همین دلیل آن را در داخل علامت براکت یعنی [ ] قرار داده‌ایم و اگر این ورودی را ندهیم، تابع خطای NA را خواهد داد.
تابع XLOOKUP و کفش سیندرلا
به صورت پیش فرض تابع XLOOKUP اکسل مانند پیشکار شاهزاده قصه سیندرلا رفتار می‌کند. یعنی اگر به او گفتند که برو و صاحب کفش سایز ۳۸ را پیدا کن، او به دنبال کسی می‌گردد که دقیقا اندازه پای او ۳۸ و نه کمتر و بیشتر باشد.
=XLOOKUP( 38, A:A, B:B )یعنی با توجه جدول زیر، خروجی فرمول ما حتما «Cinderella» خواهد شد. 😎 به این حالت تابع Exact Match یعنی انطباق دقیق می‌گوییم و اگر در جدول ما سایز ۳۸ نباشد، خطای NA و یا چیزی که در ورودی چهارم مشخص کرده‌ایم را تابع برمی‌گرداند.
| A | B | |
|---|---|---|
| 1 | سایز پا | نام |
| 2 | 36 | Baran |
| 3 | 32 | Romina |
| 4 | 38 | Cinderella |
| 5 | 37.5 | Roxana |
| 6 | 34 | Abtin |
در ادامه می‌خواهم با ورودی پنجم تابع XLOOKUP شما را آشنا کنم:
= XLOOKUP( ..., ...., ..., ..., [Match_Mode] )که با آن می‌توانیم سایر حالت‌های تطبیق را (که یکی از مقدارهای 0 و -1 و 1 و 2 ) مشخص کنیم که در جدول زیر تعریف آنها به اختصار آمده است.
| Match_Mode | شرح |
|---|---|
| 0 - Exact match. | انطباق دقیق و اگر نیافت خطای NA می‌دهد. |
| -1 - Exact match or next smaller item. | انطباق دقیق و اگر نیافت کمترین نزدیکترین را خواهد داد. |
| 1 - Exact match or next larger number. | انطباق دقیق است و اگر نیافت بیشترین نزدیکترین را خواهد داد. |
| 2 - A wildcard match | کاراکترهای * و ? و ~ در جستجو معنای خاصی دارند. |
توجه) به صورت پیش فرض (یعنی اگر ورودی پنجم را ندهیم)، حالت تطبیق 0 است یعنی همان Exact match.
کفش سوغاتی خاله مژگان
خاله مژگان سالهاست که ایران زندگی نمی‌کند و بدیهی است که سایز پای خواهر زاده‌هایش را نمی‌داند و حدس می‌زند که شاید سایز پای یکی از آنها ۳۷ باشد و به همین خاطر یک کفش سایز ۳۷ را به عنوان یک سوغاتی همراهش آورده است. اگر بر روی جدول سایز کفش‌ها فرمول زیر را اجرا کنیم، خطا خواهیم گرفت، چون عدد ۳۷ نداریم و دقیقا XLOOKUP به دنبال ۳۷ است:
=XLOOKUP( 37, A:A, B:B )
در این حالت بهتر است که سایز پای یکی از بچه‌ها را پیدا کنیم، که کمترین از ۳۷ باشد و چون در سن رشد است احتمالا چند ماه آینده می‌تواند آن را بپوشد. بنابراین ما به دنبال کمترین نزدیکترین سایز پای موجود به عدد ۳۷ هستیم و کافی است که از XLOOKUP بخواهیم که آن را برای ما پیدا کند:
=XLOOKUP( 37, A:A, B:B, , -1 )
همانطور که در فرمول قبل می‌بینید، ورودی پنجم را عدد -1 گذاشتیم و حالا تابع به دنبال کمترین نزدیکترین عدد به ۳۷ می‌گردد و در جدول ما عدد ۳۶ وجود دارد و بنابراین این کفش برای باران/Baran خواهد شد. 🎉
توجه) همانطور که می‌بینید ورودی چهارم تابع را نداده‌ایم (دو کاما پشت سر هم در فرمول است) و تابع این ورودی را به صورت پیش فرض خودش (یعنی دادن خطای NA) محاسبه خواهد کرد.
بدشانس‌ترین خواهر زاده
تا اینجای داستان سوغاتی خاله مژگان، باران شانس آورد و صاحب کفش شده است و خوشحال است. حال می‌خواهیم ببینیم که بدشانس‌ترین خواهر زاده کیست. در واقع سایز پای او کمی و فقط کمی بزرگتر از ۳۷ است. از XLOOKUP می‌خواهیم که نام کسی که سایز پای او را که نزدیکترین بیشترین به عدد ۳۷ است را بیابد:
=XLOOKUP( 37, A:A, B:B, , 1 )و با گذاشتن عدد 1 در ورودی پنجم، فرمول نام Roxana را به ما می‌دهد چون سایز پای او ۳۷.۵ است.
نکته مهم) بر خلاف تابع VLOOKUP که باید برای یافتن کمترین-نزدیکترین، داده‌های ما می‌بایستی بر اساس ستون های معلومها Sort صعودی باشند (و البته برای یافتن بیشترین نزدیکترین که Sort نزولی الزامی است)، در XLOOKUP نیازی به Sort نداریم.
توجه) برای اطلاع از کاربردهای حالت تطبیق 1 و -1 مقاله «محاسبه مالیات حقوق» را مطالعه کنید و در این مقاله قصد ندارم که مثال‌های کاربردی بزنم. 😎
داوود بربری در تهران
یکی از بچه‌های دانشگاه ما، قراره مهمان حسن جان در تهران شود. وقتی که به تهران رسید، به راننده تاکسی گفت: والا.......، می‌خواهم بروم «قنات چشمه» . خوشبختانه به -قول حسن جان- راننده تاکسی واقعا تهران بلد بوده و فهمیده که داوود می‌خواهد برود به محله «قنات کوثر» تهران، زیرا ما در تهران جایی که در آن کلمه «چشمه» باشد، نداریم. و این داستان - البته به دلایل دیگری - سوژه‌ای بود در آن سالها.
حال بیایید جدول زیر را در نظر بگیریم که در آن نام هر محله و نام دوست داوود که در آن محله زندگی می‌کند وجود دارد:
| A | B | |
|---|---|---|
| 1 | محله | نام دوست داوود |
| 2 | zaferanieh | Javad Jaan |
| 3 | bagh saba | Farshid Khan |
| 4 | ghanat kousar | Hassan Jaan |
حال باید از XLOOKUP بخواهیم که مانند آن راننده تاکسی بفهمد که نام دوست داوود که در محله‌ای که شامل کلمه «قنات/ghant» است را بیابید. پس می‌نویسیم:
=XLOOKUP("*ghanat*", A:A, B:B, , 2 )و پاسخ این فرمول Hassan Jaan خواهد بود. در این حالت ما از wildcardها استفاده کرده‌ایم و ورودی پنجم تابع را عدد 2 گذاشته‌ایم و به تابع XLOOKUP فهمانده‌ایم که ما از wildcard استفاده کرده‌ایم. برای آشنایی دقیق‌تر با wildcardها مقاله « wildcardها در اکسل» را مطالعه کنید.
آخرین مراجعه کاوه به مطب جواد جوان
جوان جان دکتر دندانپزشک است و به ترتیب تاریخ مراجعه بیمارانش را ثبت می‌کند:
| A | B | |
|---|---|---|
| 1 | نام بیمار | تاریخ |
| 2 | Kaveh | 1404/02/01 |
| 3 | Mina | 1403/06/01 |
| 4 | Kaveh | 1403/07/01 |
| 5 | Kaveh | 1403/09/01 |
| 6 | Mina | 1403/10/01 |
| 7 | Kaveh | 1403/10/01 |
برای پیدا کردن اولین مراجعه کاوه/kaveh به مطبش، کافی است که یک XLOOUP ساده بنویسید. زیرا این تابع (مانند VLOOKUP) همیشه اولین مقدار را می‌یابد. حال جواد جان می‌خواهد که آخرین تاریخ مراجعه کاوه/Kaveh به مطبش را در بین لیست بسیار بلندی که دارد، بیابد و چون این لیست بر اساس تاریخ مرتب شده است، کافی است که از XLOOKUP بخواهد که آخرین Kaveh را بیابید و خوشبختانه اینکار با ورودی ششم این تابع قابل انجام است:
= XLOOKUP( ..., ...., ..., ..., ..., [search_mode] )
همانطور که می‌بینید، آخرین ورودی تابع search_mode نام دارد که می‌تواند یکی از مقادیر 1و -1و 2و -2 باشد:
| code | English Desc | شرح فارسی |
|---|---|---|
| 1 | Search first-to-last | جستجو از اولین آیتم شروع و به صورت پیش فرض، اینگونه انجام می‌شود. |
| -1 | Search last-to-first | جستجو از آخرین آیتم شروع و به صورت معکوس انجام می‌شود. |
| 2 | Binary search (sorted ascending order) | جستجو با استفاده از الگوریتم جستجوی دودویی انجام می‌شود، که نیازمند مرتب‌سازی صعودی محدوده جستجو است. اگر محدوده Sort صعودی نباشد، نتایج نامعتبر بازگردانده خواهد شد. |
| -2 | Binary search (sorted descending order) | جستجو با استفاده از الگوریتم جستجوی دودویی انجام می‌شود، که نیازمند مرتب‌سازی نزولی محدوده جستجو است. اگر محدوده Sort نزولی نباشد، نتایج نامعتبر بازگردانده خواهد شد. |
حال که جواد جان لازم دارد که از آخر به اول جستجو انجام شود، کافی است که ورودی ششم را عدد -1 بگذارد:
=XLOOKUP("kaveh", A:A, B:B, , , -1)و پاسخ این فرمول با توجه به جدول بیماران جواد جان، 1403/10/01 است.
ایده‌ای برای رژه رفتن روی اعصاب معلم
فرض کنید که معلم زیست وارد کلاس می‌شود و به بچه‌ها می‌گوید به تصویر صفحه ۵۷ نگاه کنید و سپس بچه‌ها در اقدامی هماهنگ شده از قبل، شروع می‌کنند از صفحه ۱ کتاب ورق زدن تا به صفحه ۵۷ برسند و چند لحظه بعد که معلم خواست که بروند یک صفحه دیگر، باز هم همین کار را تکرار کنند.
قطعا بعد از لحظاتی یکی از آنها اخراج می‌شود و مراسم رژه روی اعصاب معلم زیست به پایان می‌رسد. البته اگر همین بچه‌ها در کلاس هندســـه در چند ثانیه (چون از معلمشان خوششان می‌آید) صفحه ۵۷ را خواهند یافت.
الگوریتم باینری سرچ چیست؟
در کلاس هندسه بچه‌ها از الگوریتم باینری سرچ (یا جستجوی دو دویی) استفاده می‌کنند. یعنی :
قدم ۱) وسط کتاب را باز می‌کنند و شماره صفحه را می‌خوانند.
قدم ۲) اگر صفحه ۵۷ در نیمه اول کتاب باشد، نیمه دوم را نادیده می‌گیرند و اگر صفحه ۵۷ در نیمه دوم باشد، نیمه اولی را نادیده می‌گیرند.
قدم ۳) وسط نیمه‌ای از کتاب که صفحه ۵۷ در آن است را باز می‌کنند و شماره صفحه را می‌بینند.
قدم ۴) مجدد از قدم ۲ تا اینجا را دنبال می‌کنند تا بالاخره به صفحه ۵۷ کتاب برسند.
به این روش الگوریتم binary search یا جستجوی دو دویی می‌گویند و تقریبا همین کاری است که ما در یافتن معنی یک لغت در کتاب لغت‌نامه می‌کنیم. یعنی در هر بار نیمی از داده‌ها را کنار می‌گذاریم تا بالاخره به مقدار مورد نظرمان برسیم. جستجو با این الگوریتم بسیار سریع است زیرا همانطور که گفتم در هر بار نیمی از داده‌ها کنار می‌روند.
فرض کنید که کتاب هندسه ۱ میلیون صفحه داشته باشد و با روش باینری سرچ، یک کامپیوتر می‌تواند در بدترین حالت با ۲۰ حرکت صفحه ۵۷ را بیابد. فرمول ریاضی محاسبه تعداد تکرار log2(n) است که در آن n تعداد داده‌های ما است و اگر کتاب هندسه ۱ میلیارد صفحه باشد، کامپیوتر باید ۲۹ بار (یعنی فقط ۹ بار بیشتر از حالتی که ۱ میلیون صفحه دارد) کار جستجو را انجام دهد. برای آشنایی بیشتر صفحه «ویکی پدیای الگوریتم جستجوی دودویی» را مطالعه کنید.
تذکر بسیار مهم) الگوریتم جستجوی دو دویی (باینری) فقط وقتی کار می‌کند که داده‌های شما SORT شده باشند.
تابع XLOOKUP و جستجوی باینری
اگر داده‌های شما بر اساس ستون معلومها مرتب شده است (SORT است)، می‌توانید از حالت جستجوی باینری تابع XLOOKUP اکسل استفاده کنید تا جستجوی شما با سرعت بسیار بیشتری انجام شوند. این سرعت جستجو در فایل‌های بسیار بزرگ و سنگین، کاملا مشهود خواهد بود. اگر لیست شما به صورت صعودی (یعنی از کم به زیاد) مرتب شده است، ورودی ششم تابع را عدد 2 بگذارید:
=XLOOKUP( 75, A:A, B:B, , , 2)و برای لیست‌هایی که نزولی (از زیاد به کم) مرتب هستند، باید از عدد -2 استفاده کرد.
جدول مقایسه XLOOKUP و VLOOKUP
| شرح | VLOOKUP | XLOOKUP |
|---|---|---|
| توانایی جستجوی سطری | ❌ | ✔️ |
| توانایی برگرداندن مقداری پشت ستون معلوم | ❌ | ✔️ |
| توانایی برگرداندن چندین مقدار در یک جستجو | ❌ | ✔️ |
| توانایی جستجوی اولین مقدار | ✔️ | ✔️ |
| توانایی جستجوی آخرین مقدار | ❌ | ✔️ |
| توانایی مدیریت خطا در خود تابع | ❌ | ✔️ |
| توانایی جستجو با wildcard | ✔️ | ✔️ |
| توانایی جستجوی کمترین نزدیکترین | ✔️ | ✔️ |
| توانایی جستجوی کمترین نزدیکترین بدون Sort | ❌ | ✔️ |
| توانایی جستجوی بیشترین نزدیکترین | ✔️ | ✔️ |
| توانایی جستجوی بیشترین نزدیکترین بدون Sort | ❌ | ✔️ |
| توانایی جستجو بر اساس الگوریتم باینری | ❌ | ✔️ |
| خروجی Range (آدرسی در اکسل) | ❌ | ✔️ |
خروجی XLOOKUP یک محدوده است
تا اینجا دیدیم که XLOOKUP یک چیز را می‌یابد و سپس می‌گوید که در جلو یا پشت آن مقدار چه مقدار یا چه مقدارهایی وجود دارد. اما این تابع فقط به ما مقدار نمی‌دهد بلکه خروجی این تابع واقعا آدرس سلول یا سلولهای اکسل است. جدول زیر را در نظر بگیرید که پول توجیبی بچه‌ها در ماه‌های مختلف پاییز در آن وارد شده است:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | mah / esm | mehr | aban | azar |
| 2 | Abtin | 12 | 19 | 19 |
| 3 | Baran | 18 | 20 | 17 |
| 4 | Roxana | 20 | 14 | 13 |
حال می‌خواهیم همه پول توجیبی‌هایی که به باران داده‌ایم را محاسبه کنیم و فرمول زیر این کار را می‌کند:
= XLOOKUP( F3,A:A,B:D )خروجی این فرمول ۳ عدد 18، 20، 17 خواهد شد و این سه عدد به صورت خودکار در سلولهای همجواری که فرمول را نوشته‌ایم، نمایش داده می‌شوند و اینجاست که یک خطای ذهنی ممکن است رخ دهد و گمان کنیم که تابع XLOOKUP برای ما اعداد را یافته است اما واقعیت آن است که خروجی این تابع در این مثال آدرس محدوده B3:D3 است و اکسل به صورت پیش فرض مقادیری که در این آدرس هستند را به عنوان خروجی به ما نشان می‌دهد. این رفتار XLOOKUP دقیقا مانند چیزی است که در تابع INDEX داشته‌ایم. حال که می‌دانید خروجی XLOOKUP آدرس است، بنابراین با یک فرمول ساده زیر می‌توانیم تمام پول توجیبی‌های باران را جمع بزنیم:
= SUM( XLOOKUP( F3,A:A,B:D ) )با این ویژگی XLOOKUP می‌توانیم با فرمول نویسی اکسل، محدوده یا سلولی را به صورت پویا یا Dynamic تولید کنیم که برای آشنایی بیشتر با کاربرد Dynamic Rangeها می‌توانید مقاله‌های زیر را مطالعه کنید:
- آموزش ساخت یک داینامیک چارت در اکسل
ویدئوی ساخت لیست‌های وابسته در اکسل
همچنین استفاده از آدرس خروجی XLOOKUP به شکل زیر (یعنی استفاده از عملگر : برای ساخت یک محدوده) هم صحیح است:
= SUM( B2:XLOOKUP(F3,A:A,D:D) )پرسش‌های متداول تابع XLOOKUP اکسل
پرسش ۱) اگر فایلی که XLOOKUP دارد را برای فردی ارسال کنیم که نسخه اکسل او قبل از ۲۰۲۱ است، چه اتفاقی خواهد افتاد؟
پاسخ) باید بدانید که اکسل به صورت خودکار پیشوند _xlfn. را به هر تابعی که در نسخه‌های بالاتر از نسخه فعلی نوشته شده باشد، اضافه می‌کند مانند _xlfn.XLOOKUP و آن فرمول خطای #Name? در هنگام محاسبات خواهد داد. البته اگر او بلد باشد، می‌تواند اکثر فرمول شما را مجدد با VLOOKUP بازنویسی کند.
پرسش ۲) اگر مقدار معلوم یا همان lookup_value تکراری بود، چه خواهد شد و برای یافتن همه تکرارهای مقدار معلوم باید چه کار کرد؟
پاسخ) به صورت پیش فرض برای ما اولین مورد پیدا می‌شود. اگر بخواهیم که همه تکرارهای یک مقدار را بیابیم می‌توانید در اکسل ۲۰۲۱ (و نسخه‌های بعدی) از تابع FILTER استفاده کنید که مثالی از آن در فایل پیوست همین مقاله حل شده است. همچنین در نسخه‌های قدیمی هم می‌توانید از تکنیکی که در فایل پیوست مقاله «تابع SUMIFS اکسل» گفته شده است، استفاده کنید.
پرسش ۳) اگر محدوده‌ها (arrayها) مانند فرمول زیر با هم سازگار نباشند ، چه خواهد شد؟
= XLOOKUP( "baran", A2:A10, B2:D5 )با خطای #VALUE! مواجه خواهیم شد. حتما باید سازگاری بین اندازه محدوده معلومها (در اینجا تعداد سطرها) با محدوده مجهول‌ها وجود داشته باشد. بنابراین فرمول صحیح عبارت است:
= XLOOKUP( "baran", A2:A10, B2:D10 )نکته) اگر با تابع LOOKUP آشنا باشید، هیچ الزامی نیست که حتما محدوده معلومها و مجهول‌ها در کنار هم باشند. بلکه می‌توانند محدوده‌هایی کاملا جدا باشند اما کماکان سازگاری اندازه‌ها باید رعایت شود.
شما هم تجربه یا دیدگاه خود را بنویسید:
شما واقعا یکی از بهترین ها هستین...