Excel INDEX MATCH: базовый и расширенный поиск

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

• Функция INDEX
• Функция MATCH
• Комбинирование INDEX и MATCH

• Двунаправленный поиск
• Поиск слева
• Поиск с учётом регистра
• Поиск ближайшего совпадения
• Поиск по нескольким критериям
• Поиск по нескольким столбцам
• Поиск первого непустого значения
• Поиск первого числового значения
• Поиск по максимальным или минимальным значениям
• Совет: Настройте собственные сообщения об ошибке #N/A

Как использовать INDEX и MATCH в Excel

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

Как использовать функцию INDEX в Excel

Функция INDEX в Excel возвращает значение из заданного местоположения в определённом диапазоне. Синтаксис функции INDEX следующий:

• array (обязательный параметр) — диапазон, из которого требуется вернуть значение.
• row_num (обязательный, если не указан column_num) — номер строки в массиве.
• column_num (необязательный, но обязателен, если row_num опущен) — номер столбца в массиве.

Например, чтобы узнать балл Джеффа, шестого ученика в списке, можно использовать функцию INDEX следующим образом:

=INDEX(C2:C11,6)

√ Примечание: Диапазон C2:C11 содержит список баллов, а число 6 определяет результат для 6 ученика.

Давайте проведём небольшой тест. Для формулы =INDEX(A1:C1,2) какое значение она вернёт? --- Верно, это будет Birth date, второе значение в указанной строке.

Теперь мы знаем, что функция INDEX отлично работает как с горизонтальными, так и с вертикальными диапазонами. Но что делать, если нужно вернуть значение из большего диапазона с несколькими строками и столбцами? В этом случае необходимо указать и номер строки, и номер столбца. Например, чтобы найти балл Джеффа в диапазоне таблицы, а не в одном столбце, можно определить его результат по номеру строки6 и номеру столбца3 в диапазоне ячеек от A2 до C11 следующим образом:

=INDEX(A2:C11,6,3)

Однако для действительно большой базы данных с множеством строк и столбцов неудобно вручную указывать точные номера строк и столбцов в формуле. В таких случаях рекомендуется использовать функцию MATCH совместно с INDEX.

Как использовать функцию MATCH в Excel

Функция MATCH в Excel возвращает числовое значение — позицию определённого элемента в заданном диапазоне. Синтаксис функции MATCH следующий:

• lookup_value (обязательный параметр) — значение, которое требуется найти в lookup_array.
• lookup_array (обязательный параметр) — диапазон ячеек, в котором MATCH будет выполнять поиск.
• match_type (необязательно): 1, 0 или -1.
  • 1(по умолчанию) MATCH найдёт наибольшее значение, которое меньше или равно lookup_value. Значения в lookup_array должны быть отсортированы по возрастанию.
  • 0, MATCH найдёт первое значение, которое точно равно lookup_value. Значения в lookup_array могут быть в любом порядке. (Если тип совпадения установлен в0, можно использовать подстановочные знаки.)
  • -1, MATCH найдёт наименьшее значение, которое больше или равно lookup_value. Значения в lookup_array должны быть отсортированы по убыванию.

Например, чтобы узнать позицию Веры в списке имён, используйте формулу MATCH следующим образом:

=MATCH("Vera",A2:A11,0)

√ Примечание: Результат «4» означает, что имя «Вера» находится на4-й позиции в списке.

Теперь, когда мы ознакомились с основами использования функций INDEX и MATCH в Excel, давайте перейдём к их совместному применению.

Как объединить INDEX и MATCH в Excel

Посмотрите пример ниже, чтобы понять, как можно объединить функции INDEX и MATCH:

Чтобы узнать балл Эвелин, зная, что оценки находятся в третьем столбце, мы можем использовать функцию MATCH для автоматического определения позиции строки, без необходимости считать её вручную. Затем с помощью функции INDEX получаем значение на пересечении найденной строки и третьего столбца:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Поскольку формула может показаться сложной, давайте разберём её по частям.

Формула INDEX содержит три аргумента:

• row_num: MATCH("Evelyn",A2:A11,0)определяет для INDEX позицию строки, где находится значение "Evelyn" в диапазоне A2:A11, а именно5.
• column_num:3 указывает третий столбец, в котором INDEX ищет оценку в массиве.
• array: A2:C11 указывает INDEX вернуть совпадающее значение на пересечении заданной строки и столбца в диапазоне от A2 до C11. В результате мы получаем90.

В приведённой выше формуле использовано жёстко заданное значение "Evelyn". Однако на практике жёстко заданные значения неудобны, так как их придётся менять каждый раз при поиске других данных, например, оценки другого ученика. В таких случаях рекомендуется использовать ссылки на ячейки для создания динамических формул. Например, здесь я заменю "Evelyn" на F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Примеры формул INDEX и MATCH

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

INDEX и MATCH для двунаправленного поиска

В предыдущем примере мы знали номер столбца и использовали формулу MATCH для поиска номера строки. А что делать, если мы не уверены и в номере столбца?

В таких случаях можно выполнить двунаправленный (матричный) поиск, используя две функции MATCH: одну для поиска строки, другую — для определения столбца. Например, чтобы узнать балл Эвелин, используйте формулу:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX и MATCH для поиска слева

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

Возможность выполнять поиск слева — одно из преимуществ комбинации INDEX и MATCH по сравнению с VLOOKUP.

