Come utilizzare la funzione CERCA.VERT di Excel | Exceljet

CERCA.VERT è una funzione di Excel per ottenere dati da una tabella organizzata verticalmente. I valori di ricerca devono essere visualizzati nella prima colonna della tabella passata a CERCA.VERT. CERCA.VERT supporta la corrispondenza approssimativa ed esatta e i caratteri jolly (*?) Per le corrispondenze parziali.

V sta per verticale

Lo scopo di CERCA.VERT è ottenere informazioni da una tabella organizzata in questo modo:

Utilizzando il numero dellordine nella colonna B come valore di ricerca, CERCA.VERT può ottenere lID cliente, limporto, il nome e lo stato per qualsiasi ordine. Ad esempio, per ottenere il nome del cliente per lordine 1004, la formula è:

=VLOOKUP(1004,B5:F9,4,FALSE) // returns "Sue Martin"

Per i dati orizzontali, puoi utilizzare CERCA.ORIZZ, INDICE e CORRISPONDENZA o XLOOKUP.

CERCA.VERT si basa sui numeri di colonna

Quando si utilizza CERCA.VERT, immagina che ogni colonna della tabella sia numerata, a partire da sinistra. Per ottenere un valore da una particolare colonna, fornire il numero appropriato come “indice di colonna”. Ad esempio, lindice della colonna per recuperare il nome di seguito è 2:

Il cognome e lemail possono essere recuperati con le colonne 3 e 4:

=VLOOKUP(H3,B4:E13,2,FALSE) // first name=VLOOKUP(H3,B4:E13,3,FALSE) // last name=VLOOKUP(H3,B4:E13,4,FALSE) // email address

CERCA.VERT sembra solo a destra

CERCA.VERT può guardare solo a destra. I dati che desideri recuperare (valori dei risultati) possono essere visualizzati in qualsiasi colonna a destra dei valori di ricerca:

Se è necessario cercare i valori a sinistra, vedere INDICE e CORRISPONDENZA o XLOOKUP.

Corrispondenza esatta e approssimativa

CERCA.VERT ha due modalità di corrispondenza, esatta e approssimativa. Il nome dellargomento che controlla la corrispondenza è “range_lookup”. Questo è un nome confuso, perché sembra avere qualcosa a che fare con intervalli di celle come A1: A10. In realtà, la parola “intervallo” in questo caso si riferisce a “intervallo di valori”: quando range_lookup è TRUE, CERCA.VERT corrisponderà a un intervallo di valori anziché a un valore esatto. Un buon esempio di ciò è lutilizzo di CERCA.VERT per calcolare i voti.

È importante capire che range_lookup è impostato su TRUE, il che significa che CERCA.VERT utilizzerà la corrispondenza approssimativa per impostazione predefinita, il che può essere pericoloso. Imposta range_lookup su FALSE per forzare la corrispondenza esatta:

Nota: puoi anche fornire zero (0) invece di FALSE per una corrispondenza esatta.

Corrispondenza esatta

Nella maggior parte dei casi, probabilmente vorrai utilizzare CERCA.VERT in modalità di corrispondenza esatta. Ciò ha senso quando hai una chiave univoca da utilizzare come valore di ricerca, ad esempio, il titolo del film in questi dati:

La formula in H6 per trovare lanno, basata su una corrispondenza esatta del titolo del film, è:

=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact match

Corrispondenza approssimativa

Nei casi in cui desideri la corrispondenza migliore, non necessariamente una corrispondenza esatta, dovrai utilizzare la modalità approssimativa. Ad esempio, di seguito vogliamo cercare un tasso di commissione nella tabella G5: H10. I valori di ricerca provengono dalla colonna C. In questo esempio, è necessario utilizzare CERCA.VERT in modalità di corrispondenza approssimativa, poiché nella maggior parte dei casi non verrà mai trovata una corrispondenza esatta. La formula CERCA.VERT in D5 è configurata per eseguire una corrispondenza approssimativa impostando lultimo argomento su VERO:

=VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = approximate match

CERCA.VERT esegue la scansione dei valori nella colonna G per il valore di ricerca. Se viene trovata una corrispondenza esatta, CERCA.VERT la utilizzerà. In caso contrario, CERCA.VERT “tornerà indietro” e corrisponderà alla riga precedente.

Nota: i dati devono essere ordinati in ordine crescente in base al valore di ricerca quando si utilizza la modalità di corrispondenza approssimativa con CERCA.VERT.

Prima corrispondenza

Nel caso di valori duplicati, CERCA.VERT troverà la prima corrispondenza quando la modalità di corrispondenza è esatta. Nella schermata sottostante, CERCA.VERT è configurato per trovare il prezzo per il colore “Verde”. Sono presenti tre voci con il colore verde e CERCA.VERT restituisce il prezzo per la prima voce, $ 17. La formula nella cella F5 è:

