Связка ПОИСКПОЗ + ИНДЕКС: поиск по отдельным диапазонам Теория

ПОИСКПОЗ внутри ИНДЕКС: поиск по позиции

Используйте ПОИСКПОЗ для поиска ключа, а ИНДЕКС — для получения значения на той же позиции. Отдельные диапазоны позволяют возвращать данные слева или справа от ключевого столбца.

Связка снимает привязку к первому столбцу общей таблицы. При этом нужно согласовать диапазоны, выбрать режим сопоставления и учитывать дубликаты: ПОИСКПОЗ с 0 вернёт первое совпадение, а не все подходящие строки.

Логика связки

  1. ПОИСКПОЗ находит позицию строки (или столбца), в которой хранится нужный ключ.
  2. ИНДЕКС получает это число и возвращает значение из другого диапазона по найденной позиции.

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

Общая формула

=ИНДЕКС(диапазон_результата; ПОИСКПОЗ(ключ; диапазон_ключей; 0))

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

  1. ПОИСКПОЗ ищет ключ в диапазоне_ключей и возвращает номер позиции.
  2. ИНДЕКС берёт этот номер и возвращает значение из диапазона_результата на той же позиции.

Практический пример

Лист «Продажи»: в столбце A — артикулы товаров, в столбце B — количество. Нужно подтянуть цену из листа «Прайс», где артикулы — в столбце B, а цены — в столбце A (левее артикулов!).

Обычная ВПР с прямой ссылкой не вернёт цену левее ключа. С помощью ПОИСКПОЗ и ИНДЕКС решение выглядит так:

=ИНДЕКС(Прайс!$A$2:$A$100; ПОИСКПОЗ(A2; Прайс!$B$2:$B$100; 0))

Разбор по шагам:

  1. ПОИСКПОЗ(A2; Прайс!$B$2:$B$100; 0) — ищет артикул из A2 в столбце B листа «Прайс», возвращает номер позиции.
  2. ИНДЕКС(Прайс!$A$2:$A$100; ...) — берёт это число и возвращает цену из столбца A на той же позиции.

Преимущества перед ВПР

Возможность ВПР ПОИСКПОЗ + ИНДЕКС
Выбор столбца поиска Первый столбец переданного диапазона Любой отдельно заданный столбец
Результат левее ключа Обычная ВПР с прямой ссылкой не подходит Можно задать диапазон результата левее
Устойчивость при добавлении столбцов Фиксированный номер может указать на другое поле Ссылки обычно сдвигаются при вставке; соответствие строк нужно сохранять
Поиск по строке (горизонтально) Для горизонтального поиска используют ГПР Горизонтально: ИНДЕКС(строка_результата;1;ПОИСКПОЗ(ключ;строка_ключей;0))

Двумерный поиск

Связку можно расширить до двух ПОИСКПОЗ — для одновременного поиска по строке и столбцу:

=ИНДЕКС(Данные!$B$2:$F$20;
    ПОИСКПОЗ(A2; Данные!$A$2:$A$20; 0);
    ПОИСКПОЗ(B1; Данные!$B$1:$F$1; 0))

Первый ПОИСКПОЗ находит нужную строку, второй — нужный столбец. ИНДЕКС возвращает значение на их пересечении. Строки A2:A20 должны соответствовать строкам B2:F20, а заголовки B1:F1 — столбцам этой области. Двумерный поиск возможен и через ВПР с ПОИСКПОЗ в аргументе номера столбца, если ключи находятся в первом столбце таблицы.

В Microsoft 365 и Excel 2021/2024 для Windows и Mac доступна функция ПРОСМОТРX (XLOOKUP, последняя буква — латинская X). Она использует отдельные диапазоны поиска и результата и по умолчанию ищет точное совпадение. В Excel 2016 и 2019 её нет; показанные формулы ИНДЕКС с ПОИСКПОЗ подходят и для этих версий. ПОИСКПОЗ с 0 не различает регистр; * и ? в запросе работают как подстановочные знаки, а ~ экранирует их. Если ключ не найден, возвращается #Н/Д; согласованность типов ключей и диапазонов нужно проверять в любом варианте.