ПРОСМОТРX (XLOOKUP) в Excel: как заменить ВПР и искать в обе стороны

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

Как работает ПРОСМОТРX

У функции три обязательных части: что найти, где искать и откуда вернуть результат. В русской локализации базовая запись выглядит так: `=ПРОСМОТРX(искомое_значение;просматриваемый_массив;возвращаемый_массив)`. Искомый и возвращаемый диапазоны должны согласовываться по размеру: если поиск идёт по 99 строкам, массив результата тоже должен содержать 99 соответствующих строк.

Дополнительные аргументы управляют отсутствующим значением, типом совпадения и направлением поиска: `=ПРОСМОТРX(что;где;что_вернуть;[если_не_найдено];[режим_сопоставления];[режим_поиска])`. В русской версии Excel аргументы обычно разделяются точкой с запятой; фактический разделитель зависит от региональных настроек системы.

Главное отличие от старой привычки с ВПР: функция получает отдельный диапазон поиска и отдельный диапазон результата. Не нужно считать номер столбца внутри общей таблицы, и результат может находиться по любую сторону от ключа.

Точный поиск и понятное сообщение об отсутствии

Допустим, код сотрудника находится в `D2:D100`, фамилия — в `B2:B100`, а искомый код введён в `H2`. Формула `=ПРОСМОТРX(H2;$D$2:$D$100;$B$2:$B$100;"Не найдено")` ищет код в столбце D и возвращает фамилию из столбца B. Это поиск влево, для которого обычный ВПР потребовал бы перестройки диапазона или другой комбинации функций.

Точное совпадение является режимом по умолчанию. Если четвёртый аргумент не указан и ключ отсутствует, Excel возвращает ошибку `#Н/Д`. Аргумент `"Не найдено"` удобен в интерфейсном отчёте, но в расчётной модели иногда лучше сохранить ошибку: она заметнее и не превращает проблему справочника в обычный текст.

Зафиксируйте диапазоны знаком `$`, если копируете формулу вниз. Ещё надёжнее преобразовать источник в таблицу Excel и использовать структурированные ссылки: они расширяются вместе с новыми строками и делают формулу понятнее. Ключевой столбец следует нормализовать: убрать случайные пробелы и согласовать типы, потому что число `125` и текст `"125"` могут не совпасть.

Почему поиск в обе стороны надёжнее ВПР

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

Задача	ВПР	ПРОСМОТРX
Результат слева от ключа	Не напрямую	Да
Точное совпадение	Нужно явно задать	По умолчанию
Ответ, если не найдено	Обычно отдельная обработка ошибки	Встроенный аргумент
Возврат нескольких полей	Несколько формул	Один массив результата
Поиск с последней записи	Сложная конструкция	Режим поиска −1

Это не означает, что все старые формулы нужно немедленно переписать. Если файл открывают пользователи Excel 2016 или 2019, ПРОСМОТРX недоступна. Сначала определите минимальную версию в команде, а затем выбирайте между ПРОСМОТРX и совместимой связкой ИНДЕКС + ПОИСКПОЗ.

Как вернуть сразу несколько столбцов

Если по коду нужно получить фамилию, отдел и должность из соседних столбцов `B:D`, используйте возвращаемый массив из трёх столбцов: `=ПРОСМОТРX(H2;$A$2:$A$100;$B$2:$D$100;"Не найдено")`. В версиях с динамическими массивами один результат разольётся на три ячейки по горизонтали.

Выходная область должна быть свободна. Если соседняя ячейка занята, Excel покажет ошибку разлива. Не размещайте обычные значения внутри ожидаемого динамического результата. При переносе формулы в таблицу Excel учитывайте, что разлив внутри вычисляемого столбца может вести себя иначе, чем в обычном диапазоне.

Возврат нескольких столбцов полезен для карточки объекта и снижает число повторных поисков. Но не возвращайте десятки полей «на всякий случай»: модель становится тяжелее, а зависимость — менее очевидной. Выберите только данные, необходимые конкретному отчёту.

Как найти последнее совпадение

По умолчанию ПРОСМОТРX ищет от первой записи к последней. Шестой аргумент `−1` меняет направление и позволяет вернуть последнее совпадение. Например, даты операций находятся в `A2:A1000`, клиент — в `B2:B1000`, статус — в `C2:C1000`. Формула `=ПРОСМОТРX(H2;$B$2:$B$1000;$C$2:$C$1000;"Нет операций";0;-1)` вернёт статус последней снизу записи этого клиента.

«Последняя снизу» не всегда означает «самая поздняя по дате». Источник должен быть отсортирован в нужной хронологии или иметь гарантированный порядок загрузки. Если строки перемешаны, сначала определите максимальную дату и найдите сочетание клиента и даты либо подготовьте данные в Power Query.

Режимы `2` и `−2` используют двоичный поиск по массиву, отсортированному соответственно по возрастанию или убыванию. На несортированных данных они способны вернуть неверный результат. Для большинства рабочих таблиц безопаснее обычные режимы `1` и `−1`, пока необходимость и сортировка двоичного поиска не доказаны.

