Recuperare il primo valore corrispondente in una cella rispetto a un elenco

Supponendo di avere un elenco di parole chiave, per recuperare la prima parola chiave che appare in una cella specifica — anche quando la cella contiene diversi altri valori — è necessario utilizzare una formula INDEX e MATCH, con l'aiuto delle funzioni AGGREGATE e SEARCH.

Come recuperare il primo valore corrispondente da un elenco in una cella?

Per ottenere la prima parola chiave corrispondente in una cella rispetto all'elenco Keywords, come mostrato nella tabella sopra, è necessario eseguire una corrispondenza per contenuto anziché una corrispondenza esatta. A tal fine, puoi utilizzare la funzione SEARCH per passare alla funzione AGGREGATE le posizioni numeriche delle parole chiave trovate nella cella. AGGREGATE restituirà quindi il numero più piccolo impostando function_num su 15 e l'argomento ref2 su 1. Successivamente, usa MATCH per individuare la prima occorrenza del valore più piccolo e passa tale posizione a INDEX per recuperare il valore corrispondente.

Sintassi generica

=INDEX()keyword_rng,MATCH(AGGREGATE(15,6,SEARCH()))keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))

√ Nota: questa è una formula matriciale che richiede di essere inserita con Ctrl+Maiusc+Invio.

Per recuperare la prima parola chiave corrispondente presente nella cella B5 rispetto alla colonna Keywords, copiare o inserire la formula seguente nella cella C5 e premere Ctrl+Maiusc+Invioper ottenere il risultato:

=INDEX()$E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH()))$E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

√ Nota: i simboli del dollaro ($) indicano riferimenti assoluti, il che significa che keyword_rngnella formula non cambierà quando si sposta o si copia la formula in altre celle. Tuttavia, non sono stati aggiunti simboli del dollaro a lookup_cellpoiché si desidera che sia dinamico. Dopo aver inserito la formula, trascinare il quadratino di riempimento verso il basso per applicarla alle celle sottostanti.

Spiegazione della formula

=INDEX($E$5:$E$7,)MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

• SEARCH($E$5:$E$7,B5):La funzione SEARCH restituisce la posizione di ciascuna parola chiave presente nell'intervallo $E$5:$E$7 quando viene trovata, sotto forma di numeri; se non viene trovata, restituisce l'errore #VALUE!. Il risultato sarà un array come questo: {15;11;#VALUE!}.
• AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1)=AGGREGATE(15,6,){15;11;#VALUE!},1):La funzione AGGREGATE con function_num pari a 15 e option pari a 6 restituisce il valore più piccolo nell'array in base all'argomento ref2 1, ignorando i valori di errore. Pertanto, il frammento restituirà 11.
• MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)=MATCH()11,{15;11;#VALUE!},0):La match_type 0 obbliga la funzione MATCH a eseguire una corrispondenza esatta e a restituire la posizione di 11 nell'array {15;11;#VALUE!}. La funzione restituirà quindi 2.
• INDEX($E$5:$E$7,)MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)) = INDEX($E$5:$E$7,2):La funzione INDEX restituisce quindi il 2° valore nell'intervallo $E$5:$E$7, ovvero bbb.

Nota

• Se una cella non contiene alcuna parola chiave, verrà restituito l'errore #NUM!.
• La formula non distingue tra maiuscole e minuscole. Per eseguire una corrispondenza con distinzione tra maiuscole e minuscole, basta sostituire la funzione SEARCH con FIND.

Funzioni correlate

Formule correlate