Samenvatting
Om meerdere overeenkomsten te extraheren naar afzonderlijke cellen, in afzonderlijke kolommen, kunt u een matrixformule gebruiken op basis van INDEX en SMALL. In het getoonde voorbeeld is de formule in F5:
(=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($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 C5: C11 en "groepen" verwijst naar B5: B11. Deze namen zijn ook gedefinieerd in de bovenstaande schermafbeelding.
De kern van deze formule is dit: we gebruiken de functie SMALL om een rijnummer te genereren 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=$E5,ROW(names)-MIN(ROW(names))+1)
Dit fragment test het benoemde bereik "groepen" op de waarde in E5. Indien gevonden, retourneert het een rijnummer uit een array van relatieve rijnummers gemaakt met:
ROW(names)-MIN(ROW(names))+1
Het uiteindelijke resultaat is een array die getallen bevat waar er een overeenkomst is, en FALSE waar niet:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
Deze array gaat naar SMALL. De k-waarde voor SMALL (n-de) komt uit een groeiend bereik:
COLUMNS($E$5:E5)
Wanneer het over 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
Wanneer COLUMNS 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, verpakken we de formule in de IFERROR-functie om fouten op te vangen en een lege string ("") te retourneren.
