Общая формула
=SMALL(IF(logical,ROW(list)-MIN(ROW(list))+1),n)
Резюме
Чтобы получить позицию n-го совпадения (например, 2-го совпадающего значения, 3-го совпадающего значения и т. Д.), Вы можете использовать формулу, основанную на функции МАЛЕНЬКИЙ. В показанном примере формула в G5:
=SMALL(IF(list=E5,ROW(list)-MIN(ROW(list))+1),F5)
Эта формула возвращает позицию второго вхождения «красного» в списке.
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.
Объяснение
В этой формуле используется именованный диапазон «список», который представляет собой диапазон B5: B11.
Ядром этой формулы является функция МАЛЕНЬКИЙ, которая просто возвращает n-е наименьшее значение в списке значений, соответствующих номерам строк. Номера строк были «отфильтрованы» оператором IF, который применяет логику для сопоставления. Работая изнутри, IF сравнивает все значения в названном диапазоне "list" со значением в B5, что создает такой массив:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
«Значение, если истинно» - это набор относительных номеров строк, созданных этим кодом:
ROW(list)-MIN(ROW(list))+1
Результатом является такой массив:
(1;2;3;4;5;6;7)
См. Эту страницу для полного объяснения.
При логическом тесте, который возвращает массив результатов, функция ЕСЛИ действует как фильтр - сохраняются только номера строк, соответствующие совпадению, остальные возвращают ЛОЖЬ. Результат, возвращаемый IF, выглядит так:
(1;FALSE;FALSE;FALSE;5;FALSE;7)
Цифры 1, 5 и 7 соответствуют положению «красного» в списке.
Наконец, SMALL возвращает n-й наименьший элемент в этом списке, игнорируя значения FALSE. В этом примере F5 содержит 2, поэтому SMALL возвращает второе наименьшее значение: 5.
Получить связанное значение
Как только у вас есть относительное положение n-го совпадения, вы можете использовать эту позицию с функцией ИНДЕКС для возврата связанного значения.