=VLOOKUP(E5,B5:C11,2,FALSE) // returns 17

Corrispondenza con caratteri jolly

La funzione CERCA.VERT supporta i caratteri jolly, che rendono possibile eseguire una corrispondenza parziale su un valore di ricerca. Ad esempio, è possibile utilizzare CERCA.VERT per recuperare i valori da una tabella dopo aver digitato solo una parte di un valore di ricerca. Per utilizzare i caratteri jolly con CERCA.VERT, è necessario specificare la modalità di corrispondenza esatta fornendo FALSE o 0 per lultimo argomento, range_lookup. La formula in H7 recupera il nome, “Michael”, dopo aver digitato “Aya” nella cella H4:

=VLOOKUP($H$4&"*",$B$5:$E$104,2,FALSE)

Leggi una spiegazione più dettagliata qui.

Ricerca a due vie

Allinterno della funzione CERCA.VERT, la colonna Largomento indice è normalmente codificato come numero statico.Tuttavia, puoi anche creare un indice di colonna dinamico utilizzando la funzione CONFRONTA per individuare la colonna di destra. Questa tecnica consente di creare una ricerca dinamica a due vie, che corrisponde sia alle righe che alle colonne. Nella schermata seguente, CERCA.VERT è configurato per eseguire una ricerca in base a Nome e Mese. La formula in H6 è:

=VLOOKUP(H4,B5:E13,MATCH(H5,B4:E4,0),0)

Per ulteriori dettagli, vedere questo esempio.

Nota: in generale, INDICE e CONFRONTA è un modo più flessibile per eseguire ricerche a due vie.

Criteri multipli

La funzione CERCA.VERT non gestisce più criteri in modo nativo. Tuttavia, puoi utilizzare una colonna helper per unire più campi insieme e utilizzare questi campi come più criteri allinterno di CERCA.VERT. Nellesempio seguente, la colonna B è una colonna helper che concatena il nome e il cognome insieme a questa formula:

=C5&D5 // helper column

CERCA.VERT è configurato per fare la stessa cosa per creare un valore di ricerca. La formula in H6 è:

=VLOOKUP(H4&H5,B5:E13,4,0)

Per i dettagli, vedere questo esempio.

Nota: INDEX e MATCH e XLOOKUP sono modi più affidabili per gestire le ricerche in base a più criteri.

VLOOKUP e # N / D errori

Se usi CERCA.VERT ti imbatterai inevitabilmente nellerrore # N / A. Lerrore # N / A significa semplicemente “non trovato”. Ad esempio, nella schermata seguente, il valore di ricerca “Toy Story 2” non esiste nella tabella di ricerca e tutte e tre le formule CERCA.VERT restituiscono # N / A:

Un modo per “intercettare” lerrore NA è usare la funzione IFNA in questo modo:

La formula in H6 è:

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"Not found")

Il messaggio può essere personalizzato come desiderato. Per non restituire nulla (cioè per visualizzare un risultato vuoto) quando CERCA.VERT restituisce # N / D puoi usare una stringa vuota come questa:

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"") // no message

Lerrore # N / D è utile perché ti dice che qualcosa non va. In pratica, ci sono molte ragioni per cui potresti visualizzare questo errore, tra cui:

Il valore di ricerca non esiste nella tabella
Il valore di ricerca è scritto in modo errato o contiene spazio extra
La modalità di corrispondenza è esatta, ma dovrebbe essere approssimativa
Lintervallo della tabella non è stato inserito correttamente
Stai copiando CERCA.VERT e il riferimento alla tabella non lo è bloccato

Ulteriori informazioni: CERCA.VERT senza errori # N / D

Ulteriori informazioni su CERCA.VERT

Altri esempi CERCA.VERT
Video CERCA.VERT
23 suggerimenti per lutilizzo di CERCA.VERT

Altre note

Range_lookup controlla se il valore deve corrispondere esattamente o meno. Il valore predefinito è TRUE = consenti corrispondenza non esatta.
Imposta range_lookup su FALSE per richiedere una corrispondenza esatta e TRUE per consentire una corrispondenza non esatta.
Se range_lookup è TRUE (il impostazione predefinita), una corrispondenza non esatta farà sì che la funzione CERCA.VERT corrisponda al valore più vicino nella tabella che è ancora inferiore al valore.
Quando range_lookup viene omesso, la funzione CERCA.VERT consentirà un valore non esatto match, ma utilizzerà una corrispondenza esatta se ne esiste una.
Se range_lookup è TRUE (limpostazione predefinita) assicurati che i valori di ricerca nella prima riga della tabella siano ordinati in ordine crescente. In caso contrario, CERCA.VERT potrebbe restituire un valore errato o imprevisto.
Se range_lookup è FALSE (richiede la corrispondenza esatta), i valori nella prima colonna della tabella non devono essere ordinati.

Funzione CERCA.VERT di Excel