Excel-formule: verkrijg de zoveelste overeenkomst met INDEX / MATCH -

Generieke formule

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

Samenvatting

Om meerdere overeenkomende waarden uit een set gegevens met een formule op te halen, kunt u de functies ALS en KLEIN gebruiken om het rijnummer van elke overeenkomst te achterhalen en die waarde terug te sturen naar INDEX. In het getoonde voorbeeld is de formule in I7:

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

Waarbij benoemde bereiken amts (D4: D11), id (I3) en ids (C4: C11) zijn.

Merk op dat dit een matrixformule is en moet worden ingevoerd met Control + Shift + Enter.

Uitleg

In de kern is deze formule gewoon een INDEX-formule die de waarde in een array op een bepaalde positie ophaalt. De waarde voor n wordt opgegeven in kolom H, en al het "zware" werk dat de formule doet, is uitzoeken uit welke rij een waarde moet worden opgehaald, waarbij rij overeenkomt met "n-de" overeenkomst.

De ALS-functie doet het werk om uit te zoeken welke rijen een overeenkomst bevatten, en de SMALL-functie retourneert de n-de waarde uit die lijst. Binnenin IF is de logische test:

ids=id

wat deze array oplevert:

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

Let op de overeenkomsten van de klant-ID op de 1e en 4e positie, die als WAAR worden weergegeven. Het argument "waarde indien waar" in IF genereert een lijst met relatieve rijnummers met deze uitdrukking:

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

die deze array produceert:

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

Deze array wordt vervolgens "gefilterd" door de logische testresultaten, en de IF-functie retourneert het volgende array-resultaat:

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

Merk op dat we geldige rijnummers hebben voor rij 1 en rij 2.

Deze array wordt vervolgens verwerkt door SMALL, dat is geconfigureerd om waarden in kolom H te gebruiken om "n-de" waarden te retourneren. De functie KLEIN negeert automatisch de logische waarden TRUE en FALSE in de array. Uiteindelijk verminderen de formules tot:

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

Omgaan met fouten

Zodra er geen overeenkomsten meer zijn voor een bepaalde id, retourneert de functie SMALL een #GETAL-fout. U kunt deze fout oplossen met de IFERROR-functie, of door logica toe te voegen om overeenkomsten te tellen en de verwerking af te breken zodra het aantal in kolom H groter is dan het aantal overeenkomsten. Het voorbeeld hier toont één benadering.