یکی از توابع جستجو کردن در اکسل، XLOOKUP است که در نسخه‌های نسبتاً جدید اکسل قابل استفاده است. تفاوت اصلی XLOOKUP با دو تابع مشابه یعنی HLOOKUP و VLOOKUP در این است که جستجو را به صورت دو طرفه انجام می‌دهد و مقدار موردنظر را در تقاطع یک ردیف و ستون پیدا میکند.
در ادامه مطلب روش استفاده از XLOOKUP در اکسل را با مثال‌های ساده توضیح می‌دهیم. با سیاره‌ی آی‌تی همراه باشید.
قبل از هر چیز توجه کنید که تابع XLOOKUP در اکسل 2016 یا 2019 و نسخه‌های قدیمی‌تر موجود نیست و مختص اکسل ۲۰۲۱ است. البته اگر از نسخه تحت وب اکسل و سرویس مایکروسافت ۳۶۵ استفاده می‌کنید نیز این تابع قابل استفاده است. خوشبختانه در اپلیکیشن موبایل اکسل نیز تابع XLookUp قابل استفاده است.
اگر نرم‌افزار Excel نصب شده روی سیستم شما قدیمی است، به جای XLOOKUP می‌توانید فرمول‌های طولانی‌تری بنویسید و توابع موجود را ترکیب کنید.
فرمول نویسی XLOOKUP برای جستجوی یک طرفه در اکسل
ساده‌ترین حالت استفاده از تابع XLOOKUP، جستجو کردن به صورت یک طرفه یا به قول برخی از کاربران، XLOOKUP یک شرطی است. سینتکس کلی تابع ایکس‌لوک‌آپ به صورت زیر است و ۶ آرگومان دارد:
=XLOOKUP(a,b,c,d,e,f)
آرگومان‌های تابع XLOOKUP به ترتیب به صورت زیر است:
- a مقداری است که جستجو می‌شود.
- b آرایه‌ای است که جستجو در آن انجام می‌شود.
- c آرایه‌ای است که تابع برمی‌گرداند.
- d متنی است که در صورت پیدا نشدن a برگردانده می‌شود. این آرگومان اختیاری است.
- e نوع تطبیق مقدار حین جستجو کردن است. اگر عدد 0 را وارد کنید، دقیقاً a جستجو می‌شود. اگر منفی ۱ وارد کنید، نزدیک‌ترین عدد کوچک‌تر از a انتخاب می‌شود و اگر عدد مثبت ۱ را وارد کنید، نزدیک‌ترین عدد بزرگ‌تر از a شناسایی می‌شود. عدد ۲ نیز برای جستجوی الگوها کاربرد دارد. این آرگومان نیز اختیاری است.
- f حالت جستجو را مشخص میکند که اختیاری است. عدد 1 برای جستجو از بالا به پایین یا چپ به راست است و عدد منفی ۱ برای جستجو از پایین به بالا یا راست به چپ است. عدد ۲ نیز جستجوی دودویی برای آرایه‌ی مرتب شده به صورت نزولی است و عدد منفی ۲ برای جستجوی دودویی در آرایه‌ی مرتب شده به صورت صعودی کاربرد دارد.
توجه داشته باشید که اگر آرگومان چهارم را وارد نکنید و مقدار موردنظر در آرایه پیدا نشود، اکسل عدد صفر خروجی تابع خواهد بود. از طرفی اگر آرگومان e و f را وارد نکنید، حالت پیش‌فرض اعمال می‌شود که جستجو از بالا به پایین یا چپ به راست برای پیدا کردن مقدار دقیق یعنی a است.
و اما یک مثال ساده از فرمول‌نویسی تابع XLOOKUP برای جستجوی یک طرفه، فرمول زیر را در نظر بگیرید:
=XLOOKUP(F2,A2:A100,D2:D100,"Invalid ID",0,1)
فرمول فوق برای جستجو کردن مقدار موجود در سلول F2 در محدوده‌ی سلول‌های A2 الی A100 نوشته شده است و خروجی آن یکی از سلول‌های D2 الی D100 متناسب با محدوده‌ی A2 الی A100 است. آرگومان پنجم عدد ۰ است و این یعنی اگر مقدار F2 با هیچ یک از سلول‌های A2 تا A100 برابر نباشد، آرگومان چهارم که متن Invalid ID است، برگردانده می‌شود. در نهایت آرگومان آخر عدد 1 است و این یعنی جستجو از بالا به پایین صورت می‌گیرد.
به طور کلی نوشتن فرمول با تابع XLOOKUP درست شبیه استفاده از تابع HLOOKUP در اکسل است با این تفاوت که می‌توان جزئیات بیشتری در مورد روش جستجو مشخص کرد و کار ساده‌تر می‌شود.
استفاده از XLOOKUP دو شرطی
گاهی اوقات هدف پیدا کردن یک مقدار در ستون موردنظر و برگرداندن مقداری در همان ردیف از ستونی دیگر است، در این حالت جستجوی یک طرفه کافی است اما اگر بخواهید آرایه‌ای که در آن جستجو می‌کنید نیز متغیر باشد، می‌توانید از XLOOKUP به صورت تو در تو استفاده کنید.
به فرمول زیر توجه کنید:
=XLOOKUP(F2,A2:A100,XLOOKUP(G1,B1:D1,B2:D100))
آرگومان سوم XLOOKUP در فرمول فوق، یک تابع XLOOKUPدیگر است. در واقع برای جستجو کردن مقدار موجود در سلول G1 در محدوده سلول‌های B1 الی D1 و سپس برگرداندن مقدار متناسب با آن از محدوده سلول‌های B2 الی D100 از XLOOKUP استفاده شده است.
در ادامه یک بار دیگر از XLOOKUP استفاده شده تا مقدار موجود در سلول F2 در محدوده‌ی A2 الی A100 جستجو شود و مقدار متناظر آن از آرایه‌ای که با جستجو کردن G1 پیدا شده، استخراج شود.
به عنوان مثال اگر بخواهید نمره دانش‌آموزان را با جستجو کردن سن پیدا کنید، می‌توانید عنوانی که در سلول G1 درج شده را به Age تغییر بدهید. به این ترتیب محدوده‌ای از سلول‌ها که جستجو می‌شود، ستون C خواهد بود.
یا مثال دیگر این است که اگر بخواهید نمرات افراد مذکر را پیدا کنید، می‌توانید عنوان درج شده در سلول G1 را به Gender تغییر دهید.
استفاده از منوی کرکره ای و XLOOKUP در اکسل
برای ساخت یک فایل حرفه‌ای‌تر و جستجوی سریع‌تر اطلاعات می‌توانید از لیست کشویی برای اشاره به ستون‌های داده استفاده کنید. به عنوان مثال به جای تایپ کردن عنوان ستون‌ها در سلول G1 مثال قبلی، می‌توانید سلول G1 را به منوی کرکره‌ای تبدیل کنید.
برای ساخت لیست کشویی در اکسل، ابتدا سلول موردنظر را انتخاب کنید و سپس از تب Data و سپس گزینه‌ی Data Validation استفاده کنید.
در پنجره‌ی باز شده از منوی Allow گزینه‌ی List را انتخاب کنید و سپس روی Source کلیک کنید. اکنون سلول‌های حاوی مقادیر مجاز که که عنوان ستون‌های داده است را انتخاب کنید.
در نهایت روی OK کلیک کنید. به این ترتیب هز زمان که بخواهید نوع جدیدی از اطلاعات افراد را جستجو کنید، به جای تایپ کردن عنوان ستون‌ها، می‌توانید روی فلش کنار سلول کلیک کنید و یکی از مقادیر را انتخاب کنید.
در صورت نیاز می‌توانید سایر اطلاعاتی که جستجو می‌کنید را نیز به لیست کشویی تبدیل کنید.
دقت کنید که اگر محدوده‌ی سلول‌ها گسترده باشد، لیست کشویی بسیار طولانی خواهد بود و با توجه به اینکه شاید داده‌ها ترتیب صعودی یا نزولی نداشته باشند، استفاده از لیست کشویی دشوار می‌شود. در این حالت روش تایپ کردن داده‌ی موردنظر گزینه‌ی بهتری است.
howtogeekسیاره‌ی آی‌تی