Как работать с ИНДЕКС и ПОИСКПОЗ в Excel и Google Таблицах

ИНДЕКС и ПОИСКПОЗ — функции в Excel и Google Таблицах, которые работают в связке. ПОИСКПОЗ находит позицию нужного значения, а ИНДЕКС по этой позиции выдает результат из указанного диапазона. С их помощью удобно собирать данные из больших таблиц, находить значения в любом столбце или строке, строить более точные формулы, чем с обычным ВПР. Разбираем, как работают функции и как использовать их, чтобы ускорить расчеты.

Синтаксис функций ИНДЕКС и ПОИСКПОЗ

В общем виде функция ИНДЕКС или INDEX строится так:

=ИНДЕКС(диапазон; номер_строки; номер_столбца)

Она состоит из компонентов:

• массив — диапазон, из которого нужно получить значение;
• номер_строки внутри массива, откуда нужно взять результат;
• номер_столбца внутри массива. Если значения указаны в диапазоне одного столбца или одной строки, то этот параметр можно не указывать.

Например, у маркетолога есть таблица эффективности рекламных каналов. В столбце A указаны названия каналов, в столбце B — количество лидов, в столбце C — стоимость лида. Если пользователь знает, что нужный канал находится в третьей строке диапазона C2:C6, формула будет такой:

=ИНДЕКС(С2:С6; 3)

Но в работе порядок каналов часто меняется, поэтому удобнее определять номер строки автоматически с помощью функции ПОИСКПОЗ.

В общем виде функция ПОИСКПОЗ (MATCH) записывается так:

=ПОИСКПОЗ(искомое_значение; просматриваемый_диапазон; [тип_сопоставления])

Компоненты функции:

• искомое_значение — то, что нужно найти. Например, название канала;
• просматриваемый_диапазон — столбец или строка, где выполняется поиск;
• тип_сопоставления — режим поиска.

У типа сопоставления может быть одно из значений: 0, 1, -1.

Если выбрать неверный тип сопоставления, функция может вернуть неправильный индекс или выдать ошибку.

Обычно функции ИНДЕКС и ПОИСКПОЗ используются в паре:

=ИНДЕКС(диапазон_с_результатом; ПОИСКПОЗ(искомое_значение; диапазон_поиска; 0))

Функция ПОИСКПОЗ определяет, где именно находится нужный элемент, а потом ИНДЕКС подставляет значение из этого места. С помощью такой связки можно автоматически найти нужное значение в таблице. Это удобно, когда она постоянно обновляется или меняется порядок строк.

Например, маркетолог хочет найти в таблице стоимость лида по каналу «VK Реклама». Значение находится в столбце C, а сам канал в столбце A. Формула будет выглядеть так:

=ИНДЕКС(C2:C6; ПОИСКПОЗ("VK Реклама"; A2:A6; 0))

Функция ПОИСКПОЗ ищет строку, где написано «VK Реклама». В конкретном примере это вторая по счету строка. ИНДЕКС берет второе значение из столбца со стоимостью лидов. В итоге формула возвращает значение 380 ₽, стоимость лида по VK Рекламе.

При этом поиск не зависит от регистра: «vk реклама», «Vk реклама» или «VK РЕКЛАМА» дадут тот же результат.

Когда стоит использовать ИНДЕКС и ПОИСКПОЗ вместо ВПР

Функции ВПР, ИНДЕКС и ПОИСКПОЗ используются для поиска данных в таблице. Они по-разному обрабатывают диапазоны, из-за этого результаты могут отличаться. Вот случаи, когда лучше применять ИНДЕКС и ПОИСКПОЗ вместо ВПР:

Получить данные слева от столбца поиска. Функция ВПР ищет значение только в первом столбце диапазона и возвращает данные из столбцов, которые находятся правее. Из-за этого формула не всегда может получить нужные значения.

ИНДЕКС и ПОИСКПОЗ работают по-другому: ПОИСКПОЗ находит номер строки по условию, а ИНДЕКС возвращает данные из выбранного столбца этой строки. Благодаря этому формула позволяет выполнять поиск в любую сторону, включая столбцы, расположенные слева.

К примеру, в компании у каждого сотрудника есть номер ID, к которому привязано имя и адрес электронной почты. А нам нужно найти email сотрудника по его ID из правого столбца таблицы.

Можно использовать формулу:

=ИНДЕКС(A2:A10; ПОИСКПОЗ(102; C2:C10; 0))

