Функции ИНДЕКС и ПОИСКПОЗ в Excel работают в паре и полностью заменяют ВПР, обеспечивая поиск значений в любом направлении таблицы без ограничений. Эта комбинация позволяет найти нужное значение в строке или столбце (ПОИСКПОЗ), а затем вернуть данные из соответствующей ячейки (ИНДЕКС), работая гораздо гибче, чем классическая ВПР.
Причины использования ИНДЕКС+ПОИСКПОЗ вместо ВПР
- ВПР ищет только слева направо и требует, чтобы столбец поиска находился левее столбца результата
- ВПР замедляет работу больших таблиц из-за постоянного сканирования всех данных
- ВПР не может искать значение, находящееся левее нужного результата
- ИНДЕКС+ПОИСКПОЗ позволяет искать в любом направлении — слева направо и справа налево
- Комбинация двух функций обеспечивает большую надёжность и производительность
- ПОИСКПОЗ работает с условиями поиска точного совпадения, примерного совпадения и подстановочных символов
Пошаговая инструкция по использованию ИНДЕКС и ПОИСКПОЗ
Базовый синтаксис формулы
Откройте ячейку, в которую нужно вывести результат. Введите формулу следующей структуры:
=ИНДЕКС(диапазон_результата; ПОИСКПОЗ(искомое_значение; диапазон_поиска; 0))
Где:
диапазон_результата— массив ячеек, из которого нужно вернуть значениеискомое_значение— то, что вы ищете (текст, число или ссылка на ячейку)диапазон_поиска— диапазон, в котором ищется значение0— параметр для точного совпадения (используйте всегда 0 для точного поиска)
Практический пример: поиск по столбцу
- Создайте таблицу с данными: в столбце A — фамилии сотрудников, в столбце B — их оклады.
- Нажмите на пустую ячейку (например, D2), в которой должен отобразиться результат поиска.
- Напишите в ячейку D1 подпись:
Поиск оклада. - В ячейке D2 введите формулу:
=ИНДЕКС(B:B;ПОИСКПОЗ(C2;A:A;0)) - В ячейке C2 укажите фамилию для поиска (например,
Иванов). - Нажмите
Enter— формула вернёт оклад Иванова из столбца B. - Скопируйте формулу из D2 в ячейки ниже, если нужно искать несколько фамилий.
Поиск по строке (вместо использования ГПР)
- Откройте таблицу, где данные расположены горизонтально (в строках).
- Установите курсор в ячейку, где должен быть результат.
- Введите формулу:
=ИНДЕКС(2:2;ПОИСКПОЗ(«искомое_значение»;1:1;0)) - Замените
2:2на номер строки, из которой нужно вернуть значение. - Замените
1:1на номер строки, где расположены заголовки или значения для поиска. - Нажмите
Enter.
Поиск в обе стороны (поиск в прямоугольном диапазоне)
- Используйте вложенные функции ИНДЕКС и ПОИСКПОЗ для двумерного поиска.
- В ячейке F2 введите формулу:
=ИНДЕКС(A:E;ПОИСКПОЗ(искомое_значение_строка;A:A;0);ПОИСКПОЗ(искомое_значение_столбец;1:1;0)) - Замените
A:Eна ваш диапазон данных. - Замените
искомое_значение_строкана значение для поиска в левом столбце. - Замените
искомое_значение_столбецна значение для поиска в верхней строке. - Нажмите
Enter— результат вернёт пересечение найденной строки и столбца.
Обработка ошибок (если значение не найдено)
- Оберните формулу в функцию
ЕСЛИОШИБКАдля защиты от ошибок#Н/A. - Используйте синтаксис:
=ЕСЛИОШИБКА(ИНДЕКС(B:B;ПОИСКПОЗ(C2;A:A;0));«Не найдено») - Если значение не найдено, формула выведет текст
Не найденовместо ошибки. - Нажмите
Enter.
Поиск с частичным совпадением (подстановочные символы)
- Используйте подстановочные символы в ПОИСКПОЗ для нечёткого поиска.
- Замените ноль (0) на 1 в параметре ПОИСКПОЗ:
=ИНДЕКС(B:B;ПОИСКПОЗ(C2&»*»;A:A;0)) - Символ
*означает «любые символы после текста». - Нажмите
Enter.
Часто задаваемые вопросы
В чём различие между ПОИСКПОЗ с параметром 0, 1 и -1? Параметр 0 находит точное совпадение, 1 находит наибольшее значение, не превышающее искомое (требует сортировки), -1 находит наименьшее значение, не меньшее искомого.
Почему формула выдаёт ошибку #Н/A? Это означает, что ПОИСКПОЗ не нашла нужное значение в указанном диапазоне — проверьте, что значение действительно существует в таблице и написано без опечаток и лишних пробелов.
Можно ли использовать ИНДЕКС+ПОИСКПОЗ в условных формулах? Да, вы можете комбинировать ИНДЕКС+ПОИСКПОЗ с функциями ЕСЛИ, СУММ или СЧЁТЕСЛИ для более сложной логики.
