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

Quando usare FALSO e quando usare VERO


Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 03 Maggio 2018
Categoria: Microsoft Office Excel


Excel funzione CERCA.VERT

La funzione CERCA.VERT di Microsoft Office Excel è probabilmente una delle più utilizzate di Excel, essa consente di eseguire di ricercare un dato nella prima colonna di un intervallo di celle e quindi di restituire un valore da qualsiasi cella sulla stessa riga dell'intervallo.
Insieme alla funzione CERCA.ORIZZ ed a molte altre fa parte delle formule di ricerca e riferimento dell'applicativo Excel.
Come tutte le altre funzioni di Excel è possibile inserirla a mano o sfruttando una finestra di riferimento.

Indice 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 matrice_tabella 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)

Un caso classico del CERCA.VERT

Un caso classico di uso 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

Ricercare in fogli diversi

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ì")

Ricercare 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

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'es. 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
    =”Il reddito di Rossi è di ”&INDICE(A1:C4; CONFRONTA("ROSSI";C1:C4;0); 1)&” Euro”

Ricercare in base a 2 diversi criteri

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

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

Conclusioni

Superata la paura iniziale per capirne il funzionamento, CERCA.VERT diventa abbastanza semplice da utilizzare e, anche se all'inizio sembra molto complicata, non lo è.
Ricorda che la prima colonna dell'intervallo dove cercare (matricetabella) deve contenere il valore da trovare.

Per ricevere nella tua mail tips and tricks, ovvero suggerimenti e trucchi su Microsoft Office e Web in generale iscriviti gratuitamente alla newsletter


Richiedi un preventivo gratuito

Consento il trattamento dei dati personali ai sensi del GDPR 2018 - Privacy policy