Samenvatting
Als u meerdere overeenkomsten wilt extraheren naar afzonderlijke cellen, in afzonderlijke rijen, kunt u een matrixformule gebruiken op basis van INDEX en SMALL. In het getoonde voorbeeld is de formule in E5:
(=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),""))
Dit is een matrixformule en moet worden ingevoerd met Control + Shift + Enter.
Nadat u de formule in de eerste cel hebt ingevoerd, sleept u deze naar beneden en naar de andere kant om de andere cellen in te vullen.
Uitleg
Opmerking: deze formule gebruikt twee benoemde bereiken: "namen" verwijst naar C4: C11 en "groepen" verwijst naar B4: B11. Deze namen zijn ook gedefinieerd in de bovenstaande schermafbeelding.
De kern van deze formule is dit: we gebruiken de functie KLEIN om een rijnummer te krijgen dat overeenkomt met een "n-de overeenkomst". Zodra we het rijnummer hebben, geven we het eenvoudig door aan de functie INDEX, die de waarde op die rij retourneert.
De truc is dat SMALL werkt met een array die dynamisch is opgebouwd door IF in dit bit:
IF(groups=E$4,ROW(names)-MIN(ROW(names))+1)
Dit fragment test het benoemde bereik "groepen" op de waarde in E4. Indien gevonden, retourneert het een "genormaliseerd" rijnummer uit een array van rijnummers die zijn gemaakt met dit deel van de formule:
ROW(names)-MIN(ROW(names))+1
Het resultaat is een array die rijnummers bevat waar er een overeenkomst is, en FALSE waar niet. De array ziet er ongeveer zo uit:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
Deze array gaat naar SMALL. De k-waarde voor SMALL (n-de) komt uit een groeiend bereik:
ROWS($E$5:E5)
Wanneer het naar beneden in de resultaattabel wordt gekopieerd, wordt het bereik groter, waardoor k (n-de) toeneemt. De functie SMALL retourneert elk overeenkomend rijnummer, dat wordt geleverd aan de functie INDEX als rij_getal, met het benoemde bereik "namen" als de array.
Omgaan met fouten
Als ROWS een waarde voor k retourneert die niet bestaat, genereert SMALL een #GETAL-fout. Dit gebeurt nadat alle overeenkomsten zijn opgetreden. Om de fout te onderdrukken, gebruiken we IFERROR om de fout op te vangen en een lege string ("") te retourneren.
