La formula =CERCA.VERT(F2;A2:D6;3;FALSO) cerca il valore di F2 nella prima colonna di A2:D6 e restituisce il valore della terza colonna della stessa riga. FALSO alla fine significa "solo corrispondenza esatta". Scegli un altro prodotto in F2 e il prezzo cambia. Cerca vert in Excel inglese si chiama VLOOKUP, ed è così che la mostra la tabella: =VLOOKUP(F2,A2:D6,3,FALSE). Puoi però scrivere le formule nella tabella anche in italiano, con il punto e virgola.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
=CERCA.VERT(F2;A2:D6;3;FALSO)Fai clic su G2 e la tabella A2:D6 viene evidenziata. Cambia il 3 nella formula in 2 e G2 restituisce la categoria invece del prezzo, perché Category è la seconda colonna della tabella. Maiuscole e minuscole non contano: pear trova Pear.
Sintassi di CERCA.VERT
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argomento | Cos'è | Nell'esempio |
|---|---|---|
lookup_value (valore) | Il valore da trovare. | F2 (Pear) |
table_array (matrice_tabella) | La tabella in cui cercare. CERCA.VERT cerca solo nella sua prima colonna. | A2:D6 |
col_index_num (indice) | Quale colonna della tabella restituire, contando dalla prima colonna della tabella (1). | 3 (Price) |
range_lookup (intervallo) | FALSO o 0 per una corrispondenza esatta. VERO, 1 o niente per una corrispondenza approssimata. | FALSO |
Il numero di colonna conta dall'inizio della tabella, non dalla colonna A del foglio. In una tabella che inizia nella colonna C, col_index_num 2 indica la colonna D. Un numero più grande della larghezza della tabella restituisce #RIF! (in inglese #REF!), e 0 restituisce #VALORE! (in inglese #VALUE!).
In Excel italiano gli argomenti sono separati dal punto e virgola, perché la virgola è il separatore decimale: =CERCA.VERT(F2;A2:D6;3;FALSO).
Scegliere la colonna da restituire con CONFRONTA
Un 3 scritto a mano si rompe senza avvisare quando qualcuno inserisce una colonna dentro la tabella: la formula continua a restituire la terza colonna, che ora contiene altro. Lascia invece che CONFRONTA (in inglese MATCH) trovi il numero di colonna dall'intestazione. Qui G1 è un elenco a discesa: scegli Stock o Category e G2 si adegua.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Carrot | 0.8 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
=CERCA.VERT(F2;A2:D6;CONFRONTA(G1;A1:D1;0);FALSO)CONFRONTA(G1;A1:D1;0) restituisce la posizione di "Price" nella riga delle intestazioni, 3, e CERCA.VERT la usa come numero di colonna: 0,8 per Carrot. È una ricerca bidimensionale: una riga scelta in base al prodotto, una colonna scelta in base all'intestazione. La stessa idea scritta con INDICE al posto di CERCA.VERT è nella pagina INDICE e CONFRONTA.
Corrispondenza approssimata: CERCA.VERT con VERO
Con VERO come ultimo argomento, CERCA.VERT non cerca un valore uguale. Trova il valore più grande minore o uguale al valore cercato. È ciò che serve per le fasce: scaglioni fiscali, voti, tariffe di spedizione, livelli di provvigione. La prima colonna deve essere ordinata dal più piccolo al più grande.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 0 | 0% | Ana | 750 | 0% | |
| 3 | 1000 | 3% | Ben | 4,200 | 3% | |
| 4 | 5000 | 5% | Cara | 5,000 | 5% | |
| 5 | 10000 | 8% | Dev | 12,500 | 8% |
=CERCA.VERT(E2;$A$2:$B$5;2;VERO)I 4.200 di Ben non sono nella colonna A. Il valore più grande che non li supera è 1.000, quindi riceve il 3%. I 5.000 di Cara corrispondono esattamente alla riga 5.000 e ricevono il 5%. I 12.500 di Dev superano l'ultima fascia e ricevono l'ultima percentuale, l'8%. Un valore sotto la prima fascia (qui un importo di vendite negativo) restituisce #N/D (in inglese #N/A), ed è per questo che la tabella parte da 0.
I simboli $ in $A$2:$B$5 tengono ferma la tabella quando F2 viene copiata fino a F5. Senza di essi, F3 cercherebbe in A3:B6 e salterebbe la prima fascia.
Omettere il quarto argomento equivale a VERO. Su un elenco di prodotti non ordinato è un errore silenzioso: Excel cerca come se l'elenco fosse ordinato e può restituire un prezzo dalla riga sbagliata, oppure #N/D per un valore che c'è. Quando cerchi nomi, codici o ID, termina sempre con FALSO.
Perché CERCA.VERT restituisce #N/D
#N/D significa "non trovato" (la tabella qui lo mostra in inglese, #N/A). La tabella qui sotto mostra tre cause comuni, e la colonna G ripete ogni ricerca racchiusa in SE.NON.DISP. (in inglese IFNA) e ANNULLA.SPAZI (in inglese TRIM).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | Fixed |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | #N/A | Not found |
| 3 | Pear | Fruit | $1.50 | 25 | Milk | #N/A | $1.10 |
| 4 | Carrot | Vegetable | $0.80 | 60 | Fruit | #N/A | Not found |
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
#N/A Il valore cercato non è nell’intervallo di ricerca.In Excel in italiano: =CERCA.VERT(E2;$A$2:$D$6;3;FALSO)- Il valore non è nella tabella. Kiwi non compare in A2:A6. È un vero "non trovato", e
SE.NON.DISP.(...;"Not found")lo trasforma in un testo leggibile. Cambia E2 in Apple ed entrambe le colonne mostrano il prezzo. - Spazi in più. E3 contiene
"Milk "con uno spazio finale, quindi non è uguale aMilk.ANNULLA.SPAZI(E3)lo toglie e G3 trova il prezzo. Se invece gli spazi sono nella tabella, ripulisci la colonna A con ANNULLA.SPAZI una volta sola invece che in ogni ricerca. - Il valore è in un'altra colonna. Fruit esiste, ma nella colonna B. CERCA.VERT cerca solo nella prima colonna della tabella, quindi E4 fallisce in entrambe le colonne. Fai iniziare la tabella dalla colonna in cui cerchi, oppure usa CERCA.X, che riceve separatamente la colonna di ricerca e quella da restituire.
Attorno a una ricerca usa SE.NON.DISP. invece di SE.ERRORE (in inglese IFERROR). SE.NON.DISP. intercetta solo #N/D, quindi un #RIF! dovuto a un numero di colonna sbagliato resta visibile invece di essere nascosto come "Not found".
Altre due cause:
- Numeri memorizzati come testo. Se la colonna A contiene codici prodotto inseriti come testo (spesso dopo un'importazione, con un piccolo triangolo verde nell'angolo) e F2 contiene il numero 101,
=CERCA.VERT(F2;A2:B6;2;FALSO)restituisce #N/D anche se 101 è nell'elenco. Converti uno dei due lati:=CERCA.VERT(F2&"";A2:B6;2;FALSO)cerca il testo "101", e=CERCA.VERT(VALORE(F2);A2:B6;2;FALSO)cerca un numero quando F2 è il testo. - Corrispondenza approssimata su dati non ordinati, descritta nella sezione qui sopra.
CERCA.VERT restituisce 0 invece di una cella vuota
Quando la cella su cui arriva CERCA.VERT è vuota, Excel mostra 0, non una cella vuota. Uno 0 nella colonna Stock si legge allora come "esaurito" quando la giacenza non è mai stata inserita. Aggiungi &"" alla formula, oppure verifica la lunghezza del risultato:
=VLOOKUP(F2,A2:D6,4,FALSE)&""
=IF(LEN(VLOOKUP(F2,A2:D6,4,FALSE))=0,"",VLOOKUP(F2,A2:D6,4,FALSE))
In Excel italiano sono =CERCA.VERT(F2;A2:D6;4;FALSO)&"" e =SE(LUNGHEZZA(CERCA.VERT(F2;A2:D6;4;FALSO))=0;"";CERCA.VERT(F2;A2:D6;4;FALSO)). La prima è più corta ma trasforma in testo ogni numero che restituisce, quindi una SOMMA successiva lo salta. La seconda lascia i numeri come numeri.
CERCA.VERT da un altro foglio
Scrivi il nome del foglio e ! prima della tabella. Quando costruisci la formula in Excel, fai clic sulla scheda dell'altro foglio e seleziona l'intervallo: Excel scrive Prices!A2:B6 al posto tuo. Qui il foglio Orders cerca i prezzi nel foglio Prices.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Qty | Price | Total |
| 2 | 1001 | Pear | 3 | $1.50 | $4.50 |
| 3 | 1002 | Milk | 2 | $1.10 | $2.20 |
| 4 | 1003 | Apple | 5 | $1.20 | $6.00 |
| 5 | 1004 | Bread | 1 | $2.40 | $2.40 |
=CERCA.VERT(B2;Prices!$A$2:$B$6;2;FALSO)Apri il foglio Prices e cambia il prezzo di Apple: il totale dell'ordine si aggiorna. Due dettagli:
- Un nome di foglio con spazi richiede gli apici singoli:
=CERCA.VERT(B2;'Price list'!$A$2:$B$6;2;FALSO). - Una tabella in un'altra cartella di lavoro aggiunge il nome del file tra parentesi quadre,
[Prices.xlsx]Prices!$A$2:$B$6. Quando quel file è chiuso, Excel mostra il percorso completo nella formula e la ricerca continua a funzionare sul file salvato.
CERCA.VERT con caratteri jolly (corrispondenza parziale)
Con FALSO, il valore cercato può contenere caratteri jolly: * sta per un numero qualsiasi di caratteri e ? per esattamente uno. "*"&E2&"*" trova il primo prodotto il cui nome contiene il testo di E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Contains | Price | |
| 2 | Green tea | Drinks | $3.20 | coffee | $2.90 | |
| 3 | Iced coffee | Drinks | $2.90 | |||
| 4 | Coffee beans | Pantry | $8.50 | |||
| 5 | Black tea | Drinks | $2.70 | |||
| 6 | Orange juice | Drinks | $3.40 |
=CERCA.VERT("*"&E2&"*";A2:C6;3;FALSO)"coffee" corrisponde sia a Iced coffee sia a Coffee beans; CERCA.VERT restituisce il primo partendo dall'alto, $2.90. Cambia E2 in bean per ottenere $8.50, oppure in juice. Per cercare un vero asterisco o punto interrogativo, mettici davanti una tilde: "~*".
CERCA.VERT verso sinistra
CERCA.VERT non può restituire una colonna a sinistra di quella in cui cerca: col_index_num conta solo verso destra, e i numeri negativi sono un errore. Per trovare il prodotto con un certo prezzo, cerca nella colonna C e restituisci la colonna A con CERCA.X (in inglese XLOOKUP) oppure con INDICE e CONFRONTA:
=XLOOKUP(2.4, C2:C6, A2:A6) Excel 2021 and Microsoft 365
=INDEX(A2:A6, MATCH(2.4, C2:C6, 0)) every version
In Excel italiano: =CERCA.X(2,4;C2:C6;A2:A6) e =INDICE(A2:A6;CONFRONTA(2,4;C2:C6;0)). Entrambe restituiscono Bread con i dati della prima tabella. CERCA.X ha la spiegazione completa.
Esercizio: costo di spedizione in base al peso
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | Cost | Weight (kg) | Cost | |
| 2 | 0 | $4.50 | 7 | ||
| 3 | 2 | $6.00 | |||
| 4 | 5 | $9.50 | |||
| 5 | 10 | $14.00 | |||
| 6 | 20 | $22.00 |
Tocca a te: Ogni costo vale dal suo peso fino al peso successivo dell'elenco. In E2, usa CERCA.VERT per restituire il costo di spedizione del pacco con il peso in D2.
CERCA.VERT con due criteri
CERCA.VERT accetta un solo valore da cercare. Per cercare in base a due colonne, crea una colonna di appoggio che le unisca, mettila per prima nella tabella e cerca lo stesso testo unito. La colonna A qui sotto è =B2&"-"&C2 copiata verso il basso, quindi contiene Coffee-Small, Coffee-Large e così via.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Key | Product | Size | Price | Product | Size | Price |
| 2 | Coffee-Small | Coffee | Small | $2.50 | Tea | Large | |
| 3 | Coffee-Large | Coffee | Large | $3.50 | |||
| 4 | Tea-Small | Tea | Small | $2.00 | |||
| 5 | Tea-Large | Tea | Large | $3.00 | |||
| 6 | Juice-Small | Juice | Small | $3.00 |
Tocca a te: La colonna A unisce prodotto e formato con un trattino. In G2, restituisci il prezzo per il prodotto in E2 e il formato in F2.
Il separatore conta: "Tea"&"Large" dà TeaLarge, che non corrisponde a nulla nella colonna A. In Excel 2021 e Microsoft 365 puoi fare a meno della colonna di appoggio con =CERCA.X(1;(B2:B6=E2)*(C2:C6=F2);D2:D6); la pagina sulla ricerca con più criteri mostra questa formula e la versione con INDICE e CONFRONTA.
Domande frequenti
Come si fa un CERCA.VERT in Excel?
Scrivi =CERCA.VERT( e indica quattro argomenti: il valore da trovare, la tabella (la cui prima colonna deve contenere quel valore), il numero della colonna da restituire, e FALSO per una corrispondenza esatta. =CERCA.VERT("Pear";A2:D6;3;FALSO) trova Pear nella colonna A e restituisce il valore della colonna C di quella riga.
Cosa significa VERO o FALSO alla fine di CERCA.VERT?
FALSO (o 0) chiede una corrispondenza esatta e restituisce #N/D quando il valore manca. VERO (o 1, o l'argomento omesso) chiede una corrispondenza approssimata: il valore più grande minore o uguale al valore cercato, e funziona solo quando la prima colonna è ordinata in modo crescente.
Perché il mio CERCA.VERT restituisce #N/D?
Il valore non è stato trovato nella prima colonna della tabella. Le cause abituali sono un errore di battitura, uno spazio in più ("Milk " non è "Milk"), un numero memorizzato come testo da una parte sola, o un valore che sta in un'altra colonna. Racchiudi la formula in SE.NON.DISP. per mostrare un tuo testo: =SE.NON.DISP.(CERCA.VERT(F2;A2:D6;3;FALSO);"Not found").
CERCA.VERT può cercare verso sinistra?
No. CERCA.VERT restituisce solo colonne a destra della prima colonna della tabella. Usa =CERCA.X(F2;C2:C6;A2:A6) in Excel 2021 o Microsoft 365, oppure =INDICE(A2:A6;CONFRONTA(F2;C2:C6;0)) in qualsiasi versione.
Come si fa un CERCA.VERT da un altro foglio?
Scrivi il nome del foglio e un punto esclamativo prima dell'intervallo: =CERCA.VERT(B2;Prices!$A$2:$B$6;2;FALSO). Se il nome del foglio contiene uno spazio, racchiudilo tra apici singoli: 'Price list'!$A$2:$B$6.
