Связка ИНДЕКС и ПОИСКПОЗ в Excel: значение слева от ключа и на пересечении

Справочник редко устроен под удобную формулу: коды товаров стоят в первом столбце, названия — во втором, а запрос приходит именно по названию. Справка Microsoft напоминает, что ВПР находит значение только слева направо^[1], и такой справочник ей не по силам.

Связка из двух функций снимает это ограничение. Порядок работы такой: найти столбец, где лежит ключ, ввести вложенную формулу с типом сопоставления 0, проверить результат на заведомо отсутствующем ключе, исправить ошибку и протянуть формулу на свои данные, закрепив диапазоны.

Ключевые факты: ИНДЕКС и ПОИСКПОЗ в Excel

• ПОИСКПОЗ возвращает относительную позицию элемента в диапазоне, а не сам элемент^[2].
• Тип сопоставления принимает значения 1, 0 и -1, а без аргумента функция работает так, будто указан тип 1^[2].
• ИНДЕКС возвращает #ССЫЛКА!, если номер строки или столбца указывает за пределы массива^[3].
• Справка Microsoft относит обе функции к Microsoft 365, Excel 2024, Excel 2021, Excel 2019 и Excel 2016^[2].
• В Google Таблицах ПОИСКПОЗ тоже по умолчанию использует тип 1 и принимает только одномерный диапазон^[4].

ИНДЕКС и ПОИСКПОЗ в Excel: синтаксис и порядок вычисления

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

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

ИНДЕКС умеет больше, чем требует связка. Если в массиве одна строка или один столбец, второй номер можно не указывать^[3]. Ноль в номере строки или столбца заставляет функцию вернуть весь столбец или всю строку: в Microsoft 365 такую формулу достаточно подтвердить клавишей Enter, а в старых версиях — сочетанием Ctrl+Shift+Enter^[3]. Ссылочная форма с четвёртым аргументом «номер_области» выбирает один из нескольких диапазонов, причём по умолчанию берётся первая область^[3].

Разобравшись с синтаксисом, переходим к справочнику, в котором ответ лежит левее ключа.

Пример по шагам: код товара по названию

ПОИСКПОЗ и ИНДЕКС находят код товара по названию, хотя коды стоят левее названий. Справочник занимает столбцы A–C.

1. Встаньте в E2 и наберите =ПОИСКПОЗ("Чай";B2:B5;0): функция вернёт 3, потому что «Чай» стоит третьим в диапазоне B2:B5.
2. Оберните результат в ИНДЕКС по столбцу кодов — получится формула из первой строки таблицы ниже.
3. Нажмите Enter — в ячейке появится код 103.
4. Проверьте формулу на названии, которого нет в справочнике: исправная связка покажет #Н/Д, а не чужой код.
5. Перед протягиванием закрепите оба диапазона клавишей F4: $A$2:$A$5 и $B$2:$B$5.

Та же схема работает в любую сторону: меняются только два диапазона — где искать и откуда брать ответ.

Каждая строка таблицы берёт ответ из столбца, заданного первым аргументом. Почему без знака доллара диапазоны сползают, объяснено на странице о знаке доллара в ссылках и клавише F4.

Выборка на пересечении строки и столбца

Двумерный поиск — сильная сторона связки: вторая ПОИСКПОЗ подставляет номер столбца. На листе продаж в G1 записано «Петрова», в G2 — «Март».

Формула =ИНДЕКС(B2:D4;ПОИСКПОЗ(G1;A2:A4;0);ПОИСКПОЗ(G2;B1:D1;0)) вернёт 128: первая ПОИСКПОЗ нашла строку менеджера, вторая — столбец месяца. Замените в G2 «Март» на «Январь», и результат станет 95 без правки формулы. Соберите такую формулу в тренажёре: урок подставляет другие данные и проверяет, что связка находит верную ячейку.

Однако связка остаётся точной, только пока в ПОИСКПОЗ стоит правильный тип сопоставления.

Тип сопоставления: 0, 1 или -1

