Sammanfattning
För att extrahera flera matchningar för att separera celler, i separata rader, kan du använda en matrisformel baserad på INDEX och SMALL. I exemplet som visas är formeln i E5:
(=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),""))
Detta är en matrisformel och måste anges med Control + Shift + Enter.
När du har angett formeln i den första cellen drar du den nedåt och över för att fylla i de andra cellerna.
Förklaring
Obs: denna formel använder två namngivna intervall: "namn" hänvisar till C4: C11 och "grupper" avser B4: B11. Dessa namn definieras också i skärmbilden ovan.
Kärnan i denna formel är denna: vi använder funktionen SMALL för att få ett radnummer som motsvarar en "n: e matchning". När vi väl har radnumret skickar vi det helt enkelt till funktionen INDEX, som returnerar värdet på den raden.
Tricket är att SMALL arbetar med en matris som är dynamiskt konstruerad av IF i denna bit:
IF(groups=E$4,ROW(names)-MIN(ROW(names))+1)
Detta utdrag testar det angivna intervallet "grupper" för värdet i E4. Om den hittas returnerar den ett "normaliserat" radnummer från en rad radnummer som skapats med den här delen av formeln:
ROW(names)-MIN(ROW(names))+1
Resultatet är en matris som innehåller radnummer där det finns en matchning och FALSE där inte. Arrayen ser ungefär så här ut:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
Denna matris går in i SMALL. K-värdet för SMALL (nth) kommer från ett expanderande intervall:
ROWS($E$5:E5)
När du kopierar ner resultattabellen expanderar intervallet, vilket får k (nth) att öka. SMALL-funktionen returnerar varje matchande radnummer, som levereras till INDEX-funktionen som radnummer, med det angivna intervallet "namn" som matris.
Hanteringsfel
När ROWS returnerar ett värde för k som inte finns, kastar SMALL ett #NUM-fel. Detta händer när alla matcher har inträffat. För att undertrycka felet använder vi IFERROR för att fånga upp felet och returnera en tom sträng ("").
