Excel: CERCA.VERT, CERCA.ORIZZ, INDICE, CONFRONTA, CERCA.X

Funzione CERCA.VERT e similari in Excel

Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 01 Gennaio 2023
Categoria: Microsoft Office Excel: CERCA.VERT, CERCA.ORIZZ, INDICE, CONFRONTA, CERCA.X

La funzione CERCA.VERT di Microsoft Office è probabilmente una delle più utilizzate di Excel; essa consente di ricercare un dato nella prima colonna di un intervallo di celle e di restituire un valore a destra da qualsiasi cella sulla stessa riga dell'intervallo.
Insieme alla funzione CERCA.ORIZZ e a molte altre, fa parte delle funzioni di ricerca e riferimento di Excel.
CERCA.VERT ha alcuni limiti: non cerca a sinistra (risolvibile con CERCA.X o nidificando CONFRONTA in Indice), ha problemi se la colonna che contiene Valore ha doppioni (risolvibile ad esempio concatenando Valore con un'altra colonna) e infine, come per altre funzioni di Excel, non ama gli spazi in eccesso (risolvibile con ANNULLA.SPAZI). Vediamo dunque l'uso delle funzioni Excel: CERCA.VERT, CERCA.ORIZZ, INDICE, CONFRONTA, CERCA.X, quando usare FALSO, VERO e come cercare a sinistra con Excel.

Sommario Funzione CERCA.VERT

• Funzione CERCA.VERT
  • Esempi
• CERCA.VERT classico
• Ricercare in fogli diversi con CERCA.VERT
• CERCA.VERT con VERO
• Ricercare in base a 2 criteri con CERCA.VERT
• Ricercare il 2°, il 3°, l'ennesimo, valore corrispondente
• Ricerche bidimensionali con CERCA.VERT e CONFRONTA
• ANNULLA.SPAZI e CERCA.VERT
• CERCA.ORIZZ
• CERCA.VERT con CERCA.ORIZZ
• Ricercare a sinistra con INDICE e CONFRONTA
• Ricercare a sinistra con CERCA.X
• Conclusioni

Funzione CERCA.VERT

La funzione CERCA.VERT ricerca nella prima colonna di un intervallo di celle e restituisce un valore da qualsiasi cella sulla stessa riga dell'intervallo, cerca quindi verso destra.
SINTASSI
CERCA.VERT(valore;matricetabella;indice;intervallo)

• valore Obbligatorio. Valore da cercare nella prima colonna della tabella o dell'intervallo. Valore può essere un valore o un riferimento.
• matricetabella Obbligatorio. Intervallo di celle contenenti i dati. Si può usare un intervallo (es. A1:C5) o un nome di intervallo (es. Reddito). I valori nella prima colonna di matricetabella sono i valori cercati da valore e possono essere costituiti da testo, numeri o valori logici. Non viene rilevata la distinzione tra maiuscole e minuscole.
• indice Obbligatorio. Numero di colonna dell'argomento matricetabella dal quale deve essere restituito il valore corrispondente. Un argomento indice con valore 1 restituisce il valore della prima colonna di matricetabella. Un indice con valore 2 restituisce il valore della seconda colonna di matricetabella e così via. Si può usare RIF.COLONNA(rif) al posto dell’indice numerico (ragiona un po' come le funzioni di database di Excel).
• intervallo Facoltativo. Valore logico (VERO, che se non messo è il default, e FALSO) che specifica se si desidera trovare una corrispondenza esatta o approssimativa
• Se intervallo è VERO (oppure 1) o è omesso, verrà restituita una corrispondenza esatta o approssimativa. Se non viene trovata alcuna corrispondenza esatta, verrà restituito il successivo valore più grande, inferiore a valore.
  Se intervallo è VERO o è omesso, i valori nella prima colonna di matricetabella dovranno essere collocati in ordine crescente.
• Se intervallo è FALSO (oppure 0), non sarà necessario ordinare i valori della prima colonna di matricetabella. FALSO restituisce una corrispondenza esatta. Se nella prima colonna di matricetabella è presente più di un valore corrispondente a valore, verrà utilizzato il primo valore trovato. Se non viene trovata alcuna corrispondenza esatta, verrà restituito il valore di errore #N/D (Non Disponibile). Vedi tutti i messaggi di errore Excel.

