Office 365
XLOOKUP vs ВПР в Excel — почему пора переходить
Подробное сравнение XLOOKUP и ВПР (VLOOKUP): 6 практических примеров с формулами, таблица совместимости, шпаргалка по переходу. Узнайте, почему XLOOKUP лучше.
Зачем менять ВПР на XLOOKUP?
Функция ВПР (VLOOKUP) появилась в Excel в 1985 году. За почти 40 лет она стала самой узнаваемой формулой — но и самой проблемной. В 2019 году Microsoft выпустила замену: XLOOKUP. Она решает все недостатки ВПР и заменяет сразу три функции: ВПР, ГПР и связку ИНДЕКС+ПОИСКПОЗ.
Проблемы ВПР
- Поиск только вправо — если нужный столбец левее искомого, ВПР бессильна
- Хрупкий номер столбца — вставили столбец в середину таблицы — все формулы сломались
- Приблизительный поиск по умолчанию — забыли
ЛОЖЬв конце — получили неверный результат - Одно значение за раз — для трёх столбцов нужно три формулы
- Медленная на больших данных — последовательный перебор тормозит на десятках тысяч строк
Что умеет XLOOKUP
- Поиск в любом направлении — влево, вправо, вверх, вниз
- Ссылка на диапазон — вставка столбцов ничего не сломает
- Точный поиск по умолчанию — не нужно помнить про
ЛОЖЬ - Встроенная обработка ошибок — без обёртки в ЕСЛИОШИБКА
- Несколько столбцов одной формулой — возврат массива значений
- Поиск с конца и бинарный поиск — для логов и больших таблиц
Синтаксис
ВПР — 4 аргумента
=ВПР(что_ищем; таблица; номер_столбца; тип)XLOOKUP — 3 основных
=XLOOKUP(что_ищем; где_искать; откуда_вернуть)Примеры
1. Простой поиск
Таблица: A — имя, B — отдел, C — зарплата. Найти зарплату Иванова:
ВПР: =ВПР("Иванов"; A1:C100; 3; ЛОЖЬ)
XLOOKUP: =XLOOKUP("Иванов"; A1:A100; C1:C100)В ВПР число 3 сломается при вставке столбца. В XLOOKUP — прямая ссылка на столбец C.
2. Поиск влево
Столбец A — код товара, B — название. Найти код по названию:
ВПР: невозможно (нужен ИНДЕКС+ПОИСКПОЗ)
XLOOKUP: =XLOOKUP("Молоко"; B1:B100; A1:A100)XLOOKUP просто меняет столбцы местами. Направление не важно.
3. Если значение не найдено
ВПР: =ЕСЛИОШИБКА(ВПР("Петров";A1:C100;3;ЛОЖЬ); "Не найден")
XLOOKUP: =XLOOKUP("Петров"; A1:A100; C1:C100; "Не найден")XLOOKUP — четвёртый аргумент вместо функции-обёртки.
4. Вернуть сразу несколько столбцов
ВПР: три отдельные формулы с номерами 2, 3, 4
XLOOKUP: =XLOOKUP("001"; A1:A100; B1:D100)Одна формула — три значения в соседних ячейках.
5. Последняя запись в журнале
Клиент встречается несколько раз. Нужна сумма последнего платежа:
=XLOOKUP("Иванов"; A1:A1000; C1:C1000; ; 0; -1)Аргумент -1 — поиск с конца. ВПР всегда находит только первое совпадение.
6. Подстановочные знаки
Найти клиента, чьё имя начинается на «Иван»:
=XLOOKUP("Иван*"; A1:A100; B1:B100; ; 2)Аргумент 2 включает подстановочные знаки: * — любые символы, ? — один символ.
Совместимость
| Версия | XLOOKUP | Примечание |
|---|---|---|
| Excel 2016 | ❌ | Используйте ИНДЕКС+ПОИСКПОЗ |
| Excel 2019 | ❌ | Используйте ИНДЕКС+ПОИСКПОЗ |
| Excel 2021 | ✅ | Полная поддержка |
| Excel 2024 | ✅ | Полная поддержка |
| Microsoft 365 | ✅ | Полная поддержка |
| Google Таблицы | ✅ | С 2022 года |
#ИМЯ?.Шпаргалка по переходу
| Было (ВПР) | Стало (XLOOKUP) |
|---|---|
=ВПР(A1;D:F;2;ЛОЖЬ) | =XLOOKUP(A1;D:D;E:E) |
=ВПР(A1;D:F;3;ЛОЖЬ) | =XLOOKUP(A1;D:D;F:F) |
=ЕСЛИОШИБКА(ВПР(...);"—") | =XLOOKUP(A1;D:D;E:E;"—") |
=ИНДЕКС(C:C;ПОИСКПОЗ(...)) | =XLOOKUP(A1;D:D;C:C) |
Когда оставить ВПР?
- Файлы должны открываться в Excel 2019 и старше
- Корпоративные шаблоны стандартизированы на ВПР
- Обучение новичков — ВПР проще как первая функция поиска
