Двумерный поиск в Excel: связка ИНДЕКС и ПОИСКПОЗ

Иван Корнев·21.05.2024·⏱4 мин

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

Принцип работы дуэта функций

Эти две функции компенсируют недостатки друг друга, создавая гибкий инструмент поиска:

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

Главное преимущество перед ВПР: вы можете искать значение в любом столбце таблицы и возвращать результат из любого другого, даже если он находится левее искомого.

Синтаксис и аргументы

Для корректного составления формулы важно понимать параметры каждой функции.

Функция ПОИСКПОЗ

Возвращает относительную позицию элемента в диапазоне.

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

искомое_значение: то, что вы ищете (текст, число, ссылка).
просматриваемый_массив: одна строка или один столбец, где ведется поиск.
тип_сопоставления:
- 0 — точное совпадение (используется в 95% случаев).
- 1 — ближайшее меньшее (требует сортировки по возрастанию).
- -1 — ближайшее большее (требует сортировки по убыванию).

Функция ИНДЕКС

Возвращает значение ячейки на пересечении указанных координат.

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

массив: вся таблица данных или конкретный диапазон, откуда нужно забрать результат.
номер_строки: результат функции ПОИСКПОЗ для вертикального поиска.
номер_столбца: результат функции ПОИСКПОЗ для горизонтального поиска (опционально, если массив одномерный).

Практический пример: поиск продаж по региону и месяцу

Представим таблицу продаж за первый квартал:

	A	B	C	D
1	Регион	Янв	Фев	Мар
2	Москва	100	120	150
3	СПб	80	90	110
4	Екат	70	85	95

Задача: Найти объем продаж для города Екатеринбург за месяц Февраль.

Логика построения формулы:

Находим номер строки с городом "Екат" в диапазоне A2:A4.
Находим номер столбца с месяцем "Фев" в диапазоне B1:D1.
Извлекаем данные из общей матрицы B2:D4 по найденным координатам.

Итоговая формула:

=ИНДЕКС(B2:D4; ПОИСКПОЗ("Екат"; A2:A4; 0); ПОИСКПОЗ("Фев"; B1:D1; 0))

Результат: 85.

ПОИСКПОЗ("Екат"...) вернет число 3 (третья строка в диапазоне A2:A4).
ПОИСКПОЗ("Фев"...) вернет число 2 (второй столбец в диапазоне B1:D1).
ИНДЕКС возьмет значение из 3-й строки и 2-го столбца диапазона B2:D4.

При копировании формулы обязательно фиксируйте диапазоны знаком доллара ($), например $A$2:$A$4, чтобы ссылки не сместились.

Пошаговый алгоритм создания универсальной формулы

Чтобы сделать таблицу интерактивной (меняешь название города/месяца — меняется ответ):

Создайте ячейки для ввода критериев поиска (например, F1 для города, G1 для месяца).
Выделите диапазон данных с продажами (B2:D4) и нажмите F4, чтобы закрепить его ($B$2:$D$4).
Напишите первый ПОИСКПОЗ для поиска строки: укажите ссылку на ячейку с городом (F1) и диапазон названий городов ($A$2:$A$4). Тип сопоставления — 0.
Напишите второй ПОИСКПОЗ для поиска столбца: укажите ссылку на ячейку с месяцем (G1) и диапазон заголовков месяцев ($B$1:$D$1). Тип сопоставления — 0.
Объедините всё внутри ИНДЕКС.

Конструкция будет выглядеть так: =ИНДЕКС($B$2:$D$4; ПОИСКПОЗ($F$1; $A$2:$A$4; 0); ПОИСКПОЗ($G$1; $B$1:$D$1; 0))

Частые ошибки и способы их устранения

Даже опытные пользователи сталкиваются с типовыми проблемами при использовании этой связки.

Ошибка	Причина возникновения	Решение
#Н/Д	Искомое значение не найдено в диапазоне. Часто из-за лишних пробелов или разного регистра.	Используйте функцию `СЖПРОБЕЛЫ` для очистки данных или убедитесь, что третий аргумент `ПОИСКПОЗ` равен `0`.
#ССЫЛКА!	Номер строки или столбца выходит за пределы указанного массива в `ИНДЕКС`.	Проверьте, чтобы диапазоны в `ПОИСКПОЗ` полностью соответствовали размерам массива в `ИНДЕКС`.
#ЗНАЧ!	В аргументах указан текст вместо числа или неверный тип данных.	Убедитесь, что ищете число в числовом диапазоне, а текст — в текстовом.

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

Для предотвращения вывода ошибок пользователю оберните формулу в ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ИНДЕКС(...); "Данные не найдены")

Сравнение с функцией ВПР (VLOOKUP)

Почему профессионалы предпочитают связку ИНДЕКС + ПОИСКПОЗ классическому ВПР:

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

Хотя в современных версиях Excel появилась функция ПРОСМОТРX (XLOOKUP), которая объединяет возможности обоих методов, понимание работы ИНДЕКС и ПОИСКПОЗ остается фундаментальным навыком для работы с любыми версиями программы.

FAQ

Можно ли использовать эту связку для поиска по нескольким условиям? Да. Можно создать формулу массива (в старых версиях через Ctrl+Shift+Enter), где ПОИСКПОЗ ищет комбинацию условий, либо использовать вспомогательный столбец, объединяющий критерии.

Что делать, если нужно вернуть несколько значений по одному запросу? Стандартная связка возвращает только первое совпадение. Для вывода списка всех подходящих значений лучше использовать функцию ФИЛЬТР (FILTER) в Excel 365/2021 или сводные таблицы.

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