Formula Excel: obțineți cea de-a n-a potrivire cu INDEX / MATCH -

Formula generică

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

rezumat

Pentru a extrage mai multe valori de potrivire dintr-un set de date cu o formulă, puteți utiliza funcțiile IF și SMALL pentru a afla numărul rândului fiecărei potriviri și pentru a alimenta această valoare înapoi la INDEX. În exemplul prezentat, formula din I7 este:

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

În cazul în care intervalele denumite sunt amts (D4: D11), id (I3) și id-uri (C4: C11).

Rețineți că aceasta este o formulă matrice și trebuie introdusă cu Control + Shift + Enter.

Explicaţie

La bază, această formulă este pur și simplu o formulă INDEX care recuperează valoarea într-o matrice la o poziție dată. Valoarea pentru n este furnizată în coloana H și toată munca „grea” pe care o face formula este de a afla rândul din care să recupereze o valoare, unde rândul corespunde cu „n-a” potrivire.

Funcția IF face munca de a afla ce rânduri conțin o potrivire, iar funcția SMALL returnează a n-a valoare din lista respectivă. În interiorul IF, testul logic este:

ids=id

care produce această matrice:

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

Rețineți că identitatea clientului se potrivește la pozițiile 1 și 4, care apar ca ADEVĂRAT. Argumentul „valoare dacă este adevărat” din IF generează o listă de numere de rând relative cu această expresie:

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

care produce această matrice:

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

Această matrice este apoi „filtrată” de rezultatele logice ale testului, iar funcția IF returnează următorul rezultat al matricei:

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

Rețineți că avem numere de rând valide pentru rândul 1 și rândul 2.

Această matrice este apoi procesată de SMALL, care este configurat pentru a utiliza valorile din coloana H pentru a returna valorile "nth". Funcția SMALL ignoră automat valorile logice TRUE și FALSE din matrice. În cele din urmă, formulele se reduc la:

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

Gestionarea erorilor

Odată ce nu mai există potriviri pentru un anumit id, funcția SMALL va returna o eroare #NUM. Puteți gestiona această eroare cu funcția IFERROR sau adăugând logică pentru a număra potrivirile și a anula procesarea odată ce numărul din coloana H este mai mare decât numărul de potriviri. Exemplul de aici arată o abordare.