Как функция ПРОСМОТРX ищет данные слева, справа и с конца таблицы

Справочник редко устроен так, как удобно формуле: коды стоят посередине, цены слева, а свежие записи дописываются в конец. Справка Microsoft описывает ПРОСМОТРX как функцию, которая ищет в любом направлении и по умолчанию требует точного совпадения^[2].

Путь к рабочей формуле с функцией ПРОСМОТРX короткий: найти столбец с ключом и столбец с ответом, ввести три обязательных аргумента, проверить результат на заведомо отсутствующем ключе, исправить ошибку четвёртым аргументом и протянуть формулу на свои данные. Главная оговорка — версия Excel у тех, кто откроет файл.

Ключевые факты: функция ПРОСМОТРX

• ПРОСМОТРX ищет значение в просматриваемом массиве и берёт элемент с той же позицией из возвращаемого массива^[1].
• У формулы шесть аргументов, обязательны три; режим сопоставления по умолчанию равен 0, режим поиска — 1^[1].
• В Excel 2016 и Excel 2019 этой функции нет, хотя книгу с ней там можно открыть^[1].
• Справка Microsoft называет её поиском в любом направлении с точным совпадением по умолчанию^[2].
• В Google Таблицах XLOOKUP принимает те же шесть аргументов и те же значения по умолчанию^[3].

Функция ПРОСМОТРX в Excel: синтаксис и шесть аргументов

Функция ПРОСМОТРX в Excel находит искомое значение в одном диапазоне и возвращает элемент с той же позицией из другого. Синтаксис по справке Microsoft: =ПРОСМОТРX(искомое_значение;просматриваемый_массив;возвращаемый_массив;[если_ничего_не_найдено];[режим_сопоставления];[режим_поиска])^[1]. В английском интерфейсе её имя — XLOOKUP.

Главное отличие от старых функций поиска — точное совпадение включено по умолчанию: без пятого аргумента формула ищет ровно то, что указано^[1]. Ответ берётся из отдельного диапазона, поэтому столбец результата может стоять и правее, и левее ключа, а номер столбца считать не нужно.

ПРОСМОТРX умеет вернуть сразу несколько столбцов: если третьим аргументом передать прямоугольник, одна формула выдаст всю найденную строку^[1]. Ответ при этом разливается по соседним ячейкам, как любой динамический массив.

По справке Microsoft функция недоступна в Excel 2016 и Excel 2019, хотя книгу с ней, созданную в новой версии, там можно открыть^[1]. Функция ПРОСМОТРX указана в справке для Microsoft 365, Excel 2024 и Excel 2021, включая версии для Mac, а также для Excel на iPad, iPhone и Android^[1]. Перед тем как отправлять файл коллегам, уточните, в какой версии они работают.

Дальше — пять типовых задач на одном прайсе, от простой цены до целой строки.

Примеры на одном прайсе: цена, поиск влево, вся строка

ПРОСМОТРX решает пять типовых задач прайса одной и той же схемой: ключ, где искать, откуда брать ответ. Прайс занимает A1:D5, в G2 записан код Т-103.

Поиск влево — главное, чем функция ПРОСМОТРX выручает тех, кто годами переставлял столбцы ради формулы: код может стоять где угодно. Если в возвращаемый массив взять сразу три столбца B2:D5, ответ займёт три ячейки по горизонтали, и справа от формулы должно быть пусто.

1. Введите в H2 формулу =ПРОСМОТРX(G2;A2:A5;C2:C5) и нажмите Enter.
2. Проверьте её на коде, которого нет в прайсе: исправная формула покажет #Н/Д.
3. Допишите четвёртый аргумент, если пустой результат — нормальная ситуация.
4. Закрепите диапазоны клавишей F4 и протяните формулу вниз.

Без знака доллара диапазоны сползут при протягивании — механика описана на странице о закреплении диапазонов знаком доллара. Повторите пять задач в тренажёре: урок меняет прайс и проверяет каждую формулу на новых данных.