Se si cercano valori di testo nella prima colonna di matricetabella, assicurarsi che i dati in tale colonna di matricetabella non contengano spazi iniziali, spazi finali, un uso non coerente delle singole o doppie virgolette o caratteri non stampabili. In questi casi, CERCA.VERT potrebbe restituire un valore non corretto o imprevisto.
Se si cercano valori numerici o date, assicurarsi che i dati nella prima colonna di matricetabella non siano memorizzati come valori di testo. In questo caso, CERCA.VERT potrebbe restituire un valore non corretto o imprevisto.

Se intervallo è FALSO e valore è un valore di testo, è possibile utilizzare i caratteri jolly punto interrogativo ? e asterisco *, in valore. Questi ultimi rappresentano rispettivamente un carattere singolo e una sequenza di caratteri qualsiasi.

Supponendo che i dati si trovano in Foglio1 del file cerca.vert.xlsx:

1. cerca in E2 il reddito di Sara
   =CERCA.VERT("Sara";A1:C5;3;FALSO)
2. cerca in F3 il reddito di Sara inserendo il nome in E3
   =CERCA.VERT(E3;A1:C5;3;FALSO)
   =CERCA.VERT(E3;A1:C5;RIF.COLONNA(C1);FALSO)
3. cerca in E3 di Foglio2 il reddito di Sara inserendo il nome in D3 di Foglio2
   =CERCA.VERT(D3;Foglio1!A1:C5;3;FALSO)
4. cerca in F3 di Foglio1 il reddito di Sara inserendo il nome in E3 di Foglio1 del file Altro.xlsx
   =CERCA.VERT(E3;[Altro.xlsx]Foglio1!A1:C5;3;FALSO)

CERCA.VERT classico

Un caso classico della funzione CERCA.VERT è quello di riempire una (o più) colonne di un foglio prendendo i dati da un altro foglio (o file).
Ad esempio se in Foglio1 si hanno degli Id, dei negozi e dei prodotti e si ha necessità di avere a partire da B2 di Foglio2 il prodotto digitando un Id, occorrerà digitare in B2 di Foglio2 la formula:
=CERCA.VERT(A2;Foglio1!A1:C5;3;FALSO)
Essa consentirà di cercare il prodotto (cioè il contenuto di una cella presente in colonna 3) nell’intervallo A1:C5 di Foglio1, in base all’Id scritto in A2 di Foglio2 (ovviamente tali Id potrebbero anche trovarsi già scritti)

Ricercare in fogli diversi con CERCA.VERT

1. Digitare il valore da cercare in Foglio1 e in Foglio2; a partire da A2 di Foglio3, scrivere il valore da cercare in colonna A di Foglio1 o Foglio2 e a partire da B2 di Foglio3 restituire il corrispondente dato di colonna B presente in Foglio1 o Foglio2; se il dato non viene trovato restituire Assente:
   =SE(CONTA.SE(Foglio1!$A$1:$A$5;A2)>0;
   CERCA.VERT(A2;Foglio1!$A$1:$B$5;2;0);
   SE(CONTA.SE(Foglio2!$A$1:$B$5;A2)>0;
   CERCA.VERT(A2;Foglio2!$A$1:$B$5;2;0);"Assente"))

1. Se il codice presente in una cella del Foglio1 è presente anche in Foglio2, a partire da B2 di Foglio2 fai comparire:
   • Sì e #N/D
     =SE(CERCA.VERT(
     Foglio1!A2:A4;Foglio2!A2:A4;1;FALSO);
     "Sì";"No")
   • Sì e No
     =SE(VAL.NON.DISP(
     CERCA.VERT(Foglio1!A2:A4;Foglio2!A2:A4;1;FALSO));
     "No";"Sì")
     

CERCA.VERT con VERO

Si è detto che se intervallo è VERO restituisce una corrispondenza esatta o approssimativa (in questo secondo caso viene restituito il successivo valore più grande, inferiore a valore) e che i valori nella prima colonna di matricetabella devono essere collocati in ordine crescente.
Nell'esempio proposto se si utilizzasse FALSO, immettendo le Età non scritte nelle celle (ad esempio 5, 30, 42, ecc.) si otterrebbe l'errore #N/D; VERO risolve questo problema poiché restituisce tutti i valori ricompresi nelle classi di Età

1. Inserendo l’età in D1 estrarre la categoria
   =CERCA.VERT(D1;A1:B6;2;VERO)

Ricercare in base a 2 criteri con CERCA.VERT

In un elenco di ordini e cercare la Quantità in base a 2 criteri: Cliente e Prodotto (il Cliente può aver ordinato più Prodotti).

