Sammanfattning
För att extrahera flera matchningar för att separera celler, i separata kolumner, kan du använda en matrisformel baserad på INDEX och SMALL. I exemplet som visas är formeln i F5:
(=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($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 C5: C11 och "grupper" avser B5: B11. Dessa namn definieras också i skärmbilden ovan.
Kärnan i denna formel är denna: vi använder SMALL-funktionen för att generera 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=$E5,ROW(names)-MIN(ROW(names))+1)
Detta utdrag testar det angivna intervallet "grupper" för värdet i E5. Om den hittas returnerar den ett radnummer från en array med relativa radnummer skapade med:
ROW(names)-MIN(ROW(names))+1
Det slutliga resultatet är en matris som innehåller siffror där det finns en matchning och FALSE där inte:
(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:
COLUMNS($E$5:E5)
När det kopieras över resultattabellen utvidgas intervallet och k (nth) ökar. SMALL-funktionen returnerar varje matchande radnummer, som levereras till INDEX-funktionen som radnummer, med det angivna intervallet "namn" som matris.
Hanteringsfel
När COLUMNS 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 slår vi in formeln i IFERROR-funktionen för att fånga fel och returnera en tom sträng ("").
