ПОИСКПОЗ внутри ИНДЕКС: поиск по позиции
Используйте ПОИСКПОЗ для поиска ключа, а ИНДЕКС — для получения значения на той же позиции. Отдельные диапазоны позволяют возвращать данные слева или справа от ключевого столбца.
Связка снимает привязку к первому столбцу общей таблицы. При этом нужно согласовать диапазоны, выбрать режим сопоставления и учитывать дубликаты: ПОИСКПОЗ с 0 вернёт первое совпадение, а не все подходящие строки.
Логика связки
- ПОИСКПОЗ находит позицию строки (или столбца), в которой хранится нужный ключ.
- ИНДЕКС получает это число и возвращает значение из другого диапазона по найденной позиции.
Ключевая разница с ВПР: поиск и возврат данных полностью разделены. Ключи и нужные данные могут находиться в разных столбцах и не обязательно рядом. Для показанного вертикального поиска оба диапазона должны охватывать соответствующие строки в одинаковом порядке. Сортировать столбец ключей отдельно от результатов нельзя.
Общая формула
=ИНДЕКС(диапазон_результата; ПОИСКПОЗ(ключ; диапазон_ключей; 0))
Формула выше предназначена для вертикальных диапазонов из одного столбца. Читайте её изнутри наружу:
- ПОИСКПОЗ ищет
ключвдиапазоне_ключейи возвращает номер позиции. - ИНДЕКС берёт этот номер и возвращает значение из
диапазона_результатана той же позиции.
Практический пример
Лист «Продажи»: в столбце A — артикулы товаров, в столбце B — количество. Нужно подтянуть цену из листа «Прайс», где артикулы — в столбце B, а цены — в столбце A (левее артикулов!).
Обычная ВПР с прямой ссылкой не вернёт цену левее ключа. С помощью ПОИСКПОЗ и ИНДЕКС решение выглядит так:
=ИНДЕКС(Прайс!$A$2:$A$100; ПОИСКПОЗ(A2; Прайс!$B$2:$B$100; 0))
Разбор по шагам:
ПОИСКПОЗ(A2; Прайс!$B$2:$B$100; 0)— ищет артикул из A2 в столбце B листа «Прайс», возвращает номер позиции.ИНДЕКС(Прайс!$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 — столбцам этой области. Двумерный поиск возможен и через ВПР с ПОИСКПОЗ в аргументе номера столбца, если ключи находятся в первом столбце таблицы.