Значение на пересечении строки и столбца дают вложенные функции^[1]. Если в A2:A4 стоят менеджеры, в B1:D1 — месяцы, а в F2 и G2 — «Петрова» и «Мар», формула =ПРОСМОТРX(F2;A2:A4;ПРОСМОТРX(G2;B1:D1;B2:D4)) сначала выберет столбец месяца, затем строку менеджера и вернёт 142.

Именно поэтому два последних аргумента заслуживают отдельного разбора: они управляют сравнением и порядком перебора.

Режим сопоставления или режим поиска: шкалы, маски и последняя запись

ПРОСМОТРX сравнивает значения по пятому аргументу, а порядок перебора задаёт шестым. Справка Microsoft перечисляет допустимые значения обоих режимов^[1].

Шкала скидок показывает разницу режимов сопоставления. Пороги стоят в A12:A15, скидки — в B12:B15, сумма заказа — в D12.

Режим -1 подходит для скидок «от суммы», режим 1 — для тарифов «до суммы».

Пропущенный четвёртый аргумент обозначают пустым местом между точками с запятой: ;;-1. Требование сортировки справка Microsoft ставит только двоичному поиску в режимах 2 и -2: на несортированных данных он даёт недопустимые результаты^[1].

Подстановочные знаки включает только режим сопоставления 2. На прайсе из предыдущего раздела запись =ПРОСМОТРX("К*";B2:B5;C2:C5;;2) вернёт 640 — цену первого товара на «К», а с режимом поиска -1 та же маска даст 410, цену последнего. Без двойки звёздочка ищется как обычный символ, и результатом станет #Н/Д.

Однако гибкие режимы не спасают от ошибок в самих данных и в размерах диапазонов.

Ошибки ПРОСМОТРX: #Н/Д, #ПЕРЕНОС! и пустой результат

Ошибки ПРОСМОТРX сводятся к трём ситуациям: ключа нет, ответу некуда разлиться или пропуск аргумента записан как пустая строка. Таблица связывает симптом с причиной.

Код #Н/Д означает, что допустимое совпадение не найдено, а текст для такого случая не задан^[1]. Прежде чем прятать ошибку, сверьте ключ: лишний пробел или число, сохранённое как текст, дают тот же код.

#ПЕРЕНОС! появляется, когда что-то блокирует выходной диапазон^[4]: ответ из трёх столбцов не помещается, потому что справа от формулы стоят данные. Формулы перенесённого массива к тому же не работают внутри таблиц Excel^[4], поэтому возврат целой строки ставят за пределами умной таблицы.

Пустая строка в четвёртом аргументе — тихая ловушка. Запись "";-1 вместо ;;-1 возвращает пустоту там, где раньше стояла бы ошибка, и ненайденные ключи теряются среди пустых ячеек.

Остаётся понять, в каких задачах ПРОСМОТРX лучше не применять вовсе.

Когда функция ПРОСМОТРX не подойдёт

Функция ПРОСМОТРX не подойдёт, если файл открывают в Excel 2016 или Excel 2019, нужны все совпадения или ключ состоит из двух полей. Таблица сравнивает её с двумя старыми приёмами поиска.

• Если коллеги работают в Excel 2016 или Excel 2019, формула у них не пересчитается, — для общих файлов надёжнее ВПР с точным поиском.
• Если у ключа несколько строк и нужны все, функция вернёт одну — первую или последнюю при режиме поиска -1^[1]; целый список отдаёт функция ФИЛЬТР.
• При ключе из двух полей одного искомого значения мало: условия придётся склеивать или перемножать — приёмы собраны на странице с приёмами поиска по составному ключу.
• Если ответ из нескольких столбцов нужен внутри умной таблицы, разлив не сработает^[4].

Google Таблицы поддерживают XLOOKUP с теми же шестью аргументами: режим сопоставления по умолчанию там тоже 0, а режим поиска — 1^[3]. Просматриваемый диапазон в Google Таблицах должен быть одной строкой или одним столбцом^[3]. Остальные функции поиска собраны в подборке формул Excel для поиска и расчётов.