یکی از رایج‌ترین کارها در اکسل، جستجوی مقادیر در یک جدول بر اساس معیارهای خاص است. برای مثال، ممکن است بخواهید قیمت یک محصول، نام یک مشتری یا مقدار فروش برای یک ماه مشخص را پیدا کنید. برای این منظور در اکسل فرمول‌های فراوانی وجود دارد، از جمله توابع LOOKUP، HLOOKUP، VLOOKUP، INDEX و MATCH(همگی از اکسل 2003 به بعد ظاهر شدند) و ورژن‌ جدیدتر XLOOKUP (از 2021 به بعد ظاهر شد) بهترین توابع برای جستجو هستند، در گذشته(تا پیش از 2021)، کاربران اکسل برای این کار(جستجو) به تابع VLOOKUP تکیه می‌کردند. با این حال، VLOOKUP محدودیت‌های خاص خود را داشت(و دارد) که باعث شد علاقه‌مندان به اکسل راه‌حلی مقاوم‌تر با استفاده از ترکیب INDEX و MATCH ایجاد کنند(خود من همیشه از این ترکیب استفاده می‌کنم و به ندرت به سراغ VLOOKUP می‌روم). اکنون، با ظهور نسخه‌های جدیدتر، تابع XLOOKUP به عنوان جانشینی قدرتمندتر و همه‌کاره‌تر برای VLOOKUP معرفی شده است(برای استفاده از آن نیاز به نصب آفیس ورژن 2021و یا بالاتر دارید). در ادامه به ویژگی‌ها و ظرافت‌های این توابع بپردازم.

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

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

• lookup_value: مقداری که می‌خواهیم جستجو کنیم.
• table_array: جدولی(توجه کنید منظور ترکیبی از سطرها و ستون‌ها یک جدول را نشان می‌دهد) که در آن می‌خواهیم جستجو انجام دهیم.
• col_index_numو یا row_index_num: شماره ستون یا شماره ردیفی که می‌خواهیم پس از یافتن برگردانیم.
• range_lookup: یک مقدار اختیاری با مقادیر true(به معنی یافتن در هر بخشی از جستجو که بصورت پیش فرض است) یا false(به معنی جستجوی دقیق) می‌گیرد.
• lookup_array: آرایه‌ای که جستجو درون آن انجام می‌شود(در ادامه تفاوت این با table_array را خواهید فهمید).
• return_array: آرایه‌ای که نتیچه از درون آن برگشت داده شود.
• if_not_found: انتخابی و مقداری که درصورت عدم موفقیت در جستجو میخواهیم نمایش دهیم.
• match_mode: انتخابی و مقادیر 0, 1, -1  و 2 را دارا می‌باشد
• search_mode: انتخابی و مقادیر 1, -1, -2 و2 را دارا می‌باشد.
• reference: آرایه(یک یا دو بعدی) که میخواهیم از آن داده ای در سطر(row) یا ستون(column) خاصی را برگردانیم.
• search_key: کلید مورد نظر برای جستجو، مانند lookup_value می‌باشد.
• range: محدوده‌ای که جستجو در آن انجام می‌شود.
• search_type: انتخابی و روش جستجو را مشخص می‌کند.

در زیر چند دستور و مقداری که آن دستور بازمی‌گرداند(به همراه علت آن که سعی شده با تصویر و عکس بهتر فهمیده شود) نوشته می‌شود. برای فهم بهتر مطلب فرض کنید در ستون J مقدار برای جستجو و در ستون K فرمول نوشته شده‌است.

=VLOOKUP(100030,A1:G15,4)

این فرمول کد 100030 را در جدول(در اینجا A1:G15 دقت کنید این جدول دارای 7 ستون از یک تا عدد 7 و 15 ردیف از یک تا پانزده میباشد) جستجو می‌کند(نتیجه جستجو شماره ردیف را مشخص می‌کند) و سپس بر اساس شماره ردیف و شماره ستون(عدد 4 موجود در فرمول) مقدار را برمی‌گرداند(دقیقا محل تقاطع در تصویر زیر)

=VLOOKUP(100025,A1:G15,4,FALSE)

چون کد 100025 در جدول وجود ندارد و ما با استفاده از false میخواهیم دقیقا همین کد را بیابیم لذا تابع مقدار  #N⁄Aرا برمی‌گرداند.

=VLOOKUP(100025,A1:G15,4,TRUE)

مشابه بالایی با این تفاوت که ما می‌خواهیم نزدیکترین نتیجه برگشت داده‌شود(با مقدار true در فرمول) کد 100025 میان دو عدد 100020 و 100030 می‌باشد میان این دو کوچکترین عدد 100020 است لذا مقدار 4.28 که محل تقاطع ردیف سوم(ردیف مربوط به کد 100020) و ستون 4 می‌باشد، برگردانده می‌شود، مشابه آن است که ما کد 100020 را جستجو کرده‌باشیم.

در جستجو با مقدار true در اینگونه توابع بهتر است ستون یا ردیف جستجو، از کوچکترین مقدار به بزرگترین مقدار مرتب شده‌باشد در غیر این، ممکن است پاسخ نادرست باشد.

=HLOOKUP("M06",A1:G15,10)

در اینجا مقدار سلول J5 که M06 است را در جدول جستجو کرده(توجه کنید برخلاف دستور VLOOKUP که شماره ردیف را مشخص می‌کرد در HLOOKUP شماره ستون مشخص می‌گردد)شماره ستون(اینجا 7) را مشخص کرده براساس این شماره ستون و شماره ردیف 10 مقدار را برمی‌گرداند.

