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

Supponiamo di avere un elenco di parole chiave e di voler recuperare la prima parola chiave che appare in una cella specifica, mentre la cella contiene diversi altri valori. In questo caso, sarà necessario utilizzare una formula con le funzioni INDICE e CONFRONTA insieme alle funzioni AGGREGATO e RICERCA.

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

Per ottenere la prima parola chiave corrispondente in una cella rispetto all'elenco delle Parole Chiave come mostrato nella tabella sopra, ciò significa che dobbiamo eseguire una corrispondenza parziale piuttosto che una corrispondenza esatta. Per farlo, puoi utilizzare la funzione RICERCA per fornire le posizioni numeriche delle parole chiave presenti nella cella alla funzione AGGREGATO. Quindi, AGGREGATO troverà il numero più piccolo impostando il parametro function_num su 15 e l'argomento ref2 su 1. Successivamente, usa CONFRONTA per individuare il valore più piccolo e fornire il numero di posizione a INDICE per recuperare il valore in quella posizione.

Sintassi generica

=INDICE(intervallo_parole_chiave;CONFRONTA(AGGREGATO(15;6;RICERCA(intervallo_parole_chiave;cella_da_cercare);1);RICERCA(intervallo_parole_chiave;cella_da_cercare);0))

√ Nota: Questa è una formula matrice che richiede di essere inserita premendo Ctrl + Maiusc + Invio.

Per recuperare la prima parola chiave corrispondente apparsa nella cella B5 rispetto alla colonna delle Parole Chiave, copia o inserisci la formula sottostante nella cella C5 e premi Ctrl + Maiusc + Invio per ottenere il risultato:

=INDICE($E$5:$E$7;CONFRONTA(AGGREGATO(15;6;RICERCA($E$5:$E$7;B5);1);RICERCA($E$5:$E$7;B5);0))

√ Nota: I segni del dollaro ($) indicano riferimenti assoluti, il che significa che l'intervallo_parole_chiave nella formula non cambierà quando sposterai o copierai la formula in altre celle. Tuttavia, non sono stati aggiunti segni di dollaro a cella_da_cercare poiché si desidera che sia dinamico. Dopo aver inserito la formula, trascina la maniglia di riempimento verso il basso per applicare la formula alle celle sottostanti.

Spiegazione della formula

=INDICE($E$5:$E$7;CONFRONTA(AGGREGATO(15;6;RICERCA($E$5:$E$7;B5);1);RICERCA($E$5:$E$7;B5);0))

• RICERCA($E$5:$E$7;B5): La funzione RICERCA restituisce la posizione di ciascuna parola chiave dall'intervallo $E$5:$E$7 quando trovata in numeri, e l'errore #VALORE! se non trovata. Il risultato sarà in un array come questo: {15;11;#VALORE!}.
• AGGREGATO(15;6;RICERCA($E$5:$E$7;B5);1) = AGGREGATO(15;6;{15;11;#VALORE!};1): La funzione AGGREGATO con un function_num di 15 e opzione di 6 restituirà il valore più piccolo nell'array secondo l'argomento ref2 impostato su 1, ignorando i valori di errore. Quindi, lo snippet restituirà 11.
• CONFRONTA(AGGREGATO(15;6;RICERCA($E$5:$E$7;B5);1);RICERCA($E$5:$E$7;B5);0) = CONFRONTA(11;{15;11;#VALORE!};0): Il match_type 0 forza la funzione CONFRONTA a eseguire una corrispondenza esatta e restituire la posizione di 11 nell'array {15;11;#VALORE!}. Quindi, la funzione restituirà 2.
• INDICE($E$5:$E$7;CONFRONTA(AGGREGATO(15;6;RICERCA($E$5:$E$7;B5);1);RICERCA($E$5:$E$7;B5);0)) = INDICE($E$5:$E$7;2): La funzione INDICE restituisce quindi il secondo valore nell'intervallo $E$5:$E$7, che è bbb.

Nota

• Se non ci sono parole chiave in una cella, verrà restituito un errore #NUM!.
• La formula non distingue tra maiuscole e minuscole. Per eseguire una corrispondenza sensibile alle maiuscole/minuscole, puoi semplicemente sostituire la funzione RICERCA con TROVA.

Funzioni correlate

Formule correlate