INDEX MATCH в Excel: формула, примеры и распространённые ошибки

By Mirko Peters · 2026-11-12

INDEX MATCH в Excel: как работает формула, как действует каждая функция, поиск влево и в двух измерениях, поиск по нескольким условиям и распространённые ошибки, а также сравнение с VLOOKUP.

Automatically translated with AI from the original post. Read the English original

INDEX MATCH в Excel — это формула поиска, построенная из двух функций: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). MATCH находит, где искомое значение расположено в списке, а INDEX возвращает элемент из той же позиции в другом списке. Эта статья предназначена для пользователей Excel, которые пишут формулы VLOOKUP и упираются в её ограничения, или для тех, кто получил книгу с большим количеством INDEX MATCH и хочет разобраться в ней. Вы увидите каждую функцию по отдельности, то, как они работают вместе, поиск влево, поиск в двух измерениях, использование двух условий и значение ошибок.

Ключевые выводы

• INDEX MATCH — это две функции в одной формуле: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). MATCH находит позицию, а INDEX возвращает элемент в этой позиции.
• Используйте 0 в качестве третьего аргумента MATCH для точного совпадения. Если оставить значение по умолчанию 1, MATCH считает, что данные отсортированы по возрастанию.
• Поскольку диапазоны поиска и возвращаемых значений разделены, INDEX MATCH может искать значения слева от столбца поиска, чего VLOOKUP делать не умеет.
• Для двух условий либо объедините столбцы во вспомогательном столбце, либо используйте формулу, которая перемножает два сравнения внутри MATCH.
• Оберните формулу в IFNA, чтобы заменить #N/A на сообщение, а другие ошибки продолжали отображаться, так что реальные ошибки не будут скрыты.

INDEX MATCH можно встретить во многих старых книгах, потому что он работал во всех версиях Excel задолго до появления XLOOKUP. Он и сейчас работает, и это главная причина его изучить. Подробности ниже взяты со страниц Microsoft про MATCH, INDEX и VLOOKUP.

Что делает INDEX

INDEX возвращает значение из позиции внутри диапазона. На странице Microsoft форма для массива записана как INDEX(array, row_num, [column_num]).

• array — это диапазон, из которого берётся значение.
• row_num — это строка внутри этого диапазона.
• column_num — это столбец внутри этого диапазона. Если в диапазоне только один столбец, его можно не указывать.

Отсчёт ведётся от верхней левой ячейки диапазона, а не от листа. В примере Microsoft в блоке в первом столбце стоят Apples и Bananas, во втором — Lemons и Pears. =INDEX(A2:B3,2,2) возвращает Pears, а =INDEX(A2:B3,2,1) возвращает Bananas.

Два правила с той же страницы стоит запомнить. Если row_num или column_num указывает за пределы диапазона, INDEX возвращает ошибку #REF!. А если задать row_num или column_num равным 0, INDEX вернёт всю строку или весь столбец. В текущих версиях Microsoft 365 формулу вводят обычным нажатием Enter. В старых версиях её вводят как формулу массива сочетанием Ctrl+Shift+Enter.

Сам по себе INDEX требует, чтобы вы уже знали позицию. Здесь на помощь приходит MATCH.

Что делает MATCH

MATCH ищет значение в диапазоне и возвращает позицию. Microsoft записывает синтаксис как MATCH(lookup_value, lookup_array, [match_type]). Функция возвращает не сам элемент, а только его относительную позицию в диапазоне.

Третий аргумент определяет способ сопоставления:

• 0 находит первое точное совпадение. Диапазон может быть в любом порядке. Этот вариант вы будете использовать чаще всего.
• 1, которое также является значением по умолчанию, если аргумент опущен, находит наибольшее значение, которое меньше или равно искомому. Диапазон должен быть отсортирован по возрастанию.
• -1 находит наименьшее значение, которое больше или равно искомому. Диапазон должен быть отсортирован по убыванию.

В примерах Microsoft используются значения 25, 38, 40 и 41 в диапазоне B2:B5. =MATCH(39,B2:B5,1) возвращает 2 — позицию значения 38, ближайшего меньшего. =MATCH(41,B2:B5,0) возвращает 4. А =MATCH(40,B2:B5,-1) возвращает #Н/Д, потому что эти значения не отсортированы по убыванию.

Ещё несколько особенностей из страницы: поиск текста не учитывает регистр, поэтому «apple» и «APPLE» считаются одинаковыми. При типе сопоставления 0 и текстовом искомом значении ? соответствует любому одному символу, а * — любой последовательности символов, а тильда перед любым из них означает поиск самого символа. Если MATCH ничего не находит, она возвращает #Н/Д.

