Функции ПОИСКПОЗ и ИНДЕКС в Excel

Теория

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

ПОИСКПОЗ – возвращает относительную позицию искомого значения в заданном диапазоне ячеек. Функция имеет три аргумента:

=ПОИСКПОЗ(<Контекст>; <Диапазон>; <Тип_сопоставления>)

• <Контекст> - Что ищем. Поисковый контекст или адрес ячейки, в которой будет находится поисковый контекст;
• <Диапазон> - Где ищем. Диапазон ячеек, в котором производится поиск;
• <Тип_сопоставления> - Необязательный аргумент, который отвечает за то, как сравнивать при поиске (больше, меньше, точно). Наш нижеприведенный пример требует аргумент установить в «0» (точное совпадение).

ИНДЕКС – возвращает значение или ссылку по номеру строки, или по номеру столбца, или на пересечении строки и столбца из заданного диапазона. Функция имеет три аргумента:

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

• <Диапазон> - Где ищем. Диапазон ячеек, в котором производится поиск. Это может быть одномерный (строка, столбец) или двухмерный (таблица) диапазон;
• <Номер_строки> - Номер строки с начала диапазона. Аргумент не нужен, если диапазон – строка;
• <Номер_столбца> - Номер столбца с начала диапазона. Аргумент не нужен, если диапазон – столбец.

Практика

Допустим, у нас есть сводная таблица результатов матчей прошедшего чемпионата России по футболу. Мы хотим иметь небольшой виджет, с помощью которого будем выбирать пару команд и смотреть, какие места они заняли и результат их матча. В нашем примере виджет будет находиться на одном листе с таблицей для наглядности, но на практике имеет смысл его создавать, если виджет нужно выводить на другом листе, на котором большая таблица по каким-то причинам неуместна.

На инфографике показано, что получилось:

P.S.1. Формулу в сдвоенной ячейке A24 можно упростить до такой: =ИНДЕКС(B2:Q17; A22; B22)
Дело в том, что мы один раз уже нашли номера команд в своих списках (ячейки A22 и B22) и, поэтому, вместо функций ПОИСКПОЗ в последней формуле можно поставить ссылки именно на эти ячейки.
P.S.2. В английской версии MS Excel функция ПОИСКПОЗ – это MATCH, а ИНДЕКС – это INDEX.
P.S.3. Коллеги визуалы и аудиалы могут ознакомиться с данными функциями посредством видеоролика.