Двунаправленный поиск с INDEX-MATCH-MATCH

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

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

Классический инструмент для этого — INDEX в сочетании с двумя вызовами MATCH, что часто записывают как INDEX-MATCH-MATCH.

INDEX возвращает значение из диапазона по его позиции. Полная форма: INDEX(array, row_num, column_num).

Передайте функции блок ячеек, номер строки и номер столбца — и она вернёт значение в указанной точке. Например, если таблица начинается в B2, запрос строки 3 и столбца 2 вернёт значение, находящееся на 3 строки ниже и на 2 столбца правее внутри этого блока.

Главная идея: INDEX нужны позиции, а не названия. Именно их предоставляет MATCH.

MATCH находит позицию значения внутри одной строки или одного столбца. Форма функции: MATCH(lookup_value, lookup_array, match_type).

Используйте 0 в качестве типа совпадения для точного совпадения. Результат — число, показывающее позицию значения при отсчёте от 1.

Если "East" — второй элемент диапазона A2:A5, MATCH вернёт 2. Это число можно использовать как номер строки для INDEX.

Для поиска по двум направлениям запустите MATCH дважды:

• Одна функция MATCH находит строку, в которой находится нужный регион.
• Другая функция MATCH находит столбец, в котором находится нужный месяц.

Затем передайте оба числа функции INDEX. MATCH для строки ищет в вертикальном диапазоне названий, а MATCH для столбца — в горизонтальном диапазоне заголовков.

Результатом становится единственная ячейка на пересечении этой строки и этого столбца.

Представьте такую структуру. Названия регионов находятся в A2:A5 (East, West, North, South). Заголовки месяцев находятся в B1:D1 (Jan, Feb, Mar). Фактические данные о продажах заполняют диапазон B2:D5.

Поиск управляется двумя входными ячейками: в G1 указан нужный регион, а в G2 — нужный месяц.

Наша цель — создать одну формулу, которая считывает значения G1 и G2 и возвращает соответствующий показатель продаж из диапазона B2:D5.

Сначала найдите регион. MATCH ищет значение, введённое в G1, в вертикальном списке названий A2:A5.

Если в G1 указано "North", а North является третьим названием, MATCH вернёт 3.

Это число сообщает INDEX, какую строку блока данных нужно прочитать. Обратите внимание: поиск выполняется только в A2:A5, то есть в названиях, а не в данных. Поэтому позиция 3 соответствует третьей строке данных.

Теперь найдите месяц. Эта функция MATCH ищет значение из G2 в горизонтальном ряду заголовков B1:D1.

Если в G2 указано "Feb", а Feb является вторым заголовком, MATCH вернёт 2.

Это число станет позицией столбца для INDEX. Как и при поиске строки, мы ищем только в заголовках B1:D1, чтобы позиция соответствовала столбцам данных в B2:D5.

Теперь поместите оба вызова MATCH внутрь INDEX. Блок данных B2:D5 является массивом, MATCH для строки предоставляет номер строки, а MATCH для столбца — номер столбца.

Если G1 содержит "North", а G2 — "Feb", MATCH для строки возвращает 3, а MATCH для столбца — 2. Поэтому INDEX возвращает значение в строке 3 и столбце 2 диапазона B2:D5.

Эта единственная формула выполняет полный поиск по двум направлениям.

Предположим, в диапазоне B2:D5 находятся такие данные: для North указано: январь — 50, февраль — 80, март — 65.

• MATCH("North", A2:A5, 0) возвращает 3.
• MATCH("Feb", B1:D1, 0) возвращает 2.
• INDEX(B2:D5, 3, 2) считывает строку 3, столбец 2 и возвращает 80.

Измените G1 на "East" или G2 на "Mar" — и вся формула мгновенно пересчитается. В этом и заключается преимущество использования двух поисков MATCH в INDEX.

VLOOKUP выполняет поиск только в первом столбце и возвращает значение из столбца, находящегося на фиксированном расстоянии справа. Чтобы переключать месяцы, пришлось бы вручную задавать или вычислять индекс столбца.

С помощью INDEX-MATCH-MATCH и строка, и столбец могут динамически выбираться по названию. Можно менять порядок столбцов и добавлять новые месяцы — формула всё равно будет работать, потому что ищет по тексту заголовка, а не по фиксированному номеру.

Самая распространённая ошибка — несоответствие диапазонов. Диапазон MATCH для поиска строки должен иметь такую же высоту, как и блок данных INDEX, а диапазон MATCH для поиска столбца — такую же ширину.

Здесь A2:A5 содержит 4 строки, и B2:D5 также содержит 4 строки, поэтому результат MATCH, равный 3, действительно означает третью строку данных. Если случайно выполнить поиск в A1:A5, включая заголовок, позиции сместятся на одну строку и будет выбрана неправильная ячейка.

Проверьте, насколько хорошо Вы поняли принцип двустороннего поиска.

Вы изучили принцип двустороннего поиска:

• INDEX возвращает значение по позиции строки и столбца внутри блока.
• Один MATCH находит строку, выполняя поиск по вертикальным названиям.
• Второй MATCH находит столбец, выполняя поиск по горизонтальным заголовкам.

Объединённая формула =INDEX(data, MATCH(row), MATCH(col)) считывает два входных значения и возвращает значение на их пересечении. Чтобы избежать смещения, диапазоны MATCH должны быть такого же размера, как и блок данных.