ПОИСКПОЗ определяет строку, где ID равен 102, а ИНДЕКС возвращает значение из столбца A, адрес электронной почты сотрудника.

Сохранить формулы при изменении таблицы. ВПР использует порядковый номер столбца внутри диапазона. Если добавить или удалить колонку, нумерация меняется, и формула начинает ссылаться на другую.

Связка ИНДЕКС и ПОИСКПОЗ работает по-другому. Она опирается не на номер столбца, а на конкретный диапазон. Поэтому формула остается корректной даже после изменения структуры таблицы. Это удобно, когда нужно часто обновлять отчеты и вносить дополнительные данные.

Например, маркетолог ведет таблицу показателей по рекламным каналам. Чтобы быстро найти стоимость клика (СРС) по email-рассылкам, он использует функцию ВПР.

Для этого используется формула:

=ВПР(G2; A2:D6; 4; 0)

В ячейке G2 записано точное название канала «Email-рассылки». ВПР находит канал в столбце A и возвращает CPC из четвертого столбца диапазона.

Предположим, что в таблицу нужно добавить новую метрику, например, CTR. Он вставляет столбец между «Клики» и «CPC», чтобы показатели располагались в логическом порядке.

После этого столбец с CPC смещается из D в E, но формула ВПР продолжает ссылаться на четвертый столбец. Теперь она выводит некорректные данные: CTR вместо CPC. Чтобы каждый раз при появлении новых данных не менять формулу, можно воспользоваться связкой ИНДЕКС и ПОИСКПОЗ:

=ИНДЕКС(D2:D6; ПОИСКПОЗ(G2; A2:A6; 0))

Даже если маркетолог добавит новый показатель, переставит столбцы или обновит структуру отчета, формула продолжит возвращать нужный столбец с CPC.

Проверить только нужные столбцы. ВПР анализирует весь диапазон, который указан в формуле. Если в аргументе указано A:Z, функция просматривает все столбцы между A и Z, даже если используется только один столбец результата.

Связка ИНДЕКС и ПОИСКПОЗ работает только с двумя колонками: столбцом поиска и столбцом результата. Формула обрабатывает меньше данных и благодаря этому работает быстрее.

Сделать поиск одновременно по строкам и столбцам. ВПР выполняет только вертикальный поиск. Если нужно искать по строкам, используется отдельная функция — ГПР.

Индекс и ПОИСКПОЗ одинаково работают как со столбцами, так и со строками. При необходимости можно использовать две функции ПОИСКПОЗ: первая определяет строку, вторая — столбец, а ИНДЕКС возвращает значение по двум координатам.

Например, у маркетолога есть сводная таблица с результатами рекламных каналов по месяцам. В строках указаны источники трафика, в столбцах — месяцы. Нужно узнать стоимость заявки по каналу «Яндекс Директ» в марте.

Для поиска используется формула:

=ИНДЕКС(B2:G10;ПОИСКПОЗ("Яндекс Директ"; A2:A10; 0);ПОИСКПОЗ("Март"; B1:G1; 0))

Первая функция ПОИСКПОЗ находит строку «Яндекс Директ», а вторая — столбец «Март». ИНДЕКС возвращает стоимость заявки на пересечении строки и столбца.

В Excel 2021 и Microsoft 365 есть функция ПРОСМОТРХ (XLOOKUP). Она заменяет связку ИНДЕКС + ПОИСКПОЗ или ВПР в большинстве задач: умеет искать в любую сторону, возвращает точные соответствия, работает со строками и столбцами, проще читается и не ломается при изменении структуры таблицы.

При этом синтаксис у ПРОСМОТРХ во многом похож на ИНДЕКС + ПОИСКПОЗ. В формуле точно так же задается диапазон поиска, диапазон результата и поведение при отсутствии совпадений, но вычисления выполняются одной функцией вместо двух. 

Например, маркетологу нужно найти таблице стоимость лида по каналу «VK Реклама».

Вместо связки ИНДЕКС + ПОИСКПОЗ:

=ИНДЕКС(C2:C6; ПОИСКПОЗ("VK Реклама"; A2:A6; 0))

Можно использовать одну формулу ПРОСМОТРХ:

=ПРОСМОТРХ("VK Реклама"; A2:A6; C2:C6; "Не найдено")

ПРОСМОТРХ ищет канал в столбце A и возвращает соответствующее значение из столбца C. Если нужного канала нет, формула выводит «Не найдено».

Типичные ошибки при работе с функциями ИНДЕКС и ПОИСКПОЗ и их решение