Повний гід по XLOOKUP — чому вона краща за VLOOKUP, синтаксис, що спотикає нових користувачів, і розширені прийоми, що роблять її найпотужнішою функцією Excel.
- XLOOKUP — сучасна заміна VLOOKUP, HLOOKUP та INDEX/MATCH: одна функція для кожного сценарію пошуку.
- Синтаксис:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])— точне співпадіння за замовчуванням, без крихких індексів стовпців. - Доступна в Excel 365 і Excel 2021+, а також у Google Таблицях з 2022 року.
- Пошук у будь-якому напрямку, повернення цілих рядків, вбудована обробка помилок — виправляє всі слабкості VLOOKUP з 1985.
Microsoft запровадив XLOOKUP у 2019, щоб виправити все, що не так у VLOOKUP. П'ять років потому це явно правильний стандарт для кожної нової формули Excel у сучасних версіях.
Чому XLOOKUP існує
VLOOKUP з'явився у 1985 із трьома вбудованими обмеженнями: він міг шукати лише зліва направо, посилався на стовпці за позицією (тому вставлення стовпця ламало всі формули), і стандартний тип відповідності був приблизним (тому забуття аргументу давало тихо неправильні результати). XLOOKUP — це чистий редизайн: одна функція для всіх сценаріїв пошуку з безпечнішими стандартами та чистішим синтаксисом.
Синтаксис простою мовою
| Аргумент | Що робить | Примітки |
|---|---|---|
| lookup_value | Що шукаєте | Посилання на клітинку або буквальне значення |
| lookup_array | Стовпець/рядок для пошуку | Один стовпець або рядок, будь-який напрямок |
| return_array | Що повертати при знаходженні | Один стовпець, рядок або весь діапазон |
| [if_not_found] | Що повертати при відсутності збігу | Замінює загортання в IFERROR |
| [match_mode] | 0 = точне (за замовчуванням), -1/1 = приблизне, 2 = підстановочний знак | Майже завжди залишайте за замовчуванням |
| [search_mode] | 1 = від першого до останнього (за замовчуванням), -1 = від останнього до першого | Використовуйте -1 для пошуку останнього збігу |
Базові приклади, що охоплюють 90% випадків
Знайти зарплату за іменем, де імена у стовпці A, зарплати у B:
=XLOOKUP("Іван", A:A, B:B)
З дружнім повідомленням замість #N/A:
=XLOOKUP("Марія", A:A, B:B, "Немає в списку")
Випадок, який VLOOKUP фізично не може вирішити — пошук справа наліво:
=XLOOKUP("Старший", D:D, A:A)
XLOOKUP vs VLOOKUP поруч
| Критерій | XLOOKUP | VLOOKUP |
|---|---|---|
| Тип відповідності за замовчуванням | Точне | Приблизне (небезпечно!) |
| Напрямок пошуку | Будь-який (вліво, вправо, вгору, вниз) | Лише зліва направо |
| Посилання на стовпець | За масивом (захищено від вставлення) | За номером (ламається при вставленні) |
| Обробка помилок | Вбудований аргумент if_not_found | Потрібна обгортка IFERROR |
| Швидкість на великих даних | Швидше | Повільніше |
| Версії Excel | 365, 2021+ | Всі версії |
Розширені випадки, за які варто любити функцію
Пошук з підстановочним знаком
Знайти імена, що містять "Іван" — встановіть match_mode на 2:
=XLOOKUP("*Іван*", A:A, B:B, "", 2)
Останній збіг (зворотний пошук)
Знайти останній запис Івана — корисно для журналів транзакцій. Встановіть search_mode на -1:
=XLOOKUP("Іван", A:A, B:B, "", 0, -1)
Двовимірний пошук (замінює INDEX/MATCH)
Знайти продажі Q3 для продукту X вкладенням двох XLOOKUP:
=XLOOKUP("X", A:A, XLOOKUP("Q3", B1:E1, B:E))
Повернення цілого рядка
Отримати всі дані для імені однією формулою:
=XLOOKUP("Іван", A:A, A:E)
Поширені помилки та виправлення
| Помилка | Причина | Виправлення |
|---|---|---|
| #N/A | Значення пошуку не знайдено | Додайте аргумент if_not_found: XLOOKUP(...,..., "Не знайдено") |
| #VALUE! | Масиви різних розмірів | Переконайтесь, що lookup_array і return_array мають однакову кількість клітинок |
| #NAME? | XLOOKUP недоступна у вашій версії Excel | Оновіться до 365/2021+ або використовуйте INDEX/MATCH для сумісності |
FAQ
Чому перейти з VLOOKUP на XLOOKUP?
Три причини, що підсилюють одна одну: безпечніші стандарти (точне збіг за замовчуванням позбавляє тихих неправильних результатів), більша гнучкість (пошук у будь-якому напрямку, вбудована обробка помилок), краща продуктивність (2–3 рази швидше на великих даних).
Чи є XLOOKUP у Google Таблицях?
Так, з 2022. Синтаксис ідентичний Excel XLOOKUP. Якщо ви працюєте в обох, можна використовувати однакові формули.
Що якщо у мене Excel 2019?
XLOOKUP недоступна — використовуйте VLOOKUP або INDEX/MATCH натомість. INDEX/MATCH дає більшу частину гнучкості XLOOKUP і працює в будь-якій версії Excel.
Підсумок
XLOOKUP — те, чим мав бути VLOOKUP від початку. Безпечніші стандарти, чистіший синтаксис, краща продуктивність і гнучкість для будь-якого сценарію пошуку в одній функції. Якщо у вас Excel 365 або 2021+, за замовчуванням використовуйте XLOOKUP для всіх нових формул.
- XLOOKUP замінює VLOOKUP, HLOOKUP і більшість INDEX/MATCH.
- Точне збіг за замовчуванням — позбавляє тихих неправильних результатів VLOOKUP.
- Пошук у будь-якому напрямку — вліво, вправо, вгору, вниз.
- Вбудований аргумент if_not_found замінює загортання в IFERROR.
- Доступна в Excel 365, 2021+ та Google Таблицях.
Одне посилання для контенту та навчальних матеріалів Excel
Додайте URL UniLink до свого біо — показуйте навчальні матеріали, курси, блог. Безкоштовно.