12 ключевых преимуществ функции ПРОСМОТРХ (XLOOKUP) в Excel

Функция ПРОСМОТРХ (XLOOKUP), представленная в Microsoft 365 в ноябре 2019 года, стала революционным обновлением для поиска данных в Excel. Разработанная Джо Макдейдом из команды Excel, она объединила лучшие черты ВПР (VLOOKUP) и ИНДЕКС (INDEX)/ПОИСКПОЗ (MATCH), предлагая более гибкий и надежный инструмент для работы с данными.

Синтаксис функции ПРОСМОТРХ (XLOOKUP)

Основной синтаксис функции выглядит следующим образом:

=ПРОСМОТРХ(искомое_значение; массив_поиска; массив_возврата; [если_не_найдено]; [режим_сопоставления]; [режим_поиска]) (=XLOOKUP(искомое_значение; массив_поиска; массив_возврата; [если_не_найдено]; [режим_сопоставления]; [режим_поиска]))

12 преимуществ ПРОСМОТРХ (XLOOKUP) перед старыми функциями

Точное совпадение по умолчанию — больше не нужно указывать ЛОЖЬ (FALSE) или 0 в конце формулы.
Ссылка вместо номера столбца — третий аргумент является ссылкой на диапазон, а не числом, что делает формулу устойчивой к вставке или удалению столбцов.
Встроенная обработка ошибок — четвертый аргумент [если_не_найдено] позволяет сразу указать значение или текст при отсутствии совпадения.
Поиск слева от ключевого поля — ПРОСМОТРХ (XLOOKUP) легко возвращает значения из столбцов, расположенных левее столбца поиска.
Поиск ближайшего меньшего или большего значения без сортировки — используйте аргумент [режим_сопоставления] со значениями -1 или 1.
Замена ГПР (HLOOKUP) — работает с горизонтальными массивами данных.
Поиск последнего совпадения — аргумент [режим_поиска] позволяет начать поиск с конца списка.
Подстановочные знаки отключены по умолчанию — для их использования нужно явно указать режим сопоставления 2.
Возврат нескольких значений в одной формуле — благодаря динамическим массивам можно вернуть целый диапазон (например, все 12 месяцев).
Одна формула для всего столбца — указав диапазон в качестве искомого_значения, можно выполнить массовый поиск.
Возврат ссылки на ячейку — если ПРОСМОТРХ (XLOOKUP) стоит рядом с оператором (например, двоеточием), он возвращает адрес ячейки, а не её значение.
Двунаправленный поиск — позволяет выполнять поиск и по строкам, и по столбцам, заменяя конструкцию ИНДЕКС (INDEX)(,ПОИСКПОЗ (MATCH)(),ПОИСКПОЗ (MATCH)()).

Практические примеры и советы

Рассмотрим несколько ключевых сценариев, где ПРОСМОТРХ (XLOOKUP) демонстрирует свою мощь.

Преимущество 1: Точное совпадение по умолчанию

99% формул ВПР (VLOOKUP) заканчивались на ,ЛОЖЬ (FALSE). В ПРОСМОТРХ (XLOOKUP) точное совпадение установлено по умолчанию, что упрощает запись.

Пример формулы XLOOKUP с поиском значения из ячейки A4 в диапазоне L8:L35 и возвратом цены из столбца N. — Пример формулы ПРОСМОТРХ (XLOOKUP) с поиском значения из ячейки A4 в диапазоне L8:L35 и возвратом цены из столбца Ч (N).

Преимущество 2: Устойчивость к изменению структуры таблицы

Поскольку третий аргумент — это ссылка (например, N8:N35), а не номер столбца, формула автоматически адаптируется при вставке новых столбцов.

Сравнение уязвимости VLOOKUP и устойчивости XLOOKUP к вставке столбцов в таблице. — Сравнение уязвимости ВПР (VLOOKUP) и устойчивости ПРОСМОТРХ (XLOOKUP) к вставке столбцов в таблице.

Преимущество 3: Встроенная обработка ошибки #Ч (N)/A

Четвертый аргумент позволяет элегантно обработать ситуацию, когда значение не найдено.

=ПРОСМОТРХ(А2; $Г$2:$Г$100; $Д$2:$Д$100; "Не найдено") (=XLOOKUP(A2; $D$2:$D$100; $E$2:$E$100; "Не найдено"))

Использование аргумента 'если не найдено' для замены ошибки #N/A на 0 или текст. — Использование аргумента 'если не найдено' для замены ошибки #Ч (N)/A на 0 или текст.

Преимущество 5: Поиск без сортировки таблицы

Используйте [режим_сопоставления] для поиска ближайшего меньшего (-1) или большего (1) значения. Таблицу при этом сортировать не нужно.

Пример поиска подходящего транспортного средства по количеству пассажиров с использованием match_mode=1.

Преимущество 9 и 10: Динамические массивы и массовый поиск

Сила ПРОСМОТРХ (XLOOKUP) раскрывается в полной мере вместе с динамическими массивами.

Возврат диапазона: Одна формула может вернуть все 12 месяцев, если указать прямоугольный массив_возврата.
Массовый поиск: Указав вертикальный диапазон в качестве искомого_значения, вы получите результаты для всего списка одной формулой.

Одна формула XLOOKUP в ячейке C4 возвращает ставки для всего списка имен из B4:B14. — Одна формула ПРОСМОТРХ (XLOOKUP) в ячейке C4 возвращает ставки для всего списка имен из B4:B14.

Заключение

Функция ПРОСМОТРХ (XLOOKUP) — это современный, мощный и интуитивно понятный инструмент для поиска данных в Excel. Она не только устраняет основные недостатки ВПР (VLOOKUP) и ГПР (HLOOKUP), но и открывает новые возможности благодаря интеграции с динамическими массивами. Освоение ПРОСМОТРХ (XLOOKUP) — обязательный шаг для любого, кто хочет эффективно работать с данными в современных версиях Excel 365.

Начните заменять свои старые формулы ВПР (VLOOKUP) на ПРОСМОТРХ (XLOOKUP), чтобы сделать таблицы более надежными, читаемыми и функциональными.

12 ключевых преимуществ функции XLOOKUP в Excel