آشنائی با فرمول XLOOKUP در گوگل شیت

در این مطلب در مورد فرمول XLOOKUP  از گروه فرمول های LOOKUP در نرم افزار گوگل شیت Google Sheets صحبت خواهیم کرد.

فرمول XLOOKUP برای جستجوی یک مقدار در یک بازه و نمایش مقادیر متناظر آن مقدار، بکار می رود. این فرمول تا حد زیادی شبیه به فرمول های VLOOKUP و HLOOKUP می باشد اما تفاوت هایی هم با این فرمول ها دارد؛

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

فرمول XLOOKUP دارای چندین ورودی به شرح زیر است؛

search_key: مقدار مورد جستجو

lookup_range: بازه مورد نظر جهت جستجو

result_range: بازه ای که باید مقادیر متناظر در آن نمایش داده شود

missing_value : عبارت دلخواه در صورت عدم وجود مقدار مورد جستجو

match_mode: این گزینه اختیاری بوده و دارای 4 حالت زیر است؛

0: دقیقا عبارت مورد نظر را جستجو خواهد کرد (پیش فرض)

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

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

2: جستجو را بصورت Wildcard انجام می دهد.

search_mode: این گزینه اختیاری بوده و دارای 4 حالت به شرح زیر است؛

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

-1: جستجو از آخرین داده به اولین داده

2: جستجوی باینری با در نظر گرفتن اینکه داده ها به ترتیب صعودی مرتب هستن

-2: جستجوی باینری با در نظر گرفتن اینکه داده ها به ترتیب نزولی مرتب هستن

خروجی فرمول XLOOKUP می تواند مقدار یک سلول یا محدوده ای سلول ها باشد.

فرض کنید جدولی از اطلاعات یک فروشگاه به شرح زیر داریم که در آن محصولات فروخته شده براساس جنسیت خریدار، شهر و گروه کالا لیست شده اند، می خواهیم در این جدول با استفاده از فرمول XLOOKUP گروه کالایی برای مبلغ 145.000 را بدست بیاوریم؛

همانطور که می بینید چون مقدار 145.000 در ستون Price وجود ندارد بنابراین مقدار not found را (که در فرمول تعریف کرده بودیم) نمایش می دهد، حالا اگر مقدار match_mode را 1 قرار دهیم، خروجی فرمول به شکل زیر خواهد بود؛

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

اگر در فرمول XLOOKUP بجای انتخاب یک ستون یا یک ردیف در result_range، چند ستون یا چند ردیف را انتخاب کنیم، خروجی فرمول مقدار متناظر با تمام ستون ها یا ردیف ها را نمایش خواهد داد، مانند تصویر زیر؛

همچنین می توانیم در فرمول XLOOKUP از کارکترهای وایلدکارد استفاده کنیم، مانند تصویر زیر؛

برای مشاهده سایر توابع گروه Lookup در گوگل شیت می‌توانید به لینک زیر مراجعه کنید؛