CERCA.X (XLOOKUP in inglese) è la funzione che Microsoft ha introdotto per sostituire CERCA.VERT e CERCA.ORIZZ. È disponibile in Excel per Microsoft 365 e nelle versioni più recenti, in Excel per il web, in Google Sheets e nelle versioni recenti di LibreOffice Calc. Se il foglio deve essere aperto anche con versioni vecchie, il problema va valutato prima; altrimenti conviene adottarla come scelta predefinita.
L'idea di fondo
CERCA.VERT ragiona su una tabella e un numero di colonna. CERCA.X ragiona su due intervalli separati: dove cercare e cosa restituire. Questa differenza, apparentemente piccola, elimina gran parte dei problemi.
=CERCA.X(valore; matrice_ricerca; matrice_restituzione)
Con un listino che ha i codici in A e i prezzi in D:
=CERCA.X(A2; Listino!$A$2:$A$500; Listino!$D$2:$D$500)
Non c'è nessun indice da contare. Se qualcuno inserisce una colonna nel listino, gli intervalli si spostano da soli e la formula continua a restituire il prezzo.
I sei argomenti
La sintassi completa è più lunga, ma gli argomenti oltre il terzo sono facoltativi:
| Argomento | Significato | Valore predefinito |
|---|---|---|
| valore | ciò che cerchi | obbligatorio |
| matrice_ricerca | dove cercarlo | obbligatorio |
| matrice_restituzione | cosa restituire | obbligatorio |
| se_non_trovato | testo o valore se manca | errore #N/D |
| modalità_confronto | esatta, inferiore, superiore, caratteri jolly | esatta (0) |
| modalità_ricerca | dall'inizio, dalla fine, binaria | dall'inizio (1) |
La corrispondenza esatta come comportamento predefinito è già un grande passo avanti: l'errore classico di CERCA.VERT, cioè dimenticare FALSO e ottenere dati sbagliati, qui non può verificarsi.
Esempi pratici
Cercare verso sinistra
Hai un elenco di dipendenti con la matricola in A e il cognome in C. Vuoi trovare la matricola partendo dal cognome. Con CERCA.VERT sarebbe impossibile senza riordinare le colonne; con CERCA.X basta invertire gli intervalli:
=CERCA.X("Rossi"; $C$2:$C$200; $A$2:$A$200)
Un messaggio chiaro quando il valore manca
Il quarto argomento sostituisce SE.ERRORE e, a differenza di questo, intercetta soltanto il caso «non trovato»:
=CERCA.X(A2; Codici; Prezzi; "Codice assente")
Qui Codici e Prezzi sono intervalli con nome, che rendono la formula molto più leggibile.
L'ultima occorrenza
In un registro di movimenti lo stesso cliente compare molte volte e vuoi la data dell'ultimo ordine. Impostando la modalità di ricerca a -1, la funzione parte dal fondo:
=CERCA.X(F2; $B$2:$B$5000; $A$2:$A$5000; "Nessun ordine"; 0; -1)
Se il registro è in ordine cronologico, ottieni l'ordine più recente senza formule complicate.
Fasce e scaglioni senza ordinare
Per assegnare una fascia di sconto in base alla quantità si usa la modalità di confronto -1, cioè «corrispondenza esatta oppure il valore inferiore più vicino»:
=CERCA.X(B2; $F$2:$F$5; $G$2:$G$5; ; -1)
A differenza della corrispondenza approssimata di CERCA.VERT, la tabella delle soglie non deve essere ordinata. Resta comunque buona pratica ordinarla, perché chi legge il foglio la capisce meglio.
Restituire più colonne in una volta
Se la matrice di restituzione comprende più colonne, CERCA.X restituisce l'intera riga. Per esempio:
=CERCA.X(A2; Listino!$A$2:$A$500; Listino!$B$2:$D$500)
restituisce descrizione, categoria e prezzo in tre celle adiacenti. Nelle versioni con matrici dinamiche il risultato si «espande» automaticamente a destra. Se le celle di destinazione non sono vuote compare l'errore #ESPANSIONE! (#SPILL! in inglese): basta liberarle.
Ricerca bidimensionale
Annidando due CERCA.X puoi trovare un valore all'incrocio tra una riga e una colonna, per esempio le vendite di un prodotto in un certo mese:
=CERCA.X(H2; $A$2:$A$50; CERCA.X(H3; $B$1:$M$1; $B$2:$M$50))
La funzione interna restituisce l'intera colonna del mese indicato in H3; quella esterna cerca il prodotto di H2 e prende il valore corrispondente in quella colonna.
Attenzioni pratiche
- Gli intervalli devono avere la stessa altezza. Se la matrice di ricerca va da 2 a 500 e quella di restituzione da 2 a 499, ottieni un errore #VALORE!.
- Compatibilità. Un file con CERCA.X aperto in una versione di Excel che non la supporta mostra errori al posto dei risultati. Se il foglio circola in un'azienda con installazioni miste, verificalo prima.
- Caratteri jolly. Con la modalità di confronto 2 puoi usare
*e?nel valore cercato, per esempio"Ross*". Senza quella modalità l'asterisco viene cercato come carattere normale. - Spazi e testo contro numero. Come per qualsiasi ricerca, valori che sembrano uguali ma non lo sono restano il primo sospetto quando compare #N/D.
In sintesi
- CERCA.X usa due intervalli separati, quindi niente indici di colonna da contare.
- La corrispondenza esatta è il comportamento predefinito.
- Il quarto argomento gestisce il valore mancante senza nascondere altri errori.
- Con la modalità di ricerca -1 trovi l'ultima occorrenza di un valore.
- Prima di adottarla, verifica che tutti i destinatari del file abbiano una versione compatibile.
