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