CERCA.VERT restituirebbe il primo valore trovato in base al criterio specificato; per cui volendo sapere la Quantità di Tablet ordinata da Sara Neri la formula =CERCA.VERT(F3;A1:C10;3;FALSO), restituirà invece la Quantità 3 di Libri, cioè la prima corrispondenza per Sara Neri.
Sarà allora necessario concatenare Cliente e Prodotto, attraverso =I2&" "&J2,   nella colonna più a sinistra dell’intervallo di ricerca e usare =CERCA.VERT("Sara Neri Tablet";H1:K10;4;FALSO) o =CERCA.VERT(F7;H1:K10;4;FALSO) dove F7 contiene il valore concatenato e 4 è il numero della colonna contenente i dati che si desidera trovare (indice).

Ottenere il 2°, il 3°, l'ennesimo, valore corrispondente

• Trovare il secondo prodotto acquistato da Ugo Rossi:
  =CERCA.VERT("Ugo Rossi2";A1:C10;3;FALSO)
• Trovare il terzo prodotto acquistato da Ugo Rossi:
  =CERCA.VERT("Ugo Rossi3";A1:C10;3;FALSO)

Ricerche bidimensionali con CERCA.VERT e CONFRONTA

Si tratta di cercare un valore all’intersezione di una determinata riga e colonna senza conoscere il numero di colonna.
Basandosi sui dati dell’immagine precedente cercare la Quantità di Ugo Rossi2
=CERCA.VERT("Ugo Rossi2";A1:C10;
CONFRONTA("Quantità";A1:C1;0);FALSO)
Qui CERCA.VERT cerca la corrispondenza esatta di Ugo Rossi2, ma dato che non si conosce la colonna delle Quantità, si  usa CONFRONTA() per trovarla.
CONFRONTA("Quantità";A1:C1;0) significa cerca il valore Quantità nella matrice A1:C1 e tramite il 3° argomento 0 restituisci la corrispondenza esatta.

ANNULLA.SPAZI e CERCA.VERT

In generale può succedere che nonostante si sia scritta correttamente la funzione CERCA.VERT, questa non restituisca i risultati attesi; la prima domanda da porsi è se i contenuti delle celle in questione hanno la stessa lunghezza o c’è qualche spazio di troppo. Spesso accade infatti che l'utente aggiunga uno spazio finale quando digita un termine in una cella e per Excel scrivere "Valentina " e "Valentina" sono due cose diverse.
A questo punto, avendo ipoteticamente una funzione =CERCA.VERT(A3;Foglio1!A$1:B$5;2;FALSO), si può procedere in due modi:

