Скидка на подшипники из наличия!
Новое поступление товара в 2026 году!
ВПР и связка ИНДЕКС+ПОИСКПОЗ — два основных способа поиска по таблицам в Excel, без которых не обойтись при работе с сортаментом, каталогами и справочными данными. ВПР проще и нагляднее, но у неё есть жёсткие ограничения; связка ИНДЕКС+ПОИСКПОЗ гибче и устойчивее. Разберём синтаксис обеих, ограничения ВПР, когда нужна связка, и покажем формулы на примерах инженерных таблиц.
Ниже — синтаксис функций по официальной документации, практика поиска по сортаменту (обозначение профиля, типоразмер, параметр по коду), причины перейти с ВПР на связку и современная альтернатива ПРОСМОТРX.
ВПР (вертикальный просмотр, англ. VLOOKUP) ищет значение в первом столбце диапазона и возвращает значение из указанного столбца той же строки. Функция принимает четыре аргумента.
=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
Для точного поиска по обозначению или коду четвёртый аргумент почти всегда должен быть ЛОЖЬ (0). Пропуск этого аргумента включает приблизительный поиск и на неотсортированной таблице даёт неверный результат.
=ВПР(F2; $A$2:$E$500; 4; ЛОЖЬ)
Горизонтальный аналог — ГПР (горизонтальный просмотр, HLOOKUP): ищет в первой строке диапазона и возвращает значение из указанной строки. Если искомого нет и задан точный поиск, ВПР возвращает ошибку #Н/Д — её удобно перехватывать функцией ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ВПР(F2; $A$2:$E$500; 4; ЛОЖЬ); "нет в сортаменте")
Типовая инженерная задача — подтянуть параметр из сортамента или каталога по обозначению: массу погонного метра профиля, размеры подшипника по его коду, толщину стенки трубы по условному проходу. Здесь важно правильно выбрать режим совпадения.
Когда ключ — это обозначение или код (текстовое значение), нужен точный поиск: четвёртый аргумент ЛОЖЬ. Порядок строк в таблице значения не имеет.
Когда ключ — число, а нужно выбрать ближайший стандартный типоразмер по расчётной величине (например, ближайший меньший или равный номинал), уместен приблизительный поиск: четвёртый аргумент ИСТИНА. При этом первый столбец таблицы обязан быть отсортирован по возрастанию, а функция вернёт строку с наибольшим значением, не превышающим искомое.
=ВПР(E2; $A$2:$B$40; 2; ИСТИНА)
Приблизительный поиск (ИСТИНА) требует сортировки первого столбца по возрастанию. Если таблица не отсортирована, результат будет ошибочным без выдачи ошибки — это одна из самых коварных ловушек ВПР. Для поиска по обозначениям всегда используйте точное совпадение (ЛОЖЬ).
ВПР удобна, но её конструкция накладывает ограничения, которые в больших инженерных таблицах приводят к ошибкам.
ПОИСКПОЗ (MATCH) возвращает не само значение, а его позицию (номер) в строке или столбце. ИНДЕКС (INDEX) возвращает значение из массива по номеру строки и столбца. Вместе они дают гибкий поиск, свободный от ограничений ВПР.
=ПОИСКПОЗ(искомое_значение; просматриваемый_массив; [тип_сопоставления])
=ИНДЕКС(массив; номер_строки; [номер_столбца])
Для поиска по обозначению используют тип сопоставления 0. ПОИСКПОЗ находит позицию ключа, а ИНДЕКС возвращает значение из столбца результата по этой позиции.
=ИНДЕКС($D$2:$D$500; ПОИСКПОЗ(F2; $A$2:$A$500; 0))
=ИНДЕКС($A$2:$A$500; ПОИСКПОЗ(F2; $D$2:$D$500; 0))
ПОИСКПОЗ при точном сопоставлении (0) поддерживает подстановочные знаки: звёздочка заменяет любую последовательность символов, вопросительный знак — один символ. Регистр букв функция не различает.
Многие инженерные таблицы двумерны: значение стоит на пересечении строки и столбца — например, толщина стенки по диаметру и рабочему давлению, или коэффициент по двум параметрам. ВПР такое не решает, а ИНДЕКС с двумя ПОИСКПОЗ — решает.
=ИНДЕКС($B$2:$F$40; ПОИСКПОЗ(H1; $A$2:$A$40; 0); ПОИСКПОЗ(H2; $B$1:$F$1; 0))
ПРОСМОТРX (XLOOKUP) — функция поиска, объединяющая достоинства ВПР и связки ИНДЕКС+ПОИСКПОЗ. Она ищет в любом направлении, по умолчанию выполняет точное совпадение, не использует номер столбца и имеет встроенную обработку «не найдено».
=ПРОСМОТРX(искомое_значение; массив_поиска; массив_возврата; [если_не_найдено]; [режим_совпадения]; [режим_поиска])
=ПРОСМОТРX(F2; $A$2:$A$500; $D$2:$D$500; "нет в сортаменте")
ПРОСМОТРX доступна в Excel 2021, Excel 2024 и в версии по подписке (Microsoft 365), а также в новых мобильных версиях. В Excel 2016 и Excel 2019 функции нет — там для тех же задач применяют связку ИНДЕКС+ПОИСКПОЗ. Файл с ПРОСМОТРX, открытый в неподдерживающей версии, покажет функцию с префиксом _xlfn. и при пересчёте вернёт ошибку #ИМЯ?.
Практический ориентир: для простых таблиц с ключом в первом столбце годится ВПР. Для надёжных рабочих книг, где столбцы могут добавляться, нужен поиск влево или двумерный поиск — связка ИНДЕКС+ПОИСКПОЗ. Если книга гарантированно открывается в Excel 2021 или по подписке — самый простой и универсальный вариант ПРОСМОТРX.
Связка умеет искать значения левее столбца с ключом, не ломается при вставке и удалении столбцов (позиция определяется динамически, а не жёстким номером) и обрабатывает только два нужных столбца, что быстрее на больших таблицах. ВПР этого не умеет.
По устройству функции искомое значение должно находиться в первом столбце заданного диапазона, а результат возвращается из столбца правее. Чтобы получить данные левее ключа, применяют связку ИНДЕКС+ПОИСКПОЗ или функцию ПРОСМОТРX.
Это третий аргумент: 0 — точное совпадение (порядок не важен), 1 — наибольшее значение не больше искомого (нужна сортировка по возрастанию), -1 — наименьшее значение не меньше искомого (сортировка по убыванию). Для поиска по обозначению используют 0.
Когда столбец результата левее ключа, когда таблица двумерная (значение на пересечении строки и столбца) и когда книга активно редактируется и столбцы могут переставляться. В этих случаях ВПР либо не работает, либо возвращает данные не из того столбца.
В ВПР задайте четвёртый аргумент ЛОЖЬ, в ПОИСКПОЗ — тип сопоставления 0. Тогда функция найдёт строку с точно совпадающим обозначением независимо от порядка строк. Приблизительный поиск для текстовых обозначений не применяют.
Да, функция ПРОСМОТРX: ищет в любом направлении, по умолчанию делает точное совпадение, не требует номера столбца и умеет возвращать текст вместо ошибки. Доступна в Excel 2021, Excel 2024 и по подписке; в Excel 2016 и 2019 её нет.
В библиотеке pandas аналогом поиска по таблице служит сопоставление по ключу: метод merge объединяет таблицы по общему столбцу, а map подставляет значения по словарю соответствий. Это удобно для больших сортаментов и пакетной обработки данных.
Вы можете задать любой вопрос на тему нашей продукции или работы нашего сайта.