Поиск энный матч в таблице с помощью INDEX & функции MATCH

Сценарий:

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

Как решить проблему?

Чтобы формула сначала поняла, нам нужно немного пересмотреть следующие функции. link: / lookup-formulas-excel-index-function [INDEX function]

link: / lookup-formulas-excel-match-function [MATCH function]

Теперь мы составим формулу, используя указанные выше функции. Функция ПОИСКПОЗ вернет индекс самого низкого совпадения из диапазона. Функция ИНДЕКС принимает индекс строки в качестве аргумента и возвращает соответствующие требуемые результаты. Эта функция находит

Общая формула:

= INDEX ( data , MATCH ( SMALL (range, n), range, match_type ) , col_num )

data: массив значений в таблице без заголовков range: lookup_array для наименьшего соответствия n: число, n-е соответствие match_type: 1 (точное или следующее наименьшее) или 0 (точное соответствие) или -1 (точное или следующее наибольшее соответствие)

col_num: номер столбца, необходимое значение для извлечения из столбца таблицы.

Пример:

Приведенные выше утверждения могут сбивать с толку. Итак, давайте разберемся с этим на примере формулы. Здесь у нас есть таблица с подробностями о мировом континенте. Нам нужно найти код страны из данной таблицы из поискового значения в качестве названия страны.

Именованный диапазон, используемый для таблицы (E5: I10) и массива диапазона (I5: I10).

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

Используйте приведенную выше формулу, чтобы получить первую деталь, которая является заглавной. Используйте формулу в ячейке G6:

= INDEX ( table ,MATCH ( SMALL (range, 1) ,range , 0 ) , 4 )

Именованные диапазоны

стол (E5: I10)

диапазон (I5: I10)

Пояснение:

Функция SMALL находит первое наименьшее совпадение в диапазоне и возвращает значение функции MATCH.
Функция ПОИСКПОЗ соответствует точному совпадению минимального значения в диапазоне цен и возвращает индекс своей строки в функцию ИНДЕКС.
Функция ИНДЕКС находит значение Capital с индексом ROW и 4-м столбцом в таблице с именем range.

Формула возвращает все результаты для первого наименьшего значения. Теперь скопируйте формулу, чтобы получить второй наименьший или третий наименьший, просто нужно изменить аргумент n в функции МАЛЕНЬКИЙ, чтобы получить другие результаты.

Теперь просто измените значение n и получите результаты, как показано ниже.

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

Пример:

Вы также можете использовать link: / logic-formulas-excel-large-function [LARGE]

функция для получения n-го совпадения из верхнего или n-го совпадения из диапазона и возвращает соответствующие результаты.

Общая формула:

= INDEX ( data , MATCH ( LARGE (range, n), range, match_type ) , col_num )

data: массив значений в таблице без заголовков range: lookup_array для наивысшего соответствия n: число, n-е соответствие match_type: 1 (точное или следующее наименьшее) или 0 (точное соответствие) или -1 (точное или следующее наибольшее соответствие)

col_num: номер столбца, необходимое значение для извлечения из столбца таблицы.

Пример:

Приведенные выше утверждения могут сбивать с толку. Итак, давайте разберемся с этим на примере формулы. Здесь у нас есть таблица с подробностями о мировом континенте. Нам нужно найти код страны из данной таблицы из поискового значения в качестве названия страны.

Именованный диапазон, используемый для таблицы (E5: I10) и массива диапазона (I5: I10).

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

Используйте приведенную выше формулу, чтобы получить первую деталь, которая является заглавной. Используйте формулу в ячейке G6:

= INDEX ( table ,MATCH ( LARGE (range, 1) ,range , 0 ) , 4 )

Именованные диапазоны

стол (E5: I10)

диапазон (I5: I10)

Пояснение:

Функция НАИБОЛЬШИЙ находит первое совпадение в диапазоне и возвращает значение функции ПОИСКПОЗ.
Функция ПОИСКПОЗ соответствует точному совпадению n-го значения совпадения в диапазоне цен и возвращает индекс своей строки в функцию ИНДЕКС.
Функция ИНДЕКС находит значение Capital с индексом ROW и 4-м столбцом в таблице с именем range.

Формула возвращает все результаты для первого наименьшего значения. Теперь скопируйте формулу, чтобы получить второй наименьший или третий наименьший, просто нужно изменить аргумент n в функции МАЛЕНЬКИЙ, чтобы получить другие результаты.

Теперь просто измените значение n и получите результаты, как показано ниже.

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

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

Заметки: . Значение n не может быть меньше 1 или больше длины диапазона. Функция возвращает ошибку #NA, если аргумент массива поиска функции ПОИСКПОЗ не имеет той же длины, что и массив таблицы.

Формула возвращает ошибку, если lookup_value не соответствует значению в таблице lookup_array.
Функция соответствует точному значению, поскольку аргумент типа соответствия функции ПОИСКПОЗ равен 0.
Значения поиска могут быть заданы как ссылка на ячейку или напрямую с использованием символа кавычки («) в формуле в качестве аргументов.

Надеюсь, вы поняли, как искать n-е совпадение в таблице с помощью функции ИНДЕКС и ПОИСКПОЗ. Прочтите больше статей о поиске в Excel значение здесь. Пожалуйста, не стесняйтесь формулировать свои запросы ниже в поле для комментариев. Мы обязательно поможем вам.

Если вам понравились наши блоги, поделитесь ими со своими друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам по адресу [email protected]

По теме Статьи

link: / lookup-formulas-use-index-and-match-to-lookup-value [Используйте ИНДЕКС и ПОИСКПОЗ для поиска значения]: функция ИНДЕКС и ПОИСКПОЗ для поиска необходимого значения. |||| link: / summing-sum-range-with-index-in-excel [SUM range with INDEX in Excel]: Используйте функцию INDEX, чтобы найти СУММУ значений как req uired.

link: / lookup-formulas-excel-index-function [Как использовать функцию ИНДЕКС в Excel]: Найдите ИНДЕКС массива с помощью функции ИНДЕКС, объясненной в примере.

link: / lookup-formulas-excel-match-function [Как использовать функцию ПОИСКПОЗ в Excel]: Найдите ПОИСКПОЗ в массиве, используя значение ИНДЕКС внутри функции ПОИСКПОЗ, объясненное в примере.

link: / lookup-formulas-excel-lookup-function [Как использовать функцию ПРОСМОТР в Excel]: Найдите значение поиска в массиве с помощью функции ПРОСМОТР, объясненной в примере.

link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию ВПР в Excel]: Найдите значение поиска в массиве с помощью функции ВПР, объясненной в примере.

Сценарий:

Как решить проблему?

Общая формула:

Пример:

Именованные диапазоны

Пояснение:

Пример:

Общая формула:

Пример:

Именованные диапазоны

Пояснение:

По теме Статьи

Популярные статьи