ИНДЕКС и ПОИСКПОЗ в Excel: гибкая альтернатива ВПР
ВПР ищет только в первом столбце диапазона и возвращает результат правее — а ещё «ломается», если внутри диапазона вставить или удалить столбец. Связка функций ИНДЕКС и ПОИСКПОЗ решает обе проблемы: ищет в любом направлении и не зависит от номера столбца. Разберём обе функции по отдельности и готовую формулу вместо ВПР.
Как работает функция ПОИСКПОЗ
ПОИСКПОЗ не возвращает само значение — она находит его позицию (порядковый номер) внутри диапазона:
=ПОИСКПОЗ(искомое_значение; просматриваемый_массив; тип_сопоставления)
Например, =ПОИСКПОЗ(E2;A2:A100;0) вернёт число —
на какой по счёту позиции внутри диапазона A2:A100 находится
значение из E2. Если значение из E2 стоит в ячейке A7, формула
вернёт 6 (шестая позиция от начала диапазона A2:A100), а не
номер строки листа.
Как работает функция ИНДЕКС
ИНДЕКС, наоборот, по номеру позиции возвращает значение:
=ИНДЕКС(массив; номер_строки; [номер_столбца])
Например, =ИНДЕКС(C2:C100;6) вернёт значение,
которое стоит на шестой позиции внутри диапазона C2:C100 —
то есть значение из ячейки C7. Третий аргумент (номер столбца)
нужен, только если массив включает несколько столбцов;
для одномерного диапазона, как в примере, достаточно номера
строки.
Комбинация вместо ВПР
Если вложить ПОИСКПОЗ вместо номера строки в ИНДЕКС, получится полноценная замена ВПР:
=ИНДЕКС(C2:C100;ПОИСКПОЗ(E2;A2:A100;0))
ПОИСКПОЗ находит позицию значения из E2 внутри столбца A, а ИНДЕКС по этой же позиции достаёт значение из столбца C — независимо от того, какой из столбцов, A или C, стоит в таблице левее.
Главное преимущество — поиск в любом направлении
В отличие от ВПР, у ИНДЕКС+ПОИСКПОЗ нет требования, чтобы искомый столбец был первым (самым левым) в диапазоне. Если, например, нужно найти артикул товара по его названию, а артикул в таблице стоит раньше названия, — ВПР с такой задачей не справится в принципе, а формула ИНДЕКС+ПОИСКПОЗ решает её точно так же, как и поиск в обратную сторону: диапазон результата и диапазон поиска в ней независимы друг от друга.
Второе преимущество — устойчивость к вставке столбцов
У ВПР номер нужного столбца — это просто число (третий
аргумент), которое считает столбцы от начала диапазона. Если
внутри диапазона вставить новый столбец, это число не
обновляется автоматически, и ВПР начинает возвращать значение
из соседнего, неверного столбца или выдавать ошибку
#ССЫЛКА!. В формуле ИНДЕКС+ПОИСКПОЗ столбец с результатом задан
не номером, а ссылкой на диапазон ячеек (C2:C100) —
при вставке столбца левее эта ссылка сдвигается вместе с
данными автоматически, и формула продолжает работать без
правок.
Поиск по двум условиям
Формулу можно расширить для поиска по нескольким условиям сразу, например по имени сотрудника и месяцу:
=ИНДЕКС(C2:C100;ПОИСКПОЗ(1;(A2:A100=E2)*(B2:B100=E3);0))
Ctrl+Shift+Enter — иначе Excel посчитает только
первую строку диапазона и вернёт неверный результат.
Частые проблемы
- Формула возвращает значение из соседней строки. Диапазон поиска в ПОИСКПОЗ и диапазон результата в ИНДЕКС начинаются с разных строк — например, один взят от строки 2, а другой от строки 3. ПОИСКПОЗ отсчитывает позицию от начала своего диапазона, поэтому смещение диапазонов друг относительно друга сдвигает и результат.
- ПОИСКПОЗ возвращает #Н/Д, хотя значение точно есть в таблице. Как и у ВПР, чаще всего причина в невидимых пробелах или разном типе данных (число как текст против настоящего числа) — сравните значения через СЖПРОБЕЛЫ или приведите оба к одному типу.
- Перепутан порядок аргументов ИНДЕКС. Сначала указывается диапазон, затем номер строки и только потом (не обязательно) номер столбца — обратный порядок или пропущенная точка с запятой перед номером строки дают ошибку #ЗНАЧ! или неверный результат.
Часто задаваемые вопросы
Чем ИНДЕКС и ПОИСКПОЗ лучше ВПР, если обе формулы находят одно и то же значение?
Две причины перейти на ИНДЕКС+ПОИСКПОЗ даже там, где ВПР формально справляется: во-первых, ВПР ищет только в первом столбце диапазона и возвращает результат правее — ИНДЕКС+ПОИСКПОЗ работает в любом направлении. Во-вторых, ВПР ссылается на нужный столбец числом (третий аргумент), и при вставке или удалении столбца внутри диапазона это число «уезжает» и формула начинает возвращать не то значение — ИНДЕКС ссылается на столбец диапазоном ячеек, который Excel корректирует автоматически.
Можно ли искать по двум условиям одновременно (например, по имени и месяцу)?
Да: формула вида <code>=ИНДЕКС(диапазон_результата;ПОИСКПОЗ(1;(диапазон1=условие1)*(диапазон2=условие2);0))</code> находит строку, где совпадают сразу оба условия. В Excel 365 и Excel 2021 такая формула считается как обычная, введённая через Enter; в более старых версиях (2019 и раньше) её нужно вводить сочетанием Ctrl+Shift+Enter — это формула массива.
Что означает третий аргумент ПОИСКПОЗ и когда его менять?
Это тип сопоставления. 0 — точное совпадение, стандартный выбор для поиска по коду, ID, ФИО. 1 — приближённое совпадение с наибольшим значением, не превышающим искомое, требует, чтобы просматриваемый массив был отсортирован по возрастанию. −1 — то же самое, но по убыванию. Для большинства практических задач нужен именно 0.
Почему ИНДЕКС и ПОИСКПОЗ возвращают неверное значение, хотя формула выглядит правильно?
Самая частая причина — диапазон поиска в ПОИСКПОЗ и диапазон результата в ИНДЕКС начинаются с разных строк или имеют разную длину. ПОИСКПОЗ возвращает не номер строки листа, а порядковый номер позиции внутри своего диапазона, поэтому если диапазоны смещены друг относительно друга хотя бы на одну строку, ИНДЕКС достанет значение из соседней, неверной строки.
Что дальше
Если задача проще и таблица не будет меняться по структуре, ВПР по-прежнему остаётся более короткой и понятной формулой — подробный разбор синтаксиса, точного и приближённого поиска и типичных ошибок в статье «Функция ВПР (VLOOKUP) в Excel: примеры и разбор ошибок».
Собрали разборы формул, ошибок и автоматизации в одном месте — «Excel для бухгалтера: формулы, ошибки, автоматизация».
Готовые таблицы с рабочими формулами поиска
В шаблонах на сайте формулы поиска и связанные с ними диапазоны уже настроены и проверены — остаётся только подставить свои данные.