Formula generica
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Sommario
Per testare una cella per una delle diverse stringhe e restituire un risultato personalizzato per la prima corrispondenza trovata, è possibile utilizzare una formula INDICE / CONFRONTA basata sulla funzione RICERCA. Nell'esempio mostrato, la formula in C5 è:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
dove le cose (E5: E8) e i risultati (F5: F8) sono denominati intervalli.
Questa è una formula di matrice e deve essere inserita con Ctrl + Maiusc + Invio.
Spiegazione
Questa formula utilizza due intervalli denominati: cose e risultati . Se stai trasferendo direttamente questa formula, assicurati di utilizzare intervalli denominati con gli stessi nomi (definiti in base ai tuoi dati). Se non desideri utilizzare intervalli denominati, utilizza invece riferimenti assoluti.
Il nucleo di questa formula è questo snippet:
ISNUMBER(SEARCH(things,B5)
Questo si basa su un'altra formula (spiegata in dettaglio qui) che controlla una cella per una singola sottostringa. Se la cella contiene la sottostringa, la formula restituisce VERO. In caso contrario, la formula restituisce FALSE.
Poiché stiamo dando alla funzione SEARCH più di una cosa da cercare, nell'intervallo denominato cose , ci darà più l'unico risultato, in un array che assomiglia a questo:
(#VALUE!;9;#VALUE!;#VALUE!)
I numeri rappresentano le corrispondenze nelle cose , gli errori rappresentano gli elementi che non sono stati trovati.
Per semplificare l'array, usiamo la funzione ISNUMBER per convertire tutti gli elementi dell'array in TRUE o FALSE. Qualsiasi numero valido diventa VERO e qualsiasi errore (cioè una cosa non trovata) diventa FALSO. Il risultato è un array come questo:
(FALSE;TRUE;FALSE;FALSE)
che va nella funzione MATCH come argomento lookup_array, con lookup_value TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH restituisce quindi la posizione del primo TRUE trovato, 2 in questo caso.
Infine, si usa la funzione INDEX per recuperare un risultato da l'intervallo denominato risultati in quella stessa posizione:
=INDEX(results,2) // returns "found red"
Puoi personalizzare l' intervallo dei risultati con qualsiasi valore abbia senso nel tuo caso d'uso.
Prevenire le false corrispondenze
Un problema con questo approccio con l'approccio ISNUMBER + SEARCH è che potresti ottenere false corrispondenze da corrispondenze parziali all'interno di parole più lunghe. Ad esempio, se provi ad abbinare "dr" potresti trovare anche "Andrea", "drank", "drip", ecc. Poiché "dr" appare all'interno di queste parole. Ciò accade perché la RICERCA esegue automaticamente una corrispondenza "tipo contiene".
Per una soluzione rapida, puoi racchiudere le parole di ricerca in caratteri spazio (ad esempio "dr" o "dr") per evitare di trovare "dr" in un'altra parola. Ma questo fallirà se "dr" appare per primo o per ultimo in una cella.
Se hai bisogno di una soluzione più robusta, un'opzione è normalizzare il testo prima in una colonna helper e aggiungere uno spazio iniziale e uno finale. Quindi usa la formula in questa pagina sul testo nella colonna helper, invece del testo originale.
