Formuła ogólna
=SMALL(IF(logical,ROW(list)-MIN(ROW(list))+1),n)
Podsumowanie
Aby uzyskać pozycję n-tego dopasowania (na przykład 2. pasującej wartości, 3. pasującej wartości itp.), Możesz użyć formuły opartej na funkcji MAŁE. W pokazanym przykładzie formuła w G5 to:
=SMALL(IF(list=E5,ROW(list)-MIN(ROW(list))+1),F5)
Ta formuła zwraca pozycję drugiego wystąpienia „czerwonego” na liście.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
Wyjaśnienie
Ta formuła używa nazwanego zakresu „listy”, czyli zakresu B5: B11.
Rdzeniem tej formuły jest funkcja SMALL, która po prostu zwraca n-tą najmniejszą wartość z listy wartości odpowiadających numerom wierszy. Numery wierszy zostały „odfiltrowane” przez instrukcję IF, która stosuje logikę dopasowania. Działając od wewnątrz, funkcja JEŻELI porównuje wszystkie wartości z „listy” nazwanego zakresu z wartością w B5, co tworzy następującą tablicę:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
„Wartość, jeśli prawda” to zestaw względnych numerów wierszy utworzonych przez ten kod:
ROW(list)-MIN(ROW(list))+1
Rezultatem jest tablica taka:
(1;2;3;4;5;6;7)
Zobacz tę stronę, aby uzyskać pełne wyjaśnienie.
W przypadku testu logicznego, który zwraca tablicę wyników, funkcja JEŻELI działa jak filtr - przetrwają tylko numery wierszy, które odpowiadają dopasowaniu, reszta zwraca FALSE. Wynik zwracany przez JEŻELI wygląda następująco:
(1;FALSE;FALSE;FALSE;5;FALSE;7)
Cyfry 1, 5 i 7 odpowiadają miejscu „czerwonego” na liście.
Na koniec funkcja SMALL zwraca n-tą najmniejszą pozycję na tej liście, ignorując wartości FAŁSZ. W tym przykładzie F5 zawiera 2, więc SMALL zwraca drugą najmniejszą wartość: 5.
Uzyskaj powiązaną wartość
Po uzyskaniu względnej pozycji n-tego dopasowania można użyć tej pozycji z funkcją INDEKS, aby zwrócić skojarzoną wartość.
