Получить n-е совпадение с помощью INDEX

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

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

Чтобы получить информацию о 3-м совпадении Усэйна Болта в таблице, как показано выше, вы можете использовать функции ROW и IF вместе для получения массива, состоящего из номеров строк всех значений Усэйна Болта. Затем используйте функцию SMALL, чтобы получить номер строки указанного n-го совпадения. Наконец, передайте результат в функцию INDEX, чтобы получить соответствующую информацию в той же строке.

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

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

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

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

Год (Ячейка G8)
=INDEX(B6:B12,SMALL(IF(C6:C12=G5,ROW(C6:C12-ROW(INDEX(C6:C12,1,1))+1),G6))
Время (Ячейка G9)
=INDEX(D6:D12,SMALL(IF(C6:C12=G5,ROW(C6:C12-ROW(INDEX(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): Функция ROW возвращает номера строк каждой ячейки в диапазоне C6:C12 в виде массива, например: {6;7;8;9;10;11;12}.
• ROW(INDEX(C6:C12,1,1)): Функции INDEX и ROW возвращают первый номер строки диапазона поиска C6:C12 в этом листе Excel, который равен 6.
• IF(C6:C12=G5,IF(C6:C12=G5,ROW(C6:C12)--ROW(INDEX(C6:C12,1,1))+1)+1) = IF(C6:C12=G5,IF(C6:C12=G5,{6;7;8;9;10;11;12}--6+1)+1): Функция IF проверяет, совпадает ли каждое значение в диапазоне C6:C12 со значением в ячейке G5, Усэйн Болт. Если да, функция вернет результат «соответствующий номер строки - 6 + 1»; в противном случае вернет FALSE. Таким образом, результаты будут в виде массива: {FALSE;2;3;4;FALSE;FALSE;FALSE}.
• SMALL(SMALL(IF(C6:C12=G5,ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6),G6) = SMALL(SMALL({FALSE;2;3;4;FALSE;FALSE;FALSE},G6),G6): Этот фрагмент возвращает 3-е (значение в ячейке G6) наименьшее значение из массива, которое равно 4.
• INDEX(B6:B12,SMALL(IF(C6:C12=G5,ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6)) = INDEX(B6:B12,4) Функция INDEX возвращает 4-е значение в диапазоне лет B6:B12, которое является Пекин 2008.

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

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