Получение n-го совпадения с помощью ИНДЕКС

Чтобы найти n-е совпадение значения в диапазоне и получить соответствующие ему данные, используйте формулу на основе функций ИНДЕКС, СТРОКА, НАИМЕНЬШИЙ и ЕСЛИ.

Как с помощью функции ИНДЕКС получить информацию, связанную с n-м совпадением значения?

Чтобы получить информацию о третьем совпадении Усэйна Болта в приведённой выше таблице, используйте функции СТРОКА и ЕСЛИ совместно, чтобы создать массив, содержащий номера строк всех значений «Усэйн Болт». Затем примените функцию НАИМЕНЬШИЙ, чтобы найти номер строки нужного вам n-го совпадения. Наконец, передайте этот результат функции ИНДЕКС, чтобы извлечь соответствующую информацию из той же строки.

Общий синтаксис

=INDEX(return_range,SMALL(IF(lookup_array=lookup_value,ROW(lookup_array-ROW(INDEX(lookup_array,1,1))+1),n))

√ Примечание. Это формула массива, которую необходимо вводить с помощью комбинации клавиш.Ctrl+Shift+Enter.

Чтобы получить информацию о третьем совпадении Усэйна Болта, скопируйте или введите приведённые ниже формулы в ячейки G8 и G9 и нажмите Ctrl+Shift+Enter, чтобы получить результаты:

Year (Cell G8)
=ИНДЕКС()B6:B12,НАИМЕНЬШИЙ(ЕСЛИ())C6:C12=G5,СТРОКА()C6:C12-СТРОКА(ИНДЕКС())C6:C12,1,1))+1),G6))
Time (Cell G9)
=ИНДЕКС()D6:D12,НАИМЕНЬШИЙ(ЕСЛИ())C6:C12=G5,СТРОКА()C6:C12-СТРОКА(ИНДЕКС())C6:C12,1,1))+1),G6))

Пояснение формулы

В качестве примера рассмотрим следующую формулу:

=INDEX(B6:B12,SMALL(IF(C6:C12=G5,ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6))

• ROW(C6:C12):Функция СТРОКА возвращает номера строк каждой ячейки в диапазоне C6:C12 в виде массива следующего вида: {6;7;8;9;10;11;12}.
• ROW(INDEX(C6:C12,1,1)):Функции ИНДЕКС и СТРОКА возвращают номер первой строки диапазона поиска C6:C12 на данном листе Excel, который равен 6.
• ЕСЛИ(C6:C12=G5,)ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1)=ЕСЛИ(C6:C12=G5,){6;7;8;9;10;11;12}-6+1):Функция ЕСЛИ проверяет, совпадает ли каждое значение в диапазоне C6:C12 со значением в ячейке G5, Усэйн Болт. Если да — возвращается «соответствующий номер строки – 6 + 1», если нет — значение ЛОЖЬ. В результате получается массив следующего вида: {ЛОЖЬ;2;3;4;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}.
• НАИМЕНЬШИЙ()ЕСЛИ(C6:C12=G5,)ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6)=НАИМЕНЬШИЙ(){ЛОЖЬ;2;3;4;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ},G6):Этот фрагмент возвращает 3-е (значение в ячейке)G6) наименьшее значение из массива, которое равно 4.
• ИНДЕКС(B6:B12,)НАИМЕНЬШИЙ()ЕСЛИ(C6:C12=G5,)ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6)) = ИНДЕКС(B6:B12,4)Функция ИНДЕКС возвращает 4-е значение из диапазона годов B6:B12, которое соответствует Пекину 2008.

Связанные функции

Связанные формулы