Formula generica
=XLOOKUP("*"&value&"*",lookup,results,,2)
Sommario
Per utilizzare XLOOKUP per abbinare valori che contengono testo specifico, è possibile utilizzare caratteri jolly e concatenazione. Nell'esempio mostrato, la formula in F5 è:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
dove codice (B5: B15) e quantità (C5: C15) sono intervalli denominati.
Spiegazione
La funzione XLOOKUP contiene il supporto integrato per i caratteri jolly, ma questa funzione deve essere abilitata esplicitamente impostando la modalità di corrispondenza sul numero 2.
Nell'esempio mostrato, XLOOKUP è configurato per corrispondere al valore immesso nella cella E5, che può apparire ovunque nei valori di ricerca in B5: B15. La formula in F5 è:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, con asterischi (*) concatenati davanti e dietro
- lookup_array - il codice dell'intervallo denominato (B5: B15)
- array_ritorno - la quantità dell'intervallo denominato (C5: C15)
- if_not_found - la stringa "nessuna corrispondenza"
- match_mode - fornito come 2 (corrispondenza con caratteri jolly)
- search_mode - non fornito. Il valore predefinito è 1 (dal primo all'ultimo)
Per rendere automatica una corrispondenza di tipo "contiene", il carattere jolly asterisco (*) viene anteposto e aggiunto al valore nella cella E5 con concatenazione:
"*"&E5&"*"
Dopo la concatenazione, la formula diventa:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
XLOOKUP individua la prima corrispondenza che contiene "BCC" (050-BCC-123 nella riga 10) e restituisce il valore corrispondente dalla matrice restituita, 50.
Nota che XLOOKUP non fa distinzione tra maiuscole e minuscole, l'immissione di "bcc" in E5 restituirà lo stesso risultato:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
Vedi sotto per un'opzione per configurare XLOOKUP per una corrispondenza sensibile al maiuscolo / minuscolo.
Opzione CERCA.VERT
La formula CERCA.VERT supporta anche i caratteri jolly se impostata sulla corrispondenza esatta. La formula CERCA.VERT equivalente per questo esempio è:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Spiegazione completa qui.
Con CERCA e TROVA
È anche possibile utilizzare le funzioni CERCA e TROVA per eseguire una corrispondenza di tipo "contiene" con XLOOKUP. Per una corrispondenza senza distinzione tra maiuscole e minuscole (come l'esempio sopra), puoi utilizzare RICERCA in questo modo:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
Per una corrispondenza con distinzione tra maiuscole e minuscole, puoi utilizzare TROVA:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Entrambe le opzioni precedenti rendono più semplice estendere i criteri per includere altre condizioni utilizzando la logica booleana.
La logica per ISNUMBER + SEARCH è spiegata qui.
Partite multiple
Se hai bisogno di più corrispondenze, vedi la funzione FILTRO.