Значение 1 по умолчанию часто приводит к ошибкам. Если вы забудете третий аргумент, MATCH молча предположит, что данные отсортированы, и может вернуть неверную позицию без какой-либо ошибки. Почти во всех поисках по идентификаторам, именам или кодам указывайте 0.

Соберём всё вместе

Схема всегда одна и та же: MATCH вкладывается в INDEX и передаёт номер строки.

=INDEX(диапазон_результата, MATCH(искомое_значение, диапазон_поиска, 0))

Возьмём лист с табельными номерами сотрудников в A2:A100, именами в B2:B100 и подразделениями в C2:C100. Вы вводите табельный номер в F2 и хотите получить подразделение.

1. Выберите ячейку для результата, например G2.
2. Введите =INDEX(C2:C100,, чтобы указать, откуда брать ответ.
3. Добавьте MATCH(F2,A2:A100,0)), чтобы указать, где искать табельный номер.
4. Нажмите Enter.

Готовая формула: =INDEX(C2:C100,MATCH(F2,A2:A100,0)). MATCH находит F2 в A2:A100 и возвращает, например, 14. INDEX затем возвращает 14-й элемент из C2:C100.

Держите диапазоны одинаковой длины и с одной начальной строки. Если MATCH ищет в A2:A100, а INDEX читает C3:C101, позиция указывает на неверную строку, и ответ тихо окажется неправильным. Используйте одинаковые первую и последнюю строки для обоих диапазонов и зафиксируйте их знаками доллара, $A$2:$A$100, если собираетесь копировать формулу вниз.

Поиск влево и отличие от VLOOKUP

На странице Microsoft о VLOOKUP синтаксис записан так: VLOOKUP(искомое_значение, таблица, номер_столбца, [интервальный_просмотр]). Там сказано, что искомое значение должно находиться в первом столбце диапазона, а если не указывать интервальный_просмотр, поиск будет приблизительным и предполагает отсортированные данные. На странице также предлагается попробовать XLOOKUP.

Вот одна и та же задача, решённая двумя способами: табельные номера в столбце A, имена в столбце B:

• =VLOOKUP(F2,A2:B100,2,FALSE)
• =INDEX(B2:B100,MATCH(F2,A2:A100,0))

У INDEX MATCH есть два реальных преимущества. Во-первых, он умеет искать влево. Если имена находятся в столбце B, а табельный номер нужно получить из столбца A, достаточно поменять диапазоны местами: =INDEX(A2:A100,MATCH(F2,B2:B100,0)). VLOOKUP так не может, потому что искомое значение должно стоять в первом столбце. Во-вторых, он ссылается на столбец результата напрямую, а не отсчитывает его. В VLOOKUP число 2 в формуле означает «второй столбец диапазона». Вставьте столбец внутрь диапазона, и число будет указывать на другой столбец. В INDEX MATCH ссылка корректируется так же, как любая другая ссылка.

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

Двусторонний поиск

Двусторонний поиск находит значение на пересечении строки и столбца, например продажи одного продукта за один месяц. Используйте две функции MATCH: одну для строки и одну для столбца:

=INDEX(B2:M20,MATCH(P1,A2:A20,0),MATCH(Q1,B1:M1,0))

Здесь B2:M20 содержит числа, A2:A20 содержит названия товаров, B1:M1 содержит заголовки месяцев, а в P1 и Q1 находятся нужные товар и месяц. Первая функция MATCH возвращает номер строки, вторая возвращает номер столбца, а INDEX выводит число на их пересечении. Обратите внимание, что диапазон возвращаемых значений начинается с B2, а не с A1, чтобы позиции совпадали с диапазонами подписей: оба диапазона MATCH тоже начинаются с той же строки или того же столбца, что и числа.

Поиск по двум условиям

MATCH принимает одно искомое значение. Если запись определяется двумя полями, например категорией и статусом, у вас есть два варианта.

Вспомогательный столбец. Добавьте столбец, который объединяет два поля, например =A2&"|"&B2 в D2, и протяните формулу вниз. Затем ищите объединённый текст:

=INDEX(C2:C100,MATCH(F2&"|"&G2,D2:D100,0))

Разделитель важен. Без него «AB» + «C» и «A» + «BC» дают одно и то же значение «ABC». Вспомогательный столбец легко читать и проверять, и он работает во всех версиях.

Формула массива. Без вспомогательного столбца можно перемножить два сравнения:

=INDEX(C2:C100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0))

Каждое сравнение возвращает TRUE или FALSE. Умножение превращает их в 1 только там, где оба условия выполняются, а MATCH ищет первую единицу. Этого нет на страницах Microsoft про INDEX и MATCH, поэтому проверьте формулу на небольшом диапазоне. В старых версиях Excel её, возможно, придётся ввести сочетанием клавиш Ctrl+Shift+Ввод, так же как Microsoft описывает для результатов массивов в INDEX. В актуальной Microsoft 365 достаточно клавиши Ввод.