ПОИСКПОЗ меняет поведение в зависимости от третьего аргумента: 0 ищет точное совпадение, 1 и -1 — ближайшее значение в отсортированном списке. Справка Microsoft задаёт для каждого типа своё требование к данным^[2].

На данных из справки в B2:B5 стоят четыре числа.

Формула =ПОИСКПОЗ(41;B2:B5;0) вернёт 4, а =ПОИСКПОЗ(39;B2:B5;1) — 2: точного совпадения нет, и берётся позиция ближайшего меньшего значения, 38. Запись =ПОИСКПОЗ(40;B2:B5;-1) выдаст #Н/Д, потому что список не упорядочен по убыванию^[2].

По умолчанию ПОИСКПОЗ работает с типом 1^[2], и в этом главная ловушка связки. На несортированном справочнике кодов формула без нуля =ИНДЕКС(A2:A5;ПОИСКПОЗ("Чай";B2:B5)) вернёт 104 — код какао вместо кода чая, и никакого сообщения Excel не покажет.

Регистр букв ПОИСКПОЗ не различает^[2]: «чай» и «Чай» для неё одинаковы. Подстановочные знаки работают только при типе 0 и текстовом искомом значении: ? заменяет один символ, * — любую последовательность, а тильда перед знаком ищет сам знак^[2]. Запись =ПОИСКПОЗ("Ка*";B2:B5;0) на справочнике кодов вернёт 4 — позицию «Какао».

В Google Таблицах у ПОИСКПОЗ те же три типа и то же значение по умолчанию — 1, а двумерный диапазон там возвращает #N/A^[4]. Именно поэтому первой строкой диагностики любой ошибки связки стоит проверка нуля в ПОИСКПОЗ.

Ошибки связки: #Н/Д, #ССЫЛКА! и сдвиг на строку

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

Сдвиг на строку — самая тихая из ошибок. Формула =ИНДЕКС(A2:A5;ПОИСКПОЗ("Чай";B1:B5;0)) вернёт 104 вместо 103: заголовок попал в диапазон поиска, и позиция выросла на единицу, хотя столбец ответа по-прежнему отсчитывается от второй строки.

Внешняя функция выдаёт #ССЫЛКА!, если номер строки или столбца не указывает на ячейку внутри массива^[3]. Запись =ИНДЕКС(A2:A5;5) просит пятую ячейку у диапазона из четырёх — отсюда ошибка. В ссылочной форме справка требует держать все области на одном листе^[3].

ЕСЛИОШИБКА не исправляет причину, а только прячет её. Сначала убедитесь, что ключа действительно нет, и лишь затем подписывайте пустой результат: =ЕСЛИОШИБКА(ИНДЕКС(C2:C5;ПОИСКПОЗ("Мёд";B2:B5;0));"нет") вернёт «нет».

Остаётся понять, когда связку лучше заменить другим инструментом.

Когда связка не подойдёт

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

• Если у ключа несколько строк и нужны все, связка вернёт только первую: тип 0 находит первое значение, точно равное искомому^[2].
• При ключе из двух полей, например товар и склад, одного поиска позиции не обойтись — приёмы собраны на странице о поиске по двум и более признакам.
• Если диапазон поиска охватывает несколько строк и столбцов, формула вернёт #Н/Д: по справке Google аргумент «диапазон» должен быть одномерным^[4].
• Если области ссылочной формы лежат на разных листах, функция вернёт ошибку: справка требует держать их на одном листе^[3].
• Когда файл открывают только в новых версиях, ту же задачу короче решает одна функция ПРОСМОТРX — по справке Microsoft её нет в Excel 2016 и Excel 2019^[5].

Совместимость — главный козырь связки: блок «Применяется к» в справке Microsoft перечисляет для обеих функций Microsoft 365, Excel 2024 и Excel 2021 вместе с версиями для Mac, а также Excel 2019 и Excel 2016^[2]. Поэтому формулу безопасно отправлять коллегам со старым Excel.

Для сверки с соседними приёмами пригодится обзор основных формул Excel для таблиц. Там же — функции подстановки и выборки, которые дополняют связку в отчётах.