Функция Excel INDEX MATCH (Содержание)

  • Введение в функцию INDEX MATCH
  • Как использовать функцию INDEX MATCH в Excel?

Введение в функцию INDEX MATCH

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

Синтаксис для INDEX MATCH

Как было сказано ранее, INDEX и MATCH работают как комбинация для поиска значения в заданном диапазоне. Он имеет синтаксис, как показано ниже:

Аргументы:

INDEX () - Формула, которая позволяет вам захватывать значение из данной ячейки через таблицу, связанную с номером столбца или строки.

MATCH () - Формула, которая соответствует значению поиска в данном массиве и предоставляет его положение в качестве аргумента функции INDEX.

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

В этом примере мы увидим, как INDEX MATCH можно использовать в качестве альтернативы VLOOKUP.

Вы можете скачать этот шаблон Excel с функцией INDEX MATCH здесь - Шаблон Excel с функцией INDEX MATCH

Пример № 1 - INDEX MATCH как альтернатива VLOOKUP

Предположим, у нас есть данные, как показано на скриншоте ниже:

Мы собираемся захватить столбец Location со столбцом Name в качестве ссылки (для имени Lalit).

Шаг 1: В ячейке H2 начните вводить = INDEX и дважды щелкните, чтобы выбрать формулу INDEX из списка всех возможных функций, начиная с ключевого слова INDEX.

Шаг 2: Используйте $ C $ 1: $ C $ 5 (столбец Location) в качестве аргумента формулы INDEX (это массив, из которого мы хотим получить искомое значение для соответствия). Знак доллара подчеркивает, что диапазон C1: C5 сделан постоянным для обработки этой формулы.

Шаг 3: Используйте формулу MATCH в качестве второго аргумента в формуле INDEX и используйте значение в H1 в качестве значения для поиска в формуле MATCH.

Шаг 4: Выберите массив поиска из A1: A5, так как это столбец, в котором мы хотели бы проверить, можно ли найти значение поиска. Кроме того, используйте ноль в качестве аргумента точного совпадения внутри функции MATCH, так как ноль ищет точное совпадение значения поиска.

Шаг 5: Закройте скобки, чтобы завершить формулу, и нажмите клавишу Enter, чтобы увидеть результат. Мы можем видеть Location как Pune, который ищется на основе значения поиска Lalit.

Пример № 2 - INDEX MATCH для LOOKUP справа налево

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

Шаг 1: Запустите формулу с = INDEX и используйте A1: A5 в качестве аргумента массива в ячейке H2 текущей рабочей таблицы.

Шаг 2: Используйте формулу MATCH под INDEX в качестве второго аргумента. Внутри MATCH используйте H1 в качестве аргумента для поиска значения.

Шаг 3: D1: D5 будет вашим поисковым массивом. Формула будет искать значение поиска в этом массиве и будет указывать позицию, аналогичную аргументу формулы INDEX. Не забудьте использовать ноль в качестве подходящего аргумента.

Шаг 4: Закройте скобки, чтобы завершить формулу, и нажмите клавишу Enter, чтобы увидеть результат. Вы можете увидеть имя Марты в H2 и убедиться, что она та, кто имеет зарплату 2300 долларов.

Пример № 3 - INDEX MATCH to LOOKUP Значения из строк и столбцов

Предположим, у нас есть данные, показанные ниже, и нам нужно посмотреть объем продаж для Индии в 2018 году. Нам нужно сделать два типа сопоставления, один для страны, а другой для года. Посмотрите, как это происходит с INDEX MATCH.

Шаг 1. Введите формулу = INDEX и выберите все данные в качестве ссылочного массива для функции индекса (A1: D8).

Нам нужно использовать две функции MATCH, одна из которых может соответствовать названию страны, а другая - значению года.

Шаг 2: Используйте MATCH в качестве аргумента в INDEX и установите F2 в качестве значения поиска под ним. Это матч для страны.

Шаг 3: Используйте A1: A8 в качестве поискового массива, так как именно здесь мы можем найти указанное название страны. Не забудьте использовать ноль в качестве критерия соответствия, который указывает точное соответствие.

Шаг 4: Теперь снова используйте функцию MATCH, которая позволяет системе проверить год 2018 и присвоить позиции значения продаж, связанной с Индией, формулу INDEX. Установите значение поиска как G2 внутри формулы MATCH.

Шаг 5: Здесь мы можем видеть только ячейку A1: D1 - это те, где мы можем найти значение поиска 2018. Таким образом, используйте то же самое, что и массив поиска для формулы MATCH.

Шаг 6: Используйте ноль в качестве критерия соответствия, который указывает точное соответствие для значения года в пределах диапазона поиска. Закройте скобки и нажмите клавишу Enter, чтобы увидеть результат.

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

Вывод

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

То, что нужно запомнить

  • INDEX MATCH - это не функция в Excel, а комбинация двух формул INDEX и MATCH
  • INDEX MATCH можно использовать как альтернативу старой школе VLOOKUP. VLOOKUP может видеть только через вертикальные ячейки. Принимая во внимание, что INDEX MATCH может искать значения на основе строк, столбцов, а также их комбинации (см. Пример 3 для справки).

Рекомендуемые статьи

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

  1. Создание модели данных в Excel
  2. Как использовать контрольный список в Excel?
  3. Примеры функции соответствия Excel
  4. VLOOKUP Примеры в Excel
  5. Примеры макросов Excel

Категория:

Советы по Excel