Formula generica
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Sommario
Per controllare una cella per una delle diverse cose e restituire la prima corrispondenza trovata nell'elenco, puoi utilizzare una formula INDICE / CONFRONTA che utilizza RICERCA o TROVA per individuare una corrispondenza. Nell'esempio mostrato, la formula in C5 è:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
dove "cose" è l'intervallo denominato E5: E9.
Nota: questa è una formula di matrice e deve essere inserita con Control + Maiusc + Invio.
Spiegazione
In questo esempio, abbiamo un elenco di colori in un intervallo denominato "cose" (E5: E9). Vogliamo controllare il testo nella colonna B per vedere se contiene uno di questi colori. Se è così, vogliamo restituire il nome del primo colore trovato.
Lavorando dall'interno verso l'esterno, questa formula utilizza la funzione ISNUMBER e la funzione SEARCH per cercare nel testo in B5 ogni colore elencato in "cose" come questa:
ISNUMBER(SEARCH(things,B5)
Questa espressione si basa su una formula (spiegata in dettaglio qui) che controlla una cella per una singola sottostringa. Se la cella contiene la sottostringa, l'espressione restituisce VERO. In caso contrario, l'espressione restituisce FALSE.
Quando forniamo questa RICERCA, un elenco di cose (invece di una cosa) ci restituirà una serie di risultati. Ogni colore trovato genererà una posizione numerica e i colori non trovati genereranno un errore:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
La funzione ISNUMBER converte quindi i risultati in valori TRUE / FALSE. Qualsiasi numero diventa VERO e qualsiasi errore (non trovato) diventa FALSO. Il risultato è un array come questo:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Questo array viene restituito alla funzione MATCH come argomento dell'array. Il valore di ricerca è TRUE e il tipo di corrispondenza è impostato su zero per forzare una corrispondenza esatta. Quando è presente un colore corrispondente, MATCH restituisce la prima posizione TRUE trovata. Questo valore viene inserito nella funzione INDICE come numero di riga, con l'intervallo denominato "cose" fornito come matrice. Quando c'è almeno una corrispondenza, INDICE restituisce il colore in quella posizione. Quando non viene trovata alcuna corrispondenza, questa formula restituisce l'errore # N / D.
Con valori hardcoded
Se non si desidera impostare un intervallo denominato esterno come "cose" in questo esempio, è possibile codificare i valori nella formula come "costanti di matrice" in questo modo:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Ottieni la prima corrispondenza nella cella
La lingua qui è abbastanza confusa, ma la formula sopra restituirà la prima corrispondenza trovata nell'elenco delle cose da cercare. Se invece vuoi restituire la prima corrispondenza trovata nella cella in prova, puoi provare una formula come questa:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
In questa versione della formula, la funzione CONFRONTA è impostata per cercare il risultato di questo frammento:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
che utilizza la funzione AGGREGATE per ottenere il valore minimo nei risultati restituiti da SEARCH. Abbiamo bisogno di AGGREGARE qui, perché l'array in arrivo probabilmente conterrà errori (restituiti da SEARCH quando le cose non vengono trovate), e abbiamo bisogno di una funzione che ignori quegli errori e ci fornisca comunque il valore numerico minimo.
Il risultato di AGGREGATE viene restituito direttamente a MATCH come valore di ricerca, insieme allo stesso array restituito da SEARCH. Il risultato finale è la prima corrispondenza trovata nella cella, non la prima corrispondenza trovata nell'elenco delle cose.
