ПРОСМОТРX в Excel надёжнее старой ВПР: ищет и вправо, и влево, не зависит от номера столбца, сам показывает понятный текст вместо ошибки и проще читается. Разбираем формулу на реальных примерах.
Сдвинули столбец — и ВПР тихо начала врать. Есть формула, которая так не умеет
Вы открываете отчёт, который вчера работал идеально. Цены на месте, всё посчитано. А сегодня в колонке цена не та — и непонятно почему. Вы ничего не трогали в формуле. Просто кто-то вставил один столбец в справочник. И ВПР, не моргнув, начала тянуть данные из соседней колонки.
Это и есть главная подлость ВПР. Она ломается тихо. Не выдаёт ошибку, не краснеет, не предупреждает. Просто возвращает уверенный неправильный результат, который уезжает в отчёт, к руководителю, в бухгалтерию. А виноватым окажется не тот, кто вставил столбец, а тот, чья формула «вдруг начала врать».
ВПР вообще капризнее, чем кажется. Она ищет только в первом столбце диапазона. Не умеет искать влево. Зависит от номера столбца. При ошибке выдаёт сухое #Н/Д, и человек гадает: это данные кривые или формула ушла в отпуск. Раньше всё это обходили связкой ИНДЕКС + ПОИСКПОЗ — мощно, но для новичка выглядит как заклинание из бухгалтерского подземелья.
А потом в Excel появилась функция, которая закрывает почти все эти боли разом, — ПРОСМОТРX. Она ищет в любую сторону, не требует считать номер столбца, сама показывает понятный текст вместо ошибки и читается проще. Если вы до сих пор по привычке живёте на ВПР, эту функцию стоит освоить — не потому что «новое лучше», а потому что в реальных рабочих таблицах она безопаснее.
Сразу важная ловушка, из-за которой формула может «не работать»: буква X в названии ПРОСМОТРX — английская, латинская. Если набрать кириллическую «Х», Excel функцию не распознает. Чтобы не мучиться, начните вводить «ПРОСМОТР» и выберите функцию из подсказки клавишей Tab — тогда имя подставится правильно.
Что делает ПРОСМОТРX
ПРОСМОТРX ищет значение в одном диапазоне и возвращает результат из другого. Самый простой смысл: найди вот это значение здесь и верни соответствующее значение отсюда. Например, есть артикул, в справочнике есть список артикулов, рядом стоят цены — нужно по артикулу подтянуть цену. ВПР для этого требует общий диапазон и номер столбца. ПРОСМОТРX говорит проще: вот где ищем, вот откуда возвращаем.
Базовая структура функции:
=ПРОСМОТРX(что_ищем; где_ищем; что_вернуть; если_не_найдено)
Простой пример:
=ПРОСМОТРX(A2;$F$2:$F$1000;$H$2:$H$1000;"Не найдено")
По-человечески: найди значение из A2 в диапазоне F2:F1000 и верни соответствующее значение из H2:H1000. Если ничего не найдено, покажи текст «Не найдено». Уже здесь видно главное преимущество: не надо считать, что цена находится в третьем столбце диапазона. Мы прямо указали, где искать и что вернуть.
Пример 1. Найти цену по артикулу
Представим таблицу заказов, где в основной таблице есть артикул и пустая колонка цены, а в справочнике — артикулы и цены. Нужно подтянуть цену по артикулу.
=ПРОСМОТРX(A2;$F$2:$F$1000;$H$2:$H$1000;"Не найдено")
Здесь A2 — артикул, который ищем, $F$2:$F$1000 — столбец артикулов в справочнике, $H$2:$H$1000 — столбец цен, откуда возвращаем результат, а «Не найдено» — понятный текст, если артикула нет.
ВПР для этой задачи выглядела бы так:
=ВПР(A2;$F$2:$H$1000;3;ЛОЖЬ)
Она тоже работает. Но если кто-то вставит новый столбец между названием и ценой, номер 3 станет неправильным — и вы получите ровно ту тихую ошибку из начала статьи. ПРОСМОТРX смотрит не на «третий столбец», а на конкретный диапазон цен. Поэтому риск меньше.
Почему ПРОСМОТРX удобнее ВПР
Главных преимуществ несколько. Первое — не нужен номер столбца: в ВПР цифру 3 надо указывать вручную, и это слабое место, потому что при изменении таблицы формула начинает возвращать не то. В ПРОСМОТРX мы указываем диапазон возврата напрямую.
Второе — можно искать влево. ВПР ищет значение только в первом столбце диапазона и возвращает данные справа; если результат слева, начинаются танцы. ПРОСМОТРX ищет где угодно и возвращает откуда угодно.
Третье — понятнее обрабатывает ошибку: можно сразу указать, что показать, если ничего не найдено, без отдельного ЕСЛИОШИБКА. Четвёртое — формула легче читается: через месяц открываете файл и сразу видите, что ищем в F, возвращаем из H. Пятое — для больших рабочих файлов формула с прямыми диапазонами надёжнее, чем ВПР с ручным номером столбца.
Пример 2. По номеру заказа найти клиента
Допустим, в одной таблице список заказов с пустой колонкой клиента, а в справочнике — номера заказов, даты, клиенты и суммы. Формула для клиента:
=ПРОСМОТРX(A2;$F$2:$F$1000;$H$2:$H$1000;"Заказ не найден")
Здесь мы ищем номер заказа в столбце F, а возвращаем клиента из столбца H. Если нужен не клиент, а сумма, меняем только диапазон возврата:
=ПРОСМОТРX(A2;$F$2:$F$1000;$I$2:$I$1000;"Заказ не найден")
В этом удобство ПРОСМОТРX: логика не меняется. Ищем там же, возвращаем из другого столбца.
Пример 3. По email найти менеджера
Ещё сценарий: есть список клиентов, нужно подтянуть менеджера по email. В справочнике — клиент, email и менеджер.
=ПРОСМОТРX(A2;$G$2:$G$1000;$H$2:$H$1000;"Менеджер не найден")
Здесь email — ключ. Это нормально, если email в справочнике уникален. Но если один email встречается несколько раз, формула вернёт первое найденное совпадение. Важный момент: ПРОСМОТРX удобнее ВПР, но не отменяет чистоту данных. Дубли в ключах всё равно надо проверять — например, подсветить повторяющиеся email в справочнике условным форматированием перед поиском.
Кстати, чтобы не собирать тренировочный пример с нуля, в конце я положу в Telegram готовый файл со всеми формулами из этой статьи: поиск цены по артикулу, клиента по заказу, менеджера по email, поиск влево и обработка «Не найдено». Открываете и сразу пробуете на своих данных.
Пример 4. По артикулу вернуть сразу несколько столбцов
ПРОСМОТРX умеет возвращать не только одну ячейку, но и несколько соседних столбцов, если ваша версия Excel поддерживает динамические массивы. Например, нужно по артикулу вернуть название и цену:
=ПРОСМОТРX(A2;$F$2:$F$1000;$G$2:$H$1000;"Не найдено")
Здесь диапазон возврата — G2:H1000, то есть сразу два столбца: название и цена. Результат «разольётся» в соседние ячейки: в одну попадёт название, в другую цена. Это удобно, когда по одному ключу нужно подтянуть сразу несколько данных — название и цену, клиента и сумму, город и менеджера, статус и дату. Но есть нюанс: справа от формулы должны быть пустые ячейки. Если там что-то уже есть, Excel может не вывести весь результат.
Пример 5. По цене найти товар — поиск влево
Вот где ВПР обычно спотыкается. Допустим, в справочнике товар стоит слева, а цена справа, и нужно по цене найти товар. ВПР так просто не умеет, потому что ищет в первом столбце диапазона и возвращает данные справа. ПРОСМОТРX умеет:
=ПРОСМОТРX(A2;$G$2:$G$1000;$F$2:$F$1000;"Не найдено")
Здесь мы ищем цену в G, а возвращаем товар из F, то есть слева. Особенно полезно, когда структура таблицы уже задана и переставлять столбцы ради формулы не хочется.
ПРОСМОТРX против ВПР на одном примере
Допустим, нужно по артикулу найти цену.
ВПР:
=ВПР(A2;$F$2:$H$1000;3;ЛОЖЬ)
ПРОСМОТРX:
=ПРОСМОТРX(A2;$F$2:$F$1000;$H$2:$H$1000;"Не найдено")
Что видно сразу? В ВПР нужно понимать, что цена — третий столбец внутри диапазона F:H. В ПРОСМОТРX видно напрямую: ищем в F, возвращаем из H. Если через месяц вы откроете чужой файл, ПРОСМОТРX читать проще. А в рабочих файлах читаемость — не роскошь, а страховка от ошибок.
Что означает «если не найдено»
Четвёртый аргумент ПРОСМОТРX — очень полезная вещь. Если значение найдено, Excel вернёт цену. Если нет — покажет ваш текст. Это лучше, чем сухая ошибка #Н/Д, особенно если файл будут смотреть люди, которые не любят формулы и считают Excel немного колдовством.
Можно писать разные варианты — «Артикул не найден», «Проверьте справочник», «Нет в базе». Главное — не ставить пустоту там, где ошибка важна. Плохой вариант:
=ПРОСМОТРX(A2;$F$2:$F$1000;$H$2:$H$1000;"")
Пустая ячейка может скрыть проблему: человек подумает, что данных просто нет, а на деле значение не найдено. Лучше честно писать «Не найдено».
Точный поиск и примерное совпадение
В большинстве офисных задач нужен точный поиск: артикул должен совпадать с артикулом, номер заказа — с номером, email — с email. По умолчанию ПРОСМОТРX ищет точное совпадение. Это большой плюс по сравнению с ВПР, где ошибка с последним аргументом часто приводила к неприятностям.
У ПРОСМОТРX есть дополнительные аргументы для типа совпадения и направления поиска, но на старте их можно не трогать. Для обычных справочников, прайсов, заказов и клиентов достаточно базовой формулы. Когда понадобится искать ближайшее меньшее, ближайшее большее или использовать маски, можно разобраться глубже.
Ошибка 1. Диапазоны разного размера
ПРОСМОТРX требует, чтобы диапазон поиска и диапазон возврата были одного размера. Правильно, когда оба идут со строки 2 по 1000:
=ПРОСМОТРX(A2;$F$2:$F$1000;$H$2:$H$1000;"Не найдено")
Плохо, когда поиск до 1000, а возврат до 900:
=ПРОСМОТРX(A2;$F$2:$F$1000;$H$2:$H$900;"Не найдено")
Так нельзя: Excel должен понимать, какая строка поиска соответствует какой строке результата. Простое правило: где ищем и что возвращаем — одинаковое количество строк. Если справочник растёт, лучше использовать умную таблицу через Ctrl + T — тогда формулы станут стабильнее.
Ошибка 2. Лишние пробелы в ключах
ПРОСМОТРX удобнее ВПР, но он не волшебник. Если в одной таблице артикул A-105, а в другой A-105 с пробелом на конце, это разные значения. Глазом почти одинаково. Для Excel — нет. Проверяйте ключи: артикулы, email, телефоны, номера заказов, названия клиентов, ID, ИНН. Для очистки:
=СЖПРОБЕЛЫ(A2)
Если после выгрузки есть непечатаемые символы:
=СЖПРОБЕЛЫ(ПЕЧСИМВ(A2))
И только потом использовать очищенный ключ для поиска. Скучная часть, но без неё даже самая современная формула будет искать грязные данные и возвращать грязный результат.
Ошибка 3. Дубли в справочнике
ПРОСМОТРX, как и ВПР, вернёт первое найденное совпадение, если в справочнике есть дубли. Если артикул A-105 встречается дважды — с ценой 590 и 750 — формула найдёт первый и вернёт 590, хотя актуальная цена может быть 750. Поэтому перед поиском проверьте ключевой столбец на дубли:
Выделите столбец с ключами
Главная → Условное форматирование
Правила выделения ячеек
Повторяющиеся значения
Если дубли есть, сначала решите, что с ними делать: удалить старые записи, оставить последнюю цену, добавить дату актуальности или сделать уникальный ключ из артикула и даты. Формула не обязана понимать вашу бизнес-логику — она берёт первое совпадение.
Ошибка 4. Числа как текст
Классика Excel: в одной таблице код 105 хранится как число, а в другой как текст «105». Глазом одинаково, для формулы — разница. Особенно часто это встречается в артикулах, номерах заказов, телефонах, ИНН, кодах клиентов и выгрузках из CRM и 1С.
Если код не используется для расчётов, лучше хранить его как текст: телефон, ИНН, артикул, номер договора — это не числа для арифметики, их не надо складывать и умножать. Для приведения к тексту можно использовать =ТЕКСТ(A2;"0"), для превращения текста в число — =ЗНАЧЕН(A2). Но осторожно с ведущими нулями: код 00125 после превращения в число станет 125, а это уже другой код.
Ошибка 5. Скрывать «Не найдено» пустотой
Иногда хочется сделать красиво и убрать ошибки через пустую строку в последнем аргументе. Выглядит аккуратно, но опасно: пустая ячейка может означать что угодно — значение не найдено, данных действительно нет, формула не сработала, ключ грязный, справочник неполный. Если готовите рабочий отчёт, лучше показывать явный статус «Не найдено», а потом отфильтровать все такие строки и проверить их. Ошибка должна быть видна. Иначе это не исправление, а коврик поверх люка.
Как использовать ПРОСМОТРX с умной таблицей
Если оформить справочник как умную таблицу через Ctrl + T, работать станет удобнее. Допустим, таблица называется СправочникТоваров и в ней есть столбцы Артикул и Цена. Формула может выглядеть так:
=ПРОСМОТРX(A2;СправочникТоваров[Артикул];СправочникТоваров[Цена];"Не найдено")
Это читается почти как обычная фраза: найди A2 в столбце «Артикул» таблицы «СправочникТоваров» и верни цену. Плюс умной таблицы: если добавите новые строки в справочник, формула учтёт их автоматически — не нужно вручную расширять диапазон с 1000 до 5000 строк. Для больших рабочих файлов это хорошая привычка: справочники и таблицы поиска оформлять через Ctrl + T.
ПРОСМОТРX вместо ИНДЕКС + ПОИСКПОЗ
До ПРОСМОТРX многие использовали связку:
=ИНДЕКС($H$2:$H$1000;ПОИСКПОЗ(A2;$F$2:$F$1000;0))
Она ищет позицию значения через ПОИСКПОЗ и возвращает результат через ИНДЕКС. Формула мощная и до сих пор полезная, но для новичка сложнее. ПРОСМОТРX делает то же самое проще:
=ПРОСМОТРX(A2;$F$2:$F$1000;$H$2:$H$1000;"Не найдено")
Связка ИНДЕКС + ПОИСКПОЗ работает в три шага: ищем позицию, передаём её в ИНДЕКС, возвращаем значение. ПРОСМОТРX — в два: ищем значение, возвращаем результат. Для большинства обычных задач ПРОСМОТРX понятнее. Но если вы работаете со старыми версиями Excel, где ПРОСМОТРX нет, связка ИНДЕКС + ПОИСКПОЗ всё ещё пригодится.
Что делать, если у вас нет ПРОСМОТРX
ПРОСМОТРX есть не во всех версиях Excel — он доступен в Microsoft 365 и новых версиях, а на старой функция может не работать. В таком случае варианты простые: использовать ВПР, использовать ИНДЕКС + ПОИСКПОЗ, обновить Excel при возможности или применить Power Query для сложных объединений.
Если вы делаете файл для других людей, учитывайте их версию Excel. У вас ПРОСМОТРX может работать, а у коллеги на старом Excel файл покажет ошибку. Для личных рабочих файлов используйте современную функцию, если она доступна. Для общих — уточняйте совместимость.
Когда ВПР ещё можно использовать
Не надо демонизировать ВПР. Она не плохая — просто старая и ограниченная. ВПР можно использовать, если таблица простая, поиск идёт по первому столбцу, результат находится справа, структура не меняется, а вы закрепили диапазон, поставили точный поиск и понимаете риск номера столбца.
Но если вы создаёте новый файл и у вас есть ПРОСМОТРX, чаще разумнее использовать его — особенно если таблица будет меняться, результат находится слева, нужно понятное сообщение вместо ошибки, файл будут читать другие люди или важна устойчивость к вставке столбцов.
Мини-чек-лист ПРОСМОТРX
Перед тем как использовать формулу, проверьте:
1. Что ищем.
Артикул, номер заказа, email, ID, ИНН, телефон или другой ключ.
2. Где ищем.
Один столбец или строка, где должен быть ключ.
3. Что возвращаем.
Цена, клиент, сумма, статус, дата, менеджер.
4. Диапазоны одинакового размера.
Поиск и возврат должны соответствовать друг другу.
5. Ключи очищены.
Без лишних пробелов, скрытых символов и смешения текста с числами.
6. Нет дублей.
Если нужен один точный результат, ключ должен быть уникальным.
7. Ошибка видна.
Лучше «Не найдено», чем пустая ячейка.
8. Версия Excel поддерживает функцию.
Если файл пойдёт коллегам, проверьте совместимость.
9. Справочник лучше оформить как умную таблицу.
Через Ctrl + T.
10. Несколько результатов проверить вручную.
Автоматизация любит контроль. Как старый мастер: доверяй, но рулетку держи рядом.
Практическая связка: сначала чистим данные, потом ищем
Хороший рабочий порядок такой: сначала проверить дубли в справочнике через условное форматирование, затем очистить ключи от пробелов через СЖПРОБЕЛЫ и при необходимости ПЕЧСИМВ, привести типы данных (код как текст — везде текст, число — везде число), оформить справочник как умную таблицу, чтобы диапазоны росли сами, и только потом написать ПРОСМОТРX с понятным текстом ошибки. После этого отфильтровать все «Не найдено» и проверить, почему не нашлось, а ещё выборочно сверить 5–10 строк глазами.
Это звучит дольше, чем просто вставить формулу. Но зато отчёт не будет красиво врать.
Главное
ПРОСМОТРX — одна из тех функций, которые реально упрощают жизнь в Excel. Не потому что модная, а потому что решает старые боли ВПР: ищет в любую сторону, не требует номера столбца, позволяет сразу написать «Не найдено», легче читается, хорошо работает со справочниками и умными таблицами и во многих случаях заменяет даже связку ИНДЕКС + ПОИСКПОЗ.
Но важно помнить: новая формула не лечит грязные данные. Если в справочнике дубли, пробелы, разные типы данных и старые цены, ПРОСМОТРX просто быстрее найдёт вашу проблему — или первое совпадение, которое вы забыли проверить. Поэтому сильный Excel-файл строится не на одной функции, а на связке: чистые данные, понятные ключи, проверенный справочник и правильная формула.
В Telegram я подготовлю файл-шаблон с примерами ПРОСМОТРX: поиск цены по артикулу, клиента по заказу, менеджера по email, поиск влево, обработка «Не найдено» и сравнение с ВПР. Можно будет открыть и сразу потренироваться.
Напишите в комментариях: вы уже используете ПРОСМОТРX или до сих пор работаете через ВПР и ИНДЕКС + ПОИСКПОЗ?
Макрос решает — Excel, Word, PowerPoint и VBA без лишних слов.