=HLOOKUP("M0",A1:G15,4,FALSE)

مقدار M0 در جدول موجود نیست لذا #N⁄A برمی‌گرداند.

=MATCH("M03",A1:G1,0)

از آنجایی که A1:G1 یک سطر را نشان می‌دهد لذا پس از جستجوی M03 و یافتن آن شماره ستون(عدد 4) را برمی‌گرداند.

=MATCH("M0",A1:G1,0)

مقدار M0 در جدول موجود نیست لذا #N⁄A برمی‌گرداند.

=MATCH(100050,A1:A15,0)

از آنجایی که A1:A15 یک ستون را نشان می‌دهد لذا پس از جستجوی 100050 و یافتن آن شماره سطر را برمی‌گرداند.

=INDEX(A1:G15,10,5)

از جدول ردیف 5 و ستون 10 را برمی‌گرداند.

=INDEX(A1:G15,MATCH(100090,A1:A15,0),MATCH("M03",A1:G1,0))

نخست ردیف مربوط به کد 100090 یافت شده سپس ستون مربوط به M03 یافت شده سپس براساس این دو مقدار برگشت داده میشود.

اما XLOOKUP که نیاز به مقاله‌ای جداگانه دارد، اما در ادامه توضیح داده‌می‌شود. 

تابع XLOOKUP(از اکسل 2021 به بعد) یک تابع قدرتمند برای جستجوی مقادیر در یک محدوده و بازگرداندن مقداری از محدوده دیگر است. این تابع جایگزین توابع قدیمی مانند VLOOKUP و HLOOKUP شده و انعطاف‌پذیری بیشتری دارد. در اینجا نحوه استفاده از XLOOKUP با تمام پارامترها و یک مثال بر اساس داده‌ها توضیح داده شده است.

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

1. lookup_value: مقداری که می‌خواهید جستجو کنید.
2. lookup_array: محدوده‌ای که می‌خواهید در آن جستجو کنید.
3. return_array: محدوده‌ای که می‌خواهید مقدار مربوطه از آن بازگردانده شود.
4. [if_not_found] (اختیاری): مقداری که در صورت عدم یافتن نتیجه بازگردانده شود. اگر این پارامتر مشخص نشود، تابع خطای #N/A را برمی‌گرداند.
5. [match_mode] (اختیاری): نوع تطابق:
   • 0 (پیش‌فرض): تطابق دقیق.
   • -1: تطابق دقیق یا کوچک‌ترین مقدار کمتر از lookup_value.
   • 1: تطابق دقیق یا بزرگ‌ترین مقدار بیشتر از lookup_value.
   • 2: تطابق با کاراکترهای عمومی (*, ?, ~).
6. [search_mode] (اختیاری): جهت جستجو:
   • 1 (پیش‌فرض): جستجو از اولین تا آخرین عنصر.
   • -1: جستجو از آخرین تا اولین عنصر.
   • 2: جستجوی دودویی (برای داده‌های مرتب‌شده صعودی).
   • -2: جستجوی دودویی (برای داده‌های مرتب‌شده نزولی).

مثال 1: فرض کنید می‌خواهید مقدار مربوط به کد آیتم 100050 را از ستون M01 پیدا کنید(7.44)

=XLOOKUP(100050, A2:A15, B2:B15)

مثال 2: این فرمول "Not Found" را برمی‌گرداند زیرا کد آیتم 100055 وجود ندارد.

=XLOOKUP(100055, A2:A15, B2:B15, "Not Found")

مثال 3: این فرمول مقدار مربوط به کد آیتم 100020 را برمی‌گرداند زیرا 100025 وجود ندارد و کوچک‌ترین مقدار کمتر از آن 100020 است(5.87).

=XLOOKUP(100025, A2:A15, B2:B15, , -1)

مثال 4: فرض کنید می‌خواهید عنوان ستونی را پیدا کنید که با M0 شروع می‌شود و هر تعداد کاراکتر بعدی دارد.

=XLOOKUP("M0*", A1:G1, A1:G1, , 2)

• "M0*": الگویی که با M0 شروع می‌شود و هر تعداد کاراکتر بعدی را شامل می‌شود.
• A1:G1: محدوده‌ای که در آن جستجو می‌شود (سطر اول، عنوان ستون‌ها).
• A1:G1: محدوده‌ای که مقدار مربوطه از آن بازگردانده می‌شود (همان سطر اول).
• 2: نشان‌دهنده‌ی تطابق با کاراکترهای عمومی.

این فرمول اولین عنوان ستون که با M0 شروع می‌شود را برمی‌گرداند، یعنی M01.

مثال 5: فرض کنید می‌خواهید عنوان ستونی را پیدا کنید که دقیقاً ۳ کاراکتر دارد و با M شروع می‌شود.

=XLOOKUP("M??", A1:G1, A1:G1, , 2)

• "M??" : الگویی که با M شروع می‌شود و دقیقاً دو کاراکتر بعدی دارد.
• A1:G1: محدوده‌ای که در آن جستجو می‌شود (سطر اول، عنوان ستون‌ها).
• A1:G1: محدوده‌ای که مقدار مربوطه از آن بازگردانده می‌شود (همان سطر اول).
• 2: نشان‌دهنده‌ی تطابق با کاراکترهای عمومی.

این فرمول اولین عنوان ستون که با M شروع می‌شود و دقیقاً ۳ کاراکتر دارد را برمی‌گرداند، یعنی M01.

•  توابع جستجو در اکسل (12.38 کیلو بایت)