Чтобы найти класс Эвелин, используйте следующую формулу: выполните поиск Эвелин в B2:B11 и получите соответствующее значение из A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX и MATCH для поиска с учётом регистра

Функция MATCH по умолчанию не различает регистр. Если вам нужно, чтобы формула учитывала разницу между заглавными и строчными буквами, используйте функцию EXACT. Комбинируя MATCH с EXACT в формуле INDEX, вы сможете выполнить поиск с учётом регистра, как показано ниже:

• array — диапазон, из которого требуется вернуть значение.
• lookup_value — значение для поиска с учётом регистра символов в lookup_array.
• lookup_array — диапазон ячеек, с которыми MATCH сравнивает lookup_value.

Например, чтобы узнать экзаменационный балл JIMMY, используйте следующую формулу:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Примечание: Это формула массива, которую нужно вводить с помощью Ctrl + Shift + Enter, кроме Excel365, Excel2021 и более новых версий.

INDEX и MATCH для поиска ближайшего совпадения

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

• array — диапазон, из которого требуется вернуть значение.
• lookup_array — диапазон значений, в котором нужно найти ближайшее к lookup_value.
• lookup_value — значение, для которого ищется ближайшее совпадение.

Например, чтобы узнать, чей балл ближе всего к85, используйте следующую формулу: найдите ближайший к85 балл в C2:C11 и получите соответствующее значение из A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Примечание: Это формула массива, которую нужно вводить с помощью Ctrl + Shift + Enter, кроме Excel365, Excel2021 и более новых версий.

INDEX и MATCH для поиска по нескольким критериям

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

√ Примечание: Это формула массива, которую нужно вводить с помощью Ctrl + Shift + Enter. После этого в строке формул появятся фигурные скобки.

• array — диапазон, из которого требуется вернуть значение.
• (lookup_value=lookup_array) — отдельное условие. Оно проверяет, совпадает ли определённое значение для поиска с элементами в lookup_array.

Например, чтобы узнать балл Коко из класса A, у которой дата рождения07.02.2008, используйте следующую формулу:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX и MATCH для поиска по нескольким столбцам

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

Например, в таблице ниже, как с помощью INDEX и MATCH сопоставить ученика Шона с его классом? Это возможно с помощью формулы, но она довольно объёмная и сложна для понимания и запоминания.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Вот тут и пригодится функция Индекс и сопоставление нескольких столбцов в Kutools для Excel. Она упрощает процесс, позволяя быстро и легко сопоставлять записи с их категориями. Чтобы воспользоваться этим мощным инструментом и быстро найти класс Шона, просто скачайте и установите надстройку Kutools для Excel, а затем выполните следующие действия:

1. Выберите ячейку, в которой хотите отобразить найденный класс.
2. На вкладке Kutools выберите Помощник формул > Поиск и справка > Индекс и сопоставление нескольких столбцов.
3. 
3. В появившемся диалоговом окне выполните следующие действия:
   1. Нажмите первую кнопку рядом с Lookup_col чтобы выбрать столбец с ключевой информацией, которую нужно вернуть, например, названия классов. (Здесь можно выбрать только один столбец.)
   2. Нажмите вторую кнопку рядом с Table_rng чтобы выбрать ячейки для сопоставления значений в выбранном Lookup_col, то есть имена учеников.
   3. Нажмите третью кнопку рядом с Lookup_value чтобы выбрать ячейку с именем ученика, которого нужно сопоставить с классом, в данном случае — Шон.
   4. Нажмите OK.
   5. 

Результат

Kutools автоматически сгенерирует формулу, и вы сразу увидите название класса Шона в выбранной ячейке.

INDEX и MATCH для поиска первого непустого значения

Чтобы получить первое непустое значение, игнорируя ошибки, из столбца или строки, используйте формулу на основе функций INDEX и MATCH. Если же вы не хотите игнорировать ошибки в диапазоне, добавьте функцию ISBLANK.

• Получить первое непустое значение в столбце или строке, игнорируя ошибки:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Получить первое непустое значение в столбце или строке, включая ошибки:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX и MATCH для поиска первого числового значения

Чтобы получить первое числовое значение из столбца или строки, используйте формулу на основе функций INDEX, MATCH и ISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX и MATCH для поиска по максимальным или минимальным значениям

Если нужно получить значение, связанное с максимальным или минимальным значением в диапазоне, используйте функции MAX или MIN совместно с INDEX и MATCH.

• INDEX и MATCH для получения значения, связанного с максимальным значением:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array,0))
• INDEX и MATCH для получения значения, связанного с минимальным значением:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array,0))
• В приведённых формулах два аргумента:
• • array — диапазон, из которого требуется вернуть связанную информацию.
  • lookup_array — набор значений, которые анализируются или ищутся по определённому критерию (максимуму или минимуму).

Например, чтобы определить, у кого самый высокий балл, используйте следующую формулу:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Совет: Настройте собственные сообщения об ошибке #N/A

При работе с функциями INDEX и MATCH в Excel вы можете столкнуться с ошибкой #N/A, если не найдено совпадение. Например, в таблице ниже при попытке найти балл ученицы по имени Саманта появляется ошибка #N/A, так как её нет в наборе данных.

Чтобы сделать ваши таблицы более удобными для пользователей, вы можете настроить сообщение об ошибке, обернув формулу INDEX MATCH в функцию IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

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