Приближённое совпадение по интервалам

Пятый аргумент задаёт режим сопоставления. Значение `0` — точное совпадение. Значение `−1` означает точное совпадение или следующий меньший элемент, `1` — точное или следующий больший, `2` — совпадение с подстановочными знаками. Выбор должен соответствовать бизнес-правилу, а не быть случайным способом убрать `#Н/Д`.

Пример шкалы скидок: нижние границы объёма `0`, `10`, `50`, `100` находятся в `A2:A5`, ставки — в `B2:B5`, фактический объём — в `H2`. Формула `=ПРОСМОТРX(H2;$A$2:$A$5;$B$2:$B$5;"Нет шкалы";-1;1)` ищет точную границу или ближайшую меньшую. Для объёма 72 она выбирает уровень 50. Таблица порогов должна быть отсортирована по возрастанию и начинаться с минимально допустимого значения.

Для правила «назначить ближайший следующий уровень» используется режим `1`. Перед публикацией тестируйте границы: значение ровно на пороге, между порогами, ниже минимума и выше максимума. Ошибка в знаке режима часто выглядит правдоподобно и потому опаснее явного сообщения.

Подстановочные знаки в текстовом поиске

Режим сопоставления `2` разрешает шаблоны: звёздочка означает любую последовательность символов, вопросительный знак — один символ. Например, шаблон `"*сервис*"` может найти первую строку, содержащую слово внутри более длинного названия. Тильда используется для экранирования специального знака, если нужно найти буквальную звёздочку или вопросительный знак.

Шаблонный поиск удобен для контролируемых справочников, но не заменяет очистку данных. Формула может выбрать первую из нескольких похожих записей. Если в справочнике есть «Север Сервис» и «Сервис Плюс», запрос `*сервис*` неоднозначен. Для денежных и кадровых расчётов используйте устойчивый идентификатор и точное совпадение.

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

Типичные ошибки и диагностика

Симптом	Вероятная причина	Что проверить
`#Н/Д`	Ключ отсутствует или отличается тип	Пробелы, число/текст, область поиска
`#ЗНАЧ!`	Размеры массивов не согласованы	Число строк или столбцов
Ошибка разлива	Выходная область занята	Соседние ячейки и объединения
Не та запись	Дубликаты или неверное направление	Уникальность ключа и режим поиска
Неверный интервал	Ошибочный режим или сортировка	Пороговые тесты
Функция не распознана	Старая версия Excel	Совместимость пользователей

Диагностику начинайте с трёх небольших проверок: посчитайте число совпадений ключа, сравните тип искомого значения с типом диапазона и временно уберите аргумент «если не найдено». Понятная надпись удобна пользователю, но может скрыть систематическую проблему загрузки.

Для критичной модели добавьте контроль: ключи источника уникальны, все ключи отчёта найдены, количество строк до и после объединения не изменилось неожиданно. Формула должна не только выдавать результат, но и сигнализировать о нарушении предпосылок.

Практический чек-лист

1. Убедитесь, что версия Excel поддерживает ПРОСМОТРX.
2. Выберите устойчивый уникальный ключ, а не приблизительное название.
3. Согласуйте размеры массива поиска и массива результата.
4. Зафиксируйте диапазоны или используйте таблицу Excel.
5. Оставьте точное совпадение для идентификаторов.
6. Для последней записи подтвердите порядок строк.
7. Для интервалов протестируйте обе границы каждого диапазона.
8. Проверьте дубликаты и незаполненные ключи.
9. Не скрывайте ошибку до завершения диагностики.

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

Надёжный поиск строится не только на новой функции: нужны чистый ключ, проверенная версия Excel, контроль дублей и явное бизнес-правило совпадения.

Вопросы и ответы

• Как записывается функция ПРОСМОТРX в русской версии Excel?

  Минимальная запись: `=ПРОСМОТРX(искомое_значение;просматриваемый_массив;возвращаемый_массив)` с учётом системного разделителя аргументов.

• Может ли ПРОСМОТРX возвращать значение слева?

  Да, диапазон результата задаётся отдельно от диапазона поиска и может находиться как слева, так и справа от ключа.

• Как найти последнее совпадение с помощью ПРОСМОТРX?

  Укажите точное совпадение `0` и режим поиска `−1`, предварительно подтвердив, что порядок строк соответствует понятию последней записи.

• Почему ПРОСМОТРX возвращает ошибку Н/Д?

  Ключ может отсутствовать, содержать пробелы или иметь другой тип; сначала проверьте данные, а затем добавляйте сообщение «не найдено».

• Доступна ли ПРОСМОТРX в Excel 2016 и 2019?

  Нет, Microsoft указывает, что функция недоступна в Excel 2016 и Excel 2019, поэтому для совместимости нужна другая формула.

• Чем ПРОСМОТРX безопаснее ВПР при изменении таблицы?

  Она ссылается прямо на диапазон результата и не использует жёсткий номер столбца внутри общего массива, который может сдвинуться.

По теме