Sommario
Per estrarre più corrispondenze per separare celle, in colonne separate, puoi utilizzare una formula di matrice basata su INDICE e PICCOLO. Nell'esempio mostrato, la formula in F5 è:
(=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($E$5:E5))),""))
Questa è una formula di matrice e deve essere inserita con Ctrl + Maiusc + Invio.
Dopo aver inserito la formula nella prima cella, trascinala verso il basso e attraverso per riempire le altre celle.
Spiegazione
Nota: questa formula utilizza due intervalli denominati: "nomi" si riferisce a C5: C11 e "gruppi" si riferisce a B5: B11. Questi nomi sono definiti anche nella schermata sopra.
L'essenza di questa formula è questa: stiamo usando la funzione SMALL per generare un numero di riga corrispondente a una "n-esima corrispondenza". Una volta ottenuto il numero di riga, lo passiamo semplicemente alla funzione della funzione INDICE, che restituisce il valore in quella riga.
Il trucco è che SMALL sta lavorando con un array costruito dinamicamente da IF in questo bit:
IF(groups=$E5,ROW(names)-MIN(ROW(names))+1)
Questo frammento verifica i "gruppi" di intervalli denominati per il valore in E5. Se trovato, restituisce un numero di riga da un array di numeri di riga relativi creati con:
ROW(names)-MIN(ROW(names))+1
Il risultato finale è un array che contiene numeri dove c'è una corrispondenza e FALSE dove no:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
Questo array va in SMALL. Il valore k per SMALL (nth) deriva da un intervallo in espansione:
COLUMNS($E$5:E5)
Quando viene copiato nella tabella dei risultati, l'intervallo si espande, provocando l'incremento di k (nth). La funzione SMALL restituisce ogni numero di riga corrispondente, fornito alla funzione INDEX come row_num, con l'intervallo denominato "nomi" come matrice.
Gestione degli errori
Quando COLUMNS restituisce un valore per k che non esiste, SMALL genera un errore #NUM. Ciò accade dopo che si sono verificate tutte le corrispondenze. Per eliminare l'errore, inseriamo la formula nella funzione SE.ERRORE per rilevare gli errori e restituire una stringa vuota ("").