Подстановочные знаки и ближайшие значения

При типе сопоставления 0 и текстовых значениях MATCH поддерживает подстановочные знаки. Формула =INDEX(B2:B100,MATCH("Jan*",A2:A100,0)) возвращает элемент, стоящий рядом с первой записью, которая начинается с «Jan». Это полезно для частичных кодов. Делайте шаблон достаточно конкретным, поскольку MATCH останавливается на первом совпадении.

Для шкал, например налоговых ставок или комиссионных, намеренно используйте тип сопоставления 1 (по умолчанию), при условии что пороговые значения отсортированы по возрастанию. Поместите 0, 1000 и 5000 в A2:A4, а ставки в B2:B4. Тогда =INDEX(B2:B4,MATCH(E2,A2:A4,1)) вернёт ставку для наибольшего порога, который не превышает E2. Это одна из немногих задач, где сортированный вариант по умолчанию является правильным выбором.

Устранение ошибок

• #N/A. MATCH не находит совпадений. Проверьте опечатки, лишние пробелы в конце и числа, сохранённые как текст, ведь «100» и 100 — это разные значения. Убедитесь, что тип сопоставления равен 0, а для типов 1 или -1, которым нужна сортировка, данные действительно отсортированы.
• #REF!. INDEX запросили строку или столбец за пределами диапазона. Это также происходит, когда вы удаляете строку или столбец, которые использует формула. Проверьте, что диапазоны MATCH и INDEX имеют одинаковый размер.
• Неверный ответ без ошибки. Обычно это пропущенный 0 в MATCH или диапазоны, которые начинаются с разных строк.
• Неверный ответ после копирования вниз. Диапазоны сместились, потому что не были закреплены. Добавьте $ к диапазонам.

Чтобы заменить ошибку понятным сообщением, функция IFNA от Microsoft возвращает значение, которое вы выбираете, когда формула выдаёт #N/A, и пропускает любые другие ошибки без изменений:

=IFNA(INDEX(C2:C100,MATCH(F2,A2:A100,0)),"Not found")

Для поиска предпочитайте IFNA вместо IFERROR. IFERROR скрывает все ошибки, включая #REF!, возникающую из-за повреждённого диапазона, поэтому реальная неисправность выглядит как обычное «Not found». IFNA охватывает только ожидаемый случай. Microsoft указывает IFNA для Excel 2016 и более новых версий.

INDEX MATCH или XLOOKUP

Если у всех, кто открывает ваш файл, установлена актуальная Microsoft 365, XLOOKUP выполняет ту же задачу одной функцией со встроенным сообщением для случая, когда значение не найдено. Мы разбираем её в нашем руководстве по XLOOKUP в Excel. Но страница Microsoft про XLOOKUP не даёт ясного ответа по Excel 2016 и 2019 и не упоминает Excel для веба. INDEX MATCH работает во всех них. Поэтому выбор практический: используйте XLOOKUP в файлах для актуальных версий, а INDEX MATCH — когда файл передаётся людям со старыми версиями или когда вы поддерживаете книгу, которая уже его использует.

Распространённые ошибки

• Пропуск 0 в MATCH и доверие значению по умолчанию.
• Использование для INDEX и MATCH диапазонов разного размера или с разными начальными строками.
• Забытое закрепление диапазонов с помощью $ перед копированием формулы вниз.
• Объединение условий без разделителя во вспомогательном столбце.
• Оборачивание всего в IFERROR, из-за чего сломанные формулы выглядят как отсутствующие данные.
• Ожидаете, что INDEX MATCH вернёт все совпадения. Он возвращает только первое.

Связанные темы для изучения

• XLOOKUP. Более новая функция поиска со встроенными параметрами направления поиска и значения, возвращаемого при отсутствии совпадения.
• Несколько результатов сразу. INDEX MATCH возвращает только первое совпадение. Для вывода всех совпадений в версиях, где она есть, используйте функцию FILTER.
• Таблицы и именованные диапазоны. Структурированные ссылки и имена упрощают чтение формул и позволяют диапазону расти вместе с данными.
• Производительность на больших листах. Используйте ограниченные диапазоны вместо целых столбцов и держите диапазоны компактными.
• Поиск с учётом регистра. MATCH не различает регистр, поэтому для поиска с учётом регистра нужен другой подход. Проверьте любое обходное решение, прежде чем на него полагаться.

Следующий шаг

Перепишите одну из ваших формул VLOOKUP в виде INDEX MATCH и сравните результат на нескольких известных значениях. Затем попробуйте ту же формулу с поиском влево. Сделав оба варианта, вы поймёте, когда INDEX MATCH в Excel — лучший инструмент для задачи.