Объединение INDEX и MATCH

Теперь Вы знаете две составляющие поиска. MATCH находит, где находится значение, а INDEX возвращает значение, расположенное на определённой позиции.

Объединив их, Вы получите полноценный поиск: MATCH находит строку, а INDEX извлекает данные из этой строки в любом выбранном Вами столбце.

Схема проста, если её понять: поместите MATCH внутрь INDEX, на место номера строки.

Вот форма, которую Вы будете использовать снова и снова:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Читайте формулу изнутри наружу. Сначала выполняется MATCH и возвращает номер позиции. Затем это число становится значением row_num для INDEX, которая возвращает значение из диапазона результатов.

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

Представьте таблицу, где в столбце A находятся названия товаров, а в столбце C — цены. Вам нужно узнать цену товара «Вишня».

Сначала MATCH находит Вишню: =MATCH("Cherry", A2:A20, 0) возвращает, например, 3.

Затем INDEX использует это число 3: =INDEX(C2:C20, 3) возвращает цену из 3-й строки столбца C.

Вложив одну функцию в другую, Вы получаете результат за один шаг: =INDEX(C2:C20, MATCH("Cherry", A2:A20, 0)).

Жёстко заданное значение «Cherry» удобно для обучения, но в реальных формулах вместо него указывают ссылку на ячейку. Введите искомый текст в E1 и используйте ссылку на эту ячейку.

=INDEX(C2:C20, MATCH(E1, A2:A20, 0))

Теперь любой товар, который Вы введёте в E1, сразу вернёт свою цену. Введите Banana — получите цену Банана; введите Date — и результат обновится.

Одна формула превращается в многоразовый инструмент поиска, полностью управляемый входной ячейкой.

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

Предположим, цены находятся в столбце A, а названия товаров — в столбце C. Чтобы найти цену товара по названию, используйте =INDEX(A2:A20, MATCH(E1, C2:C20, 0)).

Поиск выполнялся в столбце C, а результат возвращался из столбца A. VLOOKUP не умеет делать это без дополнительных средств.

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

Найдите адрес электронной почты клиента: =INDEX(D2:D50, MATCH(E1, A2:A50, 0)).

Вместо этого найдите город того же клиента: =INDEX(F2:F50, MATCH(E1, A2:A50, 0)).

Часть MATCH остаётся неизменной; меняется только диапазон INDEX, чтобы выбрать другой результат.

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

=INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0))

Первый MATCH находит строку по меткам в A, второй — столбец по заголовкам в строке 1. INDEX возвращает ячейку на их пересечении. Этот продвинутый приём подробно рассматривается далее.

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

Если MATCH ищет в A2:A20 (19 строк), а INDEX возвращает результат из C2:C19 (18 строк), позиции смещаются и вы получаете неправильный результат.

Полезное правило: используйте для обоих диапазонов один и тот же интервал строк, например A2:A20 и C2:C20. Ссылки на целые столбцы, такие как A:A и C:C, также автоматически остаются согласованными.

Если MATCH не может найти искомое значение, он возвращает #N/A, и вся связка INDEX-MATCH показывает эту ошибку. Оберните её в IFNA, чтобы задать понятное значение по умолчанию.

=IFNA(INDEX(C2:C20, MATCH(E1, A2:A20, 0)), "Not found")

Теперь для отсутствующего товара отображается текст «Not found», а не непонятное сообщение об ошибке. IFERROR тоже подходит, но IFNA обрабатывает только случай отсутствия результата и позволяет другим ошибкам проявиться.

Объединим всё. У вас есть таблица сотрудников: ID в столбце A, имена в B, отделы в C, зарплаты в D. Пользователь вводит ID в G1.

Чтобы вернуть отдел этого сотрудника: =INDEX(C2:C200, MATCH(G1, A2:A200, 0)).

Чтобы вместо этого вернуть его зарплату, замените диапазон INDEX на D2:D200. Логика поиска не меняется — меняется только столбец, из которого считывается результат. Это основной рабочий инструмент для повседневного динамического поиска.

Когда формула выглядит пугающе, вычисляйте её так же, как это делает электронная таблица, — от самой внутренней функции к внешней.

Для =INDEX(C2:C20, MATCH(E1, A2:A20, 0)): сначала разберите MATCH(E1, A2:A20, 0), представьте, что он возвращает число, например 5, затем мысленно подставьте его и получите =INDEX(C2:C20, 5).

Внезапно формула превращается просто в «вернуть 5-ю цену». Эта привычка упрощает отладку любого вложенного поиска.

Убедитесь, что вы понимаете, как эти две функции работают вместе.

Вы объединили две функции в гибкий поиск:

• Шаблон: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
• MATCH находит позицию строки; INDEX возвращает значение в этой позиции
• Столбцы поиска и результата независимы, поэтому искать слева так же легко, как и справа
• Следите, чтобы оба диапазона имели одинаковую высоту, и оборачивайте формулу в IFNA для понятной обработки ошибок

Далее разберитесь, почему этот подход часто лучше VLOOKUP.