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

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
  • Esempi
• Un caso classico del CERCA.VERT
• Ricercare in fogli diversi
• Ricercare con VERO
• Ricercare a sinistra con INDICE e CONFRONTA
• Ricercare in base a 2 diversi criteri
• Ricercare il 2°, il 3°, l'ennesimo, valore corrispondente
• Ricerche bidimensionali con CERCA.VERT e CONFRONTA
• Il problema degli spazi
• Funzione CERCA.ORIZZ
• 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.
• intervallo  Facoltativo. Valore logico (VERO, che se non lo metto è 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).

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.

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)

Ricercare in fogli diversi

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

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à

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

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

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.

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

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)

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