1. utilizzare il carattere jolly * (una wildcard che significa "qualsiasi carattere") e quindi scrivere =CERCA.VERT(A3&"*";Foglio1!A$1:B$5;2;FALSO)
2. eliminare gli spazi con la funzione ANNULLA.SPAZI e applicare il CERCA.VERT sulla nuova colonna che avrà gli stessi dati ma senza spazi; questo secondo metodo è più scomodo perché crea una nuova colonna (le celle con sfondo grigio e testo EUR contengono uno spazio finale. In B6 c'è la formula =ANNULLA.SPAZI(A6))

CERCA.ORIZZ

La funzione CERCA.ORIZZ ricerca un valore nella prima riga di un intervallo di celle e restituisce un altro valore nella riga indicata in corrispondenza della colonna in cui è stato trovato.
Sintassi
CERCA.ORIZZ(valore;matricetabella;indice;intervallo)
Del tutto simile al CERCA.VERT ha l'unica differenza nell'argomento indice che questa volta indica il numero di riga da restituire anziché il numero di colonna

1. Quante Tonnellate di Frutta ha prodotto la Sicilia?
   =CERCA.ORIZZ("Frutta";A1:D4;4;FALSO)

CERCA.VERT con CERCA.ORIZZ

CERCA.VERT e CERCA.ORIZZ possono essere usate insieme!
Esempio 1 Excel INDICE CONFRONTA
Immaginando di avere una tabella con pesi e altezze di un uomo adulto, e volendo ottenere una valutazione sul suo stato di forma incrociando i dati del peso e dell'altezza, è possibile utilizzare la funzione seguente:
=INDICE($A$6:$D$9;
CONFRONTA($B$2;$A$6:$A$9;0);
CONFRONTA($B$1;$A$6:$D$6;0))
che non prevede l'uso di CERCA.VERT e CERCA.ORIZZ

Qui viene dato il nome dei prodotti con le relative vendite mensili. Indicando il prodotto e il nome del mese, si devono ottenere le vendite. In tal caso va costruita una riga ulteriore con il numero di colonna dei mesi, la funzione è:
=CERCA.VERT(B12;A16:D20;
CERCA.ORIZZ(B13;A16:D17;2;FALSO);
FALSO)

Ricercare a sinistra con INDICE e CONFRONTA

Cercare verso sinistra non è possibile con CERCA.VERT, ma per risolvere il problema posso usare CONFRONTA e INDICE
SINTASSI
CONFRONTA(valore;matrice;corrisp)
CONFRONTA cerca un dato in una matrice e restituisce la posizione relativa di questo dato, cioè la sua posizione nella colonna di ricerca. Il 3° argomento 0 indica la corrispondenza esatta.
=CONFRONTA("ROSSI"; C1:C4; 0)
Se "ROSSI" è presente restituirà un numero che rappresenta la posizione del dato all'interno di C1:C4. Nell'esempio ottengo 2, ciò significa che "ROSSI" si trova nella seconda riga, ovvero in C2.
INDICE(Matrice;Riga;Col)
INDICE restituisce il valore di una cella all'intersezione (di una determinata riga e colonna incluse in un certo intervallo).
=INDICE(matrice; riga; colonna)
Ora posso incrociare la riga 2 ottenuta con CONFRONTA, con la colonna 1 immessa in INDICE

1. Cerca il Reddito di Rossi in D2, facendo comparire la scritta:
   Il reddito di Rossi è di … Euro (ovviamente al posto di "ROSSI" è possibile usare un riferimento di cella dove scrivere ROSSI, e quindi, se decidessi di scrivere ROSSI in D2, si avrebbe CONFRONTA(D2;C1:C4;0))
   =”Reddito Rossi: ”&
   INDICE(A1:C4; CONFRONTA("ROSSI";C1:C4;0); 1)&
   ” Euro”

CERCA.X

La funzione CERCA.X, come la Funzione Cerca.Vert, serve per trovare elementi contenuti in un riga all'interno di una tabella o in un intervallo dati. Il vantaggio è che CERCA.X di Excel risolve alcuni problemi della funzione CERCA.VERT.

Sintassi della Funzione CERCA.X

La sintassi della Funzione CERCA.X presenta 6 argomenti di cui i primi 3 obbligatori:
=CERCA.X(
Valore;
Matrice_ricerca;
Matrice_restituita;
[se_non_trovato];
[modalità_confronto];
[modalità_ricerca]
)

Valore è il dato da ricercare nella tabella
Matrice_ricerca è la colonna della tabella dove ricercare il valore
Matrice_restituita è la colonna della tabella dove risiede il valore che si desidera in restituzione
[se non trovato] (facoltativo) definisce una risposta se la funzione restituisce #N/D, ad esempio "Non presente" senza ricorrere alla funzione se.errore
[modalità di confronto] (facoltativo) definisce i codici 0 = corrispondenza esatta; -1 corrispondenza esatta o elemento successivo più piccolo; 1 = corrispondenza esatta o numero successivo più grande; 2 = corrispondenza carattere jolly;
[modalità_ricerca] (facoltativo) definisce i codici 1 = ricerca dal primo all'ultimo dei valori presenti nell'elenco; -1 ricerca dall'ultimo al primo dei valori presenti nell'elenco; 2 Ricerca binaria in ordine crescente; -2 ricerca binaria in ordine decrescente

Cercare a sinistra con la funzione Excel CERCA.X

CERCA.VERT non può cercare a sinistra e per farlo è necessario utilizzare una funzione annidata con INDICE e CONFRONTA, come visto in precedenza. In realtà è possibile usare CERCA.X.

Riprendendo proprio l’esempio con INDICE e CONFRONTA, quello dei Redditi con l’Età e i Cognomi, per cercare il Reddito di ROSSI, che ad esempio decido di scrivere in D2, basta utilizzare la seguente sintassi:
=CERCA.X(D2;C2:C4;A2:A4)
dove C2:C4 contiene il cognome e A2:A4 il reddito

Gestire il codice di errore #N/D con CERCA.X

Conclusioni

Superata la paura iniziale per capirne il funzionamento, CERCA.VERT, CERCA.ORIZZ, INDICE, CONFRONTA, CERCA.X diventano abbastanza semplici da utilizzare e, anche se all'inizio sembrano funzioni molto complicate, non lo sono.
Ricorda che in CERCA.VERT la prima colonna dell'intervallo dove cercare (matricetabella) deve contenere il valore da trovare. Se ti interessa puoi simulare il CERCA.VERT con le Power Query.