ИНДЕКС и ПОИСКПОЗ в Excel: гибкий поиск вместо ВПР
Связка ИНДЕКС + ПОИСКПОЗ сначала находит положение нужного элемента, а затем возвращает значение из указанного диапазона. Она подходит для точного поиска, работает влево и не ломается только из-за вставки нового столбца между ключом и результатом.
Как устроена связка функций
ПОИСКПОЗ отвечает на вопрос «на каком месте находится значение?», а ИНДЕКС — «что находится на этом месте в другом диапазоне?». Допустим, в B2:B6 записаны артикулы, в D2:D6 — цены, а искомый артикул находится в G2. Формула:
=ИНДЕКС($D$2:$D$6;ПОИСКПОЗ(G2;$B$2:$B$6;0))
Внутренняя функция возвращает номер строки внутри диапазона B2:B6. ИНДЕКС берёт значение с той же позиции из D2:D6.
Третий аргумент 0 задаёт точное совпадение. Для артикулов, фамилий и других ключей обычно нужен именно он. Диапазоны поиска и результата должны начинаться и заканчиваться на одинаковых строках.
Как собрать формулу без ошибки
- Отдельно проверьте
=ПОИСКПОЗ(G2;$B$2:$B$6;0). Результатом должно быть число от 1 до количества строк диапазона. - Выделите столбец, из которого нужен ответ: в примере D2:D6.
- Вложите проверенную ПОИСКПОЗ вторым аргументом ИНДЕКС.
- Закрепите справочные диапазоны знаками доллара и оставьте G2 относительной, если формулу нужно копировать вниз.
- Сравните несколько результатов с исходной таблицей вручную.
Официальные справки Microsoft отдельно объясняют функции ИНДЕКС и ПОИСКПОЗ.
Чем это отличается от ВПР
| Сценарий | ИНДЕКС + ПОИСКПОЗ | ВПР |
|---|---|---|
| Результат расположен левее ключа | Работает | Требует перестройки диапазона или другого приёма |
| Между ключом и ответом вставили столбец | Диапазон результата остаётся явным | Номер столбца может перестать соответствовать задаче |
| Формула для начинающего | Длиннее, но логика разделена | Короче для простого поиска вправо |
| Поиск по строке и столбцу | Две ПОИСКПОЗ внутри ИНДЕКС | Нужны дополнительные решения |
Если ВПР уже решает простую стабильную задачу, переписывать его необязательно. Разбор базового варианта есть в статье про функцию ВПР в Excel.
Научитесь строить устойчивые формулы поиска
Связка ИНДЕКС + ПОИСКПОЗ раскрывается вместе с типами ссылок, проверкой данных, обработкой ошибок и современными функциями поиска. На курсе вы соберёте такие решения на практических таблицах.
Двумерный поиск
ИНДЕКС может принимать номер строки и номер столбца. Пусть A2:A10 содержит названия товаров, B1:E1 — месяцы, а B2:E10 — продажи. Товар указан в H2, месяц — в H3:
=ИНДЕКС($B$2:$E$10;ПОИСКПОЗ(H2;$A$2:$A$10;0);ПОИСКПОЗ(H3;$B$1:$E$1;0))
Первая ПОИСКПОЗ выбирает строку товара, вторая — столбец месяца. ИНДЕКС возвращает значение на пересечении.
Почему формула возвращает ошибку
- #Н/Д. Ключа нет, число сохранено как текст или в строке есть лишний пробел.
- #ССЫЛКА!. Номер позиции выходит за границы диапазона ИНДЕКС либо ссылка стала недействительной.
- Неверный результат. Для обычного справочника пропущен третий аргумент 0 или диапазоны смещены относительно друг друга.
- Формула меняется при копировании. Справочные диапазоны не закреплены абсолютными ссылками.
Для пользовательского отчёта ошибку можно обработать: =ЕСЛИОШИБКА(ИНДЕКС(...);"Не найдено"). Но сначала устраните причину: ЕСЛИОШИБКА не должна маскировать неправильный диапазон или дубликаты ключей.
Как проверить справочник
Убедитесь, что ключевой столбец не содержит неожиданных дублей: ПОИСКПОЗ вернёт первое совпадение. Сравните типы данных, удалите непечатаемые пробелы, протестируйте существующий и отсутствующий ключ. Если версия Excel поддерживает ПРОСМОТРX, сравните с ним читаемость решения, но выбирайте функцию с учётом версии программы у всех получателей файла.
Вопросы и ответы
- Можно ли с помощью ИНДЕКС и ПОИСКПОЗ искать значение слева?
Да. Диапазон поиска задаётся внутри ПОИСКПОЗ, а диапазон результата — отдельно в ИНДЕКС, поэтому столбец с ответом может находиться как справа, так и слева от ключа.