Formuła programu Excel: uzyskaj n-te dopasowanie za pomocą INDEX / MATCH -

Spisie treści

Formuła ogólna

(=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth)))

Podsumowanie

Aby pobrać wiele pasujących wartości ze zbioru danych za pomocą formuły, możesz użyć funkcji JEŻELI i MAŁE, aby obliczyć numer wiersza każdego dopasowania i przesłać tę wartość z powrotem do INDEKSU. W pokazanym przykładzie formuła w I7 to:

(=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6)))

Gdzie nazwane zakresy to amts (D4: D11), id (I3) i id (C4: C11).

Zwróć uwagę, że jest to formuła tablicowa i należy ją wprowadzić za pomocą Control + Shift + Enter.

Wyjaśnienie

Zasadniczo ta formuła jest po prostu formułą INDEKS, która pobiera wartość z tablicy w danej pozycji. Wartość n jest podawana w kolumnie H, a cała „ciężka” praca wykonywana przez formułę polega na ustaleniu wiersza, z którego należy pobrać wartość, gdzie wiersz odpowiada dopasowaniu „n-temu”.

Funkcja JEŻELI sprawdza, które wiersze zawierają dopasowanie, a funkcja MAŁE zwraca n-tą wartość z tej listy. Wewnątrz IF test logiczny to:

ids=id

co daje tę tablicę:

(TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)

Zwróć uwagę, że identyfikator klienta pasuje na pierwszej i czwartej pozycji, które pojawiają się jako PRAWDA. Argument „wartość, jeśli prawda” w funkcji JEŻELI generuje listę względnych numerów wierszy z następującym wyrażeniem:

ROW(ids)-ROW(INDEX(ids,1,1))+1

który tworzy tę tablicę:

(1;2;3;4;5;6;7)

Ta tablica jest następnie „filtrowana” przez wyniki testów logicznych, a funkcja JEŻELI zwraca następujący wynik tablicy:

(1;FALSE;FALSE;4;FALSE;FALSE;FALSE)

Zauważ, że mamy prawidłowe numery wierszy dla wiersza 1 i wiersza 2.

Ta tablica jest następnie przetwarzana przez SMALL, który jest skonfigurowany do używania wartości w kolumnie H do zwracania wartości „nth”. Funkcja SMALL automatycznie ignoruje wartości logiczne TRUE i FALSE w tablicy. Ostatecznie formuły sprowadzają się do:

=INDEX(amts,1) // I6, returns $150 =INDEX(amts,4) // I7, returns $125

Obsługa błędów

Gdy nie będzie już żadnych dopasowań dla danego identyfikatora, funkcja SMALL zwróci błąd #NUM. Możesz obsłużyć ten błąd za pomocą funkcji IFERROR lub dodając logikę do liczenia dopasowań i przerywając przetwarzanie, gdy liczba w kolumnie H jest większa niż liczba dopasowań. Poniższy przykład przedstawia jedno podejście.