XLOOKUP в Excel: синтаксис, приклади і чим він кращий за VLOOKUP
By Mirko Peters · · Updated
XLOOKUP в Excel пояснено: синтаксис, кожен аргумент, приклади для точного, шаблонного та останнього збігу, кількох стовпців і чим він відрізняється від VLOOKUP.
Automatically translated with AI from the original post. Read the English original
XLOOKUP в Excel знаходить значення в одному діапазоні та повертає відповідний елемент з іншого діапазону: =XLOOKUP(lookup_value, lookup_array, return_array). Введіть ідентифікатор співробітника, вкажіть стовпець ідентифікаторів, вкажіть стовпець імен — і отримаєте ім’я. Ця стаття для користувачів Excel, які ще за звичкою пишуть VLOOKUP або щойно побачили XLOOKUP у файлі колеги. Ви отримаєте синтаксис, шість аргументів, покрокові приклади для типових робочих ситуацій, порівняння з VLOOKUP і перелік помилок, з якими ймовірно зіткнетеся.
Головне
- XLOOKUP приймає значення для пошуку, діапазон пошуку і діапазон повернення: =XLOOKUP(lookup_value, lookup_array, return_array). Вона знаходить перший збіг і повертає елемент з діапазону повернення.
- Вона може шукати ліворуч від стовпця пошуку і типово використовує точний збіг — двох речей, яких VLOOKUP не вміє або за замовчуванням не робить.
- Четвертий аргумент, if_not_found, замінює помилку #N/A текстом, який ви обираєте. Без нього незнайдений пошук повертає #N/A.
- match_mode 2 вмикає шаблони з символами-замінниками, -1 і 1 знаходять наступний менший або більший елемент, а search_mode -1 шукає від останнього елемента до першого.
- На сторінці Microsoft XLOOKUP зазначено для Microsoft 365, 2021 та 2024, а її примітки щодо Excel 2016 і 2019 суперечать одна одній, тож перевіряйте спільні файли, перш ніж на неї покладатися.
Синтаксис і параметри нижче відповідають сторінці Microsoft про функцію XLOOKUP. Де шаблону немає на цій сторінці, у тексті про це сказано. Це функція для Excel, і саме її Microsoft сама пропонує як кращий вибір, коли ви відкриваєте довідку про VLOOKUP.
Синтаксис і шість аргументів
Повна формула виглядає так:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Перші три аргументи обов’язкові. Останні три — необов’язкові.
lookup_value— це те, що ви шукаєте, наприклад ідентифікатор у клітинці D2. Якщо його пропустити, XLOOKUP поверне порожні клітинки, які знайде в lookup_array.lookup_array— це діапазон або масив для пошуку, наприклад A2:A100.return_array— це діапазон або масив, з якого повертається результат, наприклад B2:B100.if_not_found— це текст, який показується, коли дійсного збігу немає. Якщо його пропустити, ви отримаєте #N/A.match_modeзадає, як працює збіг.0— точний збіг і значення за замовчуванням.-1означає точний збіг або, якщо його немає, наступний менший елемент.1означає точний збіг або наступний більший елемент.2— збіг із шаблоном, де*,?і~мають спеціальне значення.search_modeзадає напрямок пошуку.1шукає від першого елемента до останнього і є значенням за замовчуванням.-1шукає від останнього до першого.2і-2— двійковий пошук за даними, відсортованими за зростанням або спаданням. Microsoft попереджає, що несортовані дані у двійкових режимах дають некоректні результати.
Тримайте масив пошуку і масив результатів однакового розміру. Якщо масив пошуку охоплює рядки з 2 по 100, масив результатів також має охоплювати рядки з 2 по 100. Сторінка Microsoft прямо не формулює це як правило, але невідповідні діапазони найчастіше є причиною того, що пошук повертає не той рядок або помилку.
Базові пошуки та аргумент для випадку, коли значення не знайдено
Припустімо, ідентифікатори працівників знаходяться в A2:A100, а імена в B2:B100. Ви вводите потрібний ідентифікатор у D2.
- Клацніть комірку, де має з'явитися результат, наприклад E2.
- Введіть
=XLOOKUP(D2,A2:A100,B2:B100). - Натисніть Enter.
Ви отримаєте ім'я, що відповідає ідентифікатору. XLOOKUP повертає елемент для першого знайденого збігу. Якщо ідентифікатора немає, ви побачите #N/A.
Щоб показати щось зрозуміліше, додайте четвертий аргумент:
=XLOOKUP(D2,A2:A100,B2:B100,"Запис не знайдено")
Тепер ідентифікатор, якого немає в списку, показує «Запис не знайдено». Використовуйте це на аркушах, які читають інші люди. Одне застереження: таке повідомлення може приховувати реальну проблему. Якщо в кожному рядку написано «Запис не знайдено», справа в діапазонах, а не в даних, тому спочатку перевірте відомий ідентифікатор.
Пошук ліворуч
VLOOKUP не може повернути значення зі стовпця ліворуч від стовпця пошуку. XLOOKUP може, адже діапазони пошуку і результату є окремими. Якщо імена знаходяться в стовпці C, а потрібний ідентифікатор – у стовпці A:
=XLOOKUP(F2,C2:C100,A2:A100)
На сторінці Microsoft це сказано прямо: стовпець пошуку може бути з будь-якого боку від стовпця результату.
Режими збігу: шаблони та найближчі значення
Збіги із шаблонами
Встановіть match_mode у 2, щоб використовувати шаблонні символи. Зірочка означає будь-яку кількість символів, а знак питання – рівно один символ. Тильда дозволяє шукати буквальну зірочку або знак питання.
=XLOOKUP("Jan*",B2:B100,A2:A100,"Не знайдено",2)
Ця формула повертає перший запис, що починається з «Jan». Якщо пропустити 2, Excel шукатиме буквальний текст «Jan*» і поверне ваше повідомлення про відсутність. Це найпоширеніша помилка з шаблонами. Робіть шаблони якомога конкретнішими, адже XLOOKUP повертає лише перший збіг, і занадто загальний шаблон може знайти не те, що ви мали на увазі.
Наступне менше або більше значення
Режими -1 і 1 призначені для шкал: податкових ставок, рівнів комісійних, цінових порогів. Розмістіть пороги в A2:A6, ставки в B2:B6 і шукайте дохід у E2:
=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,,-1)
При -1 Excel повертає точний збіг або найближчий менший поріг, якщо точного немає. Порожнє місце перед -1 пропускає аргумент для випадку, коли значення не знайдено. У власному прикладі Microsoft для наближеного збігу використовується 1, щоб повернути податкову ставку для найближчого більшого значення доходу, тож обирайте напрямок, який відповідає вашій таблиці. На відміну від старого наближеного VLOOKUP, для цих режимів пороги не потрібно сортувати. Бінарні режими пошуку потребують відсортованих даних.
Пошук з кінця: останній збіг
Коли значення трапляється кілька разів, XLOOKUP повертає перший, який знаходить. Щоб отримати останній, виконайте пошук з кінця, встановивши search_mode у -1:
=XLOOKUP(D2,A2:A100,C2:C100,"Не знайдено",0,-1)
Якщо аркуш містить замовлення у хронологічному порядку, ця формула повертає останній запис для клієнта. Параметр 0 забезпечує точний збіг. Це шаблон для запитань «остання ціна», «останній контакт» та подібних, які з VLOOKUP було складно реалізувати.
Повернення кількох стовпців одночасно
На сторінці Microsoft зазначено, що на відміну від VLOOKUP, XLOOKUP може повертати кілька стовпців як один масив. Розширте діапазон результату:
=XLOOKUP(D2,A2:A100,B2:D100)
Формула поміщає збіг зі стовпця B у комірку, а значення зі стовпців C і D виводить у комірки праворуч. Залиште для них місце. Якщо комірки праворуч уже містять дані, Excel показує помилку переповнення замість того, щоб їх перезаписати. У прикладі Microsoft одна формула повертає ім'я працівника та його відділ.
Двосторонній пошук
Microsoft показує, як вкладати дві функції XLOOKUP, щоб знайти значення на перетині рядка і стовпця. Шаблон зі сторінки:
=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))
Внутрішня функція вибирає стовпець, що відповідає заголовку в C3. Зовнішня функція потім шукає D2 серед назв рядків і повертає значення з цього стовпця. Microsoft порівнює це з комбінацією INDEX і MATCH. Якщо у вас звіт, де товари розташовані в лівому стовпці, а місяці — у заголовках зверху, це заміна для складнішої формули.
Кілька критеріїв
XLOOKUP приймає одне значення для пошуку, а на сторінці Microsoft немає прикладу з кількома критеріями. Поширений підхід — дозволити Excel перевірити дві умови одночасно. Припустімо, категорія міститься в A, статус — у B, а ціна — у C. Щоб знайти ціну для категорії в F2 і статусу в G2:
=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found")
Кожне порівняння повертає TRUE або FALSE. Їх добуток дає 1 лише в тих рядках, де обидві умови істинні, а XLOOKUP шукає перше 1. Цей підхід працює в поточних версіях Microsoft 365, але його немає на сторінці функції Microsoft, тож перевірте його на невеликій вибірці. Допоміжний стовпець, що об'єднує два поля, — це старіша альтернатива. Між значеннями поставте роздільник, наприклад =A2&"|"&B2, і шукайте той самий об'єднаний текст.
XLOOKUP і VLOOKUP поруч
На сторінці VLOOKUP Microsoft подає синтаксис як VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) і зазначає, що значення для пошуку має бути в першому стовпці діапазону. Якщо пропустити range_lookup, VLOOKUP виконає наближений пошук, який припускає, що перший стовпець відсортовано. Встановіть FALSE для точного збігу. Та сама сторінка радить спробувати XLOOKUP і зазначає, що він працює в будь-якому напрямку та за замовчуванням повертає точні збіги.
Одне й те саме завдання в обох функціях, з ідентифікаторами в стовпці A та іменами в стовпці B:
=VLOOKUP(D2,A2:B100,2,FALSE)=XLOOKUP(D2,A2:A100,B2:B100)
Відмінності, які важливі в повсякденній роботі:
- VLOOKUP посилається на результат за номером стовпця, тож вставка стовпця в таблицю змінює те, що означає це число. XLOOKUP посилається на діапазон результатів напряму.
- VLOOKUP не може шукати ліворуч. XLOOKUP може.
- За замовчуванням VLOOKUP шукає наближений збіг. За замовчуванням XLOOKUP шукає точний.
- XLOOKUP має вбудований аргумент для випадку, коли нічого не знайдено, і напрямок пошуку.
Старі книги переписувати не обов'язково. Якщо VLOOKUP працює, залиште його. Використовуйте XLOOKUP у нових формулах і для пошуків, яким потрібен пошук ліворуч, останній збіг або кілька стовпців результату.
Які версії Excel підтримують XLOOKUP
Тут потрібна обережність. На сторінці Microsoft XLOOKUP перелічено для Excel для Microsoft 365 у Windows і Mac, Excel 2024, Excel 2021, а також у застосунках для iPad, iPhone та Android. Примітка на сторінці каже, що його немає в Excel 2016 чи 2019, але він може з'являтися в книгах, створених у новіших версіях, а в рядку «Стосується» також згадано 2016 і 2019. Сторінка суперечить сама собі, тож не вважайте Excel 2016 і 2019 безпечними. Excel для веб на цій сторінці також не згаданий.
Якщо ви надсилаєте книгу комусь зі старішою версією, формула може показати помилку там, де мала б показати значення. Уточніть, яка версія є у ваших одержувачів, а для файлів, які надходять багатьом людям, перевіряйте на найстарішій версії, що використовується. Якщо перевірити не можете, для такого файлу використовуйте класичний пошук.
Типові помилки та несправності
- Забули
match_mode2 під час використання шаблонів. Тоді пошук шукає буквальні символи. - Використання двійкового
search_mode(2 або -2) на несортованих даних. Microsoft зазначає, що результати будуть недійсними. - Діапазони пошуку та результату різної довжини. Зробіть їх однаковими за довжиною.
- Приховані пробіли та числа, збережені як текст. «100» і 100 — це різні значення, тож пошук не вдається, і ви бачите #N/A. Очистіть дані за допомогою TRIM або перетворіть числа.
- Приховування проблем дружнім повідомленням про відсутність результату. Перевірте одне відоме значення, перш ніж покладатися на формулу.
- Спрямування масиву результатів у клітинки, де результат має розширюватися, і залишення їх зайнятими.
- Посилання на весь стовпець у дуже великих аркушах. Використовуйте обмежений діапазон або таблицю Excel, щоб Excel обробляв менше клітинок.
Пов'язані теми для вивчення
- XLOOKUP проти INDEX і MATCH. Обидві пари дозволяють пошук ліворуч, а комбінація INDEX і MATCH працює і в старіших версіях Excel. Почитайте про це, якщо ви ділитеся файлами з людьми, які користуються Excel 2016 або 2019.
- Кілька результатів. XLOOKUP повертає лише перший збіг. Щоб отримати всі збіги, розгляньте функцію FILTER.
- XLOOKUP з таблицями Excel. Структуровані посилання роблять діапазони такими, що зростають разом із даними, і їх легко читати у формулах.
- Дані з Power Query. Після завантаження даних до таблиці XLOOKUP може шукати в ній так само, як в будь-якій іншій таблиці. Спершу оновіть запит, а потім перевірте результати.
- Excel для вебу. На сторінці Microsoft про XLOOKUP цей застосунок не згадано, тож перед тим, як надавати файл колегам, перевірте у своєму середовищі, чи працює функція для них.
Наступний крок
Візьміть одну формулу VLOOKUP з поточної книги та перепишіть її через XLOOKUP. Додайте повідомлення про відсутність значення і порівняйте два результати на кількох відомих вам ідентифікаторах. Якщо обидва збігаються, ви отримаєте шаблон, який можна повторно використовувати для кожного нового XLOOKUP в Excel.