#Excel CERCA.VERT, CERCA.ORIZZ, INDICE, CONFRONTA

FALSO, VERO e cercare a sinistra con Excel

Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 01 Aprile 2021
Categoria: Microsoft Office Excel CERCA.VERT CERCA.ORIZZ INDICE CONFRONTA

Excel funzione CERCA.VERT
#Excel: CERCA.VERT, CERCA.ORIZZ, INDICE, CONFRONTA

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.
Come tutte le altre funzioni di Excel è possibile inserirla a mano o sfruttando una finestra. Vediamo dunque l'uso delle funzioni CERCA.VERT, CERCA.ORIZZ, INDICE, CONFRONTA e quando usare FALSO, VERO, come cercare a sinistra con Excel.

Sommario Funzione CERCA.VERT

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)

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.


Funzione CERCA.VERT
Excel: funzione CERCA.VERT

Esercizi:

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)

Un caso classico del CERCA.VERT
Un caso classico del CERCA.VERT

Ricercare in fogli diversi con CERCA.VERT

Funzione CERCA.VERT
Excel: funzione CERCA.VERT per fogli diversi

Esercizio:
  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"))
  2. Funzione CERCA.VERT
    Excel: funzione CERCA.VERT per fogli diversi


Funzione CERCA.VERT
Excel: funzione CERCA.VERT per fogli diversi

Esercizio:
  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à

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

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

Funzione CONFRONTA e INDICE
Excel: funzioni CONFRONTA e INDICE
Esercizio:
  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”
Per cercare a sinistra è anche possibile utilizzare la funzione CERCA.X che vedremo più avanti.

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).

Excel Ricercare in base a 2 diversi criteri
Excel: funzione CERCA.VERT per cercare con criteri diversi

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

Per ottenere il 2°, il 3°, ecc. prodotto acquistato da un cliente, è possibile aggiungere una colonna Cliente n prima della colonna Cliente popolandolo con nome e numero di occorrenza del cliente, ad esempio, Ugo Rossi1, Ugo Rossi2, ecc., per non farlo a mano si usa  =B2&CONTA.SE($B$2:B2;B2) in A2, supponendo che i nomi dei clienti siano in colonna B, e poi CERCA.VERT (eventualmente inserendo un riferimento di cella al posto del testo del valore di ricerca) per trovare l’ordine corrispondente: Excel Ottenere il 2°, il 3°, l'ennesimo, valore corrispondente
Excel: funzione CERCA.VERT per ottenere l'ennesimo valore

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.

Il problema degli spazi nel 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))
Excel Il problema degli spazi
Il problema degli spazi nel CERCA.VERT

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

Funzione CERCA.ORIZZ
Excel: funzione CERCA.ORIZZ
Esercizio:
  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

Esempio 2 Excel CERCA.VERT 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)

Excel CERCA.VERT CERCA.ORIZZ
Excel CERCA.VERT CERCA.ORIZZ

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.

Funzione CERCA.X
Excel: funzioni 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

Non è necessario ricorrere alla funzione SE.ERRORE per definire un codice di errore alternativo a #N/D
Sempre riferendosi all’esempio appena fatto, se cercassi il cognome Marini, non presente all’interno della tabella, che infatti ha Rossi Bianchi e Neri, si otterrebbe un errore #N/D. Per ottenere al suo posto la stringa “Non presente”, si può usare la seguente sintassi:
=CERCA.X(D2;C2:C4;A2:A4;"Non presente")


Conclusioni

Superata la paura iniziale per capirne il funzionamento, CERCA.VERT diventa abbastanza semplice da utilizzare e, anche se all'inizio sembra una funzione molto complicata, non lo è.
Ricorda che 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.

Per saperne di più su Microsoft Excel potrebbero interessarti i video corsi Excel o questi libri:

Se vuoi approfondire alcuni dei temi trattati, visita la pagina con le mie pubblicazioni cartacee e online

Se l'articolo ti è piaciuto, condividilo!

Segui l'hashtag #AndreaPacchiarotti