10 Esempi di VLOOKUP per principianti e utenti avanzati

La funzione VLOOKUP è il punto di riferimento.

Se sai come usare la funzione CERCA.VERT, sai qualcosa su Excel.

In caso contrario, faresti meglio a non elencare Excel come uno dei tuoi punti di forza nel tuo curriculum.

Ho fatto un colloquio di gruppo e una volta che un candidato ha menzionato Excel come sua area di competenza, la prima cosa che gli è stata chiesta è stata – ti viene l'idea – la funzione CERCA.VERT.

Ora che conosciamo l'importanza di questa funzione di Excel, ha senso comprenderla appieno in modo da poter dire con orgoglio: "So una o due cose in Excel".

Questo sarà un enorme tutorial VLOOKUP (secondo i miei standard).

Tratterò tutto e poi ti mostrerò un esempio utile e pratico di CERCA.VERT.

Quando utilizzare la funzione CERCA.VERT in Excel?

La funzione CERCA.VERT funziona meglio quando cerchi punti dati corrispondenti in una colonna e quando viene trovato un punto dati corrispondente, prendi la colonna a destra della riga e ottieni il valore dal numero di celle specificato.

Facciamo un semplice esempio qui per capire quando usare Vlookup in Excel.

Ricorda, tutti erano soliti cercare freneticamente i propri nomi e punteggi quando i punteggi dei test uscivano e si attaccavano alle bacheche (almeno è quello che è successo spesso quando ero a scuola).

Ecco come funziona:

• Vai in bacheca e inizi a cercare il tuo nome o numero di registrazione (muovi il dito su e giù per l'elenco).
• Una volta trovato il tuo nome, muoverai gli occhi a destra del nome/numero di registrazione per vedere il tuo punteggio.

Questo è esattamente ciò che fa per te la funzione CERCA.VERT di Excel (sentiti libero di usare questo esempio nella tua prossima intervista).

La funzione CERCA.VERT cerca un valore specificato in una colonna (nell'esempio sopra, è il tuo nome) e quando trova una corrispondenza specificata, restituisce un valore (il token che ottieni) nella stessa riga.

sintassi

=CERCA.VERT(valore_ricerca, matrice_tabella, numero_indice_col, [ricerca_intervallo])

Parametri di input

• valore di ricerca -Questo è il valore di ricerca che stai cercando di trovare nella colonna più a sinistra della tabella.Può essere un valore, un riferimento di cella o una stringa di testo.Nell'esempio del referto, questo sarebbe il tuo nome.
• matrice di tabella -Questa è la matrice di tabelle in cui stai cercando i valori.Può essere un riferimento a un intervallo di celle oa un intervallo denominato.Nell'esempio della tabella dei punteggi, questa sarebbe l'intera tabella contenente i punteggi di tutti per ciascuna materia
• indice_col –Questo è il numero di indice della colonna da cui si desidera ottenere i valori corrispondenti.Nell'esempio della tabella delle frazioni, se vuoi una frazione per la matematica (questa è la prima colonna in una tabella contenente frazioni), puoi guardare la colonna 1.Se vuoi un punteggio fisico, puoi guardare le colonne 1 e 2.
• [ricerca_intervallo] –Qui puoi specificare se desideri una corrispondenza esatta o una corrispondenza approssimativa.Se omesso, il valore predefinito è TRUE - corrispondenza approssimativa(vedi note aggiuntive sotto).

Note aggiuntive (noioso, ma importante da sapere)

• Le corrispondenze possono essere esatte (FALSE o 0 in range_lookup) o approssimative (TRUE o 1).
• In una ricerca approssimativa, assicurati che l'elenco sia ordinato in ordine crescente (dall'alto verso il basso), altrimenti i risultati potrebbero essere imprecisi.
• Quando range_lookup è TRUE (ricerca approssimativa) e i dati sono ordinati in ordine crescente:
  • Se la funzione CERCA.VERT non riesce a trovare il valore, restituisce il valore più grande inferiore a lookup_value.
  • Restituisce un errore #N/D se valore_ricerca è inferiore al valore minimo.
  • Se lookup_value è testo, è possibile utilizzare i caratteri jolly (vedere l'esempio di seguito).

Ora, si spera che tu abbia una conoscenza di base di cosa può fare la funzione CERCA.VERT, sbucciamo la cipolla e vediamo alcuni esempi pratici della funzione CERCA.VERT.

10 Esempi di CERCA.VERT in Excel (di base e avanzati)

Ecco 10 utili esempi di utilizzo di Excel Vlookup per mostrarti come usarlo nel tuo lavoro quotidiano.

Esempio 1 - Trova il punteggio matematico di Brad

Nell'esempio VLOOKUP di seguito, ho un elenco con i nomi degli studenti nella colonna più a sinistra e i voti per le diverse materie nelle colonne da B a E.

Ora mettiamoci al lavoro e usiamo la funzione CERCA.VERT per fare ciò che sa fare meglio.Dai dati sopra, ho bisogno di sapere cosa ha segnato Brad in matematica.

Dai dati sopra, ho bisogno di sapere cosa ha segnato Brad in matematica.

Ecco la formula CERCA.VERT che restituisce il punteggio matematico di Brad:

= CERCA.VERT("Brad",$A$3:$E$10,2,0)

La formula sopra ha quattro parametri:

• "Brad": - Questo è il valore di ricerca.
• $A$3:$E$10 - Questo è l'intervallo di celle che stiamo guardando.Ricorda che Excel cerca i valori di ricerca nella colonna più a sinistra.In questo esempio, cerca il nome Brad in A3:A10, che è la colonna più a sinistra dell'array specificato.
• 2 – Una volta che la funzione trova il nome di Brad, va alla seconda colonna dell'array e restituisce il valore nella stessa riga di Brad.Un valore di 2 qui significa che stiamo cercando i punteggi dalla seconda colonna dell'array specificato.
• 0 – Questo dice alla funzione VLOOKUP di cercare solo le corrispondenze esatte.

Ecco come funziona la formula CERCA.VERT nell'esempio sopra.

Innanzitutto, cerca il valore Brad nella colonna più a sinistra.Cerca il valore nella cella A6 dall'alto verso il basso.

Una volta trovato il valore, si sposta a destra nella seconda colonna e ne cattura il valore.

Puoi usare la stessa costruzione della formula per ottenere il punteggio di chiunque in qualsiasi materia.

Ad esempio, per trovare il punteggio di Maria in Chimica, usa la seguente formula CERCA.VERT:

= CERCA.VERT("Maria",$A$3:$E$10,4,0)

Nell'esempio sopra, il valore di ricerca (nome dello studente) viene inserito tra virgolette.Puoi anche utilizzare riferimenti di cella che contengono valori di ricerca.

Il vantaggio dell'utilizzo dei riferimenti di cella è che rende dinamica la formula.

Ad esempio, se hai una cella con il nome di uno studente e stai ricevendo voti per la matematica, quando cambi il nome dello studente, i risultati si aggiornano automaticamente (come mostrato di seguito):

Se si immette un valore di ricerca che non si trova nella colonna più a sinistra, viene restituito un errore #N/D.

Esempio 2 - Ricerca bidirezionale

Nell'esempio 1 sopra, abbiamo codificato i valori delle colonne.Pertanto, la formula restituirà sempre il punteggio di Math perché utilizziamo 2 come numero di indice della colonna.

Ma cosa succede se si desidera rendere dinamici sia il valore VLOOKUP che il numero di indice della colonna.Ad esempio, come mostrato di seguito, puoi modificare il nome dello studente o il nome della materia e la formula VLOOKUP otterrà il punteggio corretto.Ecco un esempio di una formula VLOOKUP bidirezionale.

Ecco un esempio di una funzione VLOOKUP bidirezionale.

Per rendere questa formula di ricerca bidirezionale, devi anche rendere dinamica la colonna.Quindi quando l'utente cambia argomento, la formula seleziona automaticamente la colonna corretta (2 per la matematica, 3 per la fisica, ecc.).

Per fare ciò, è necessario utilizzare la funzione CONFRONTA come parametro di colonna.

Ecco la formula VLOOKUP che farà questo:

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

La formula precedente utilizza MATCH(H3,$A$2:$E$2,0) come numero di colonna.La funzione CONFRONTA prende il nome dell'argomento come valore di ricerca (in H3) e restituisce la sua posizione in A2:E2.Quindi, se usi Math, restituirà 2 perché Math si trova in B2 (che è la seconda cella nell'intervallo di array specificato).

Esempio 3 - Utilizzo del menu a discesa come valore di ricerca

Nell'esempio sopra, dobbiamo inserire i dati manualmente.Questo può richiedere molto tempo e può essere soggetto a errori, soprattutto se hai molti valori di ricerca.

In questo caso, una buona idea sarebbe quella di creare un elenco a discesa di valori di ricerca (in questo caso potrebbe essere il nome dello studente e la materia) e selezionarli semplicemente dall'elenco.

A seconda della selezione, la formula aggiornerà automaticamente il risultato.

Come mostrato di seguito:

Questo è un ottimo componente del dashboard perché puoi avere un enorme set di dati di centinaia di studenti nel back-end, ma gli utenti finali (ad esempio gli insegnanti) possono semplicemente scendere dal file .

Come faccio a fare questo:

La formula CERCA.VERT utilizzata in questo esempio è la stessa utilizzata nell'esempio 2.

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

I valori di ricerca sono stati convertiti in elenchi a discesa.

Di seguito sono riportati i passaggi per creare un elenco a discesa:

• Seleziona la cella che necessita di un elenco a discesa.In questo esempio, in G4, abbiamo bisogno del nome dello studente.
• Vai a Dati -> Strumenti dati -> Convalida dati.
• Nella scheda Impostazioni della finestra di dialogo Convalida dati, selezionare Elenco dall'elenco a discesa Consenti.
• In Sorgente, seleziona $A$3:$A$10
• Fare clic su OK.

Ora vedrai l'elenco a discesa nella cella G4.Allo stesso modo, puoi crearne uno in H3 per un tema.

Esempio 4 - Ricerca a tre vie

Che cos'è una ricerca a tre vie?

Nell'esempio 2, abbiamo utilizzato una tabella di ricerca contenente i punteggi degli studenti in diverse materie.Questo è un esempio di ricerca bidirezionale perché utilizziamo due variabili per ottenere i punteggi (nome dello studente e nome del soggetto).

Ora, diciamo tra un anno, uno studente sosterrà tre diversi livelli di esami, esami unitari, esami intermedi e esami finali (questo è l'esame dei miei giorni da studente).

Una ricerca a tre vie sarà in grado di ottenere il punteggio di uno studente per una materia specifica da un livello di esame specifico.

Come mostrato di seguito:

Nell'esempio precedente, la funzione CERCA.VERT può cercare e restituire i voti degli studenti specificati nelle materie specificate in tre tabelle diverse (test unitari, intermedi e finali).

Ecco la formula utilizzata nella cella H4:

=VLOOKUP(G4,CHOOSE(IF(H2="Unit Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0),0)

Questa formula utilizza la funzione SCEGLI per garantire che venga fatto riferimento alla tabella corretta.Analizziamo la parte SCEGLI della formula:

CHOOSE(IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23)

Il primo argomento della formula è IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)), che esamina la cella H2 e vede il livello di test di riferimento.Se si tratta di un test unitario, viene restituito $A$3:$E$7, che contiene il punteggio del test unitario.Restituisce $A$11:$E$15 se si tratta di un esame intermedio, altrimenti restituisce $A$19:$E$23.

In questo modo l'array di tabelle VLOOKUP diventa dinamico, rendendolo una ricerca a tre vie.

Esempio 5 - Ottieni l'ultimo valore da un elenco

Puoi creare una formula CERCA.VERT per ottenere l'ultimo valore in un elenco.

Il numero positivo più grande che puoi utilizzare in Excel è 9.99999999999999 307 + . Ciò significa anche che il numero di ricerca più grande nel numero VLOOKUP è lo stesso.

Non credo che avrai mai bisogno di calcoli che coinvolgono numeri così grandi.Questo è esattamente ciò che possiamo usare per ottenere l'ultimo numero nell'elenco.

Supponiamo di avere un set di dati come il seguente(in A1:A14)e si desidera ottenere l'ultimo numero nell'elenco.

Ecco la formula che puoi utilizzare:

= CERCA.VERT(9.99999999999999E+307,$A$1:$A$14,VERO)

Nota che la formula sopra utilizza un VLOOKUP di corrispondenza approssimativa  (Nota il VERO alla fine della formula, non FALSO o 0).Inoltre, nota che questa formula CERCA.VERT funziona senza ordinare l'elenco.

Ecco come funziona la funzione approssimativa di VLOOKUP.Esegue la scansione della colonna più a sinistra dall'alto verso il basso.

• Questo valore viene restituito se viene trovata una corrispondenza esatta.
• Se trova un valore superiore al valore di ricerca, restituisce il valore nella cella sopra di esso.
• Se il valore di ricerca è maggiore di tutti i valori nell'elenco, viene restituito l'ultimo valore.

Nell'esempio sopra, il terzo caso è al lavoro.

a causa di9.99999999999999 307 +è il numero più grande che può essere utilizzato in Excel, quindi quando viene utilizzato come valore di ricerca, restituisce l'ultimo numero nell'elenco.

Allo stesso modo, puoi anche usarlo per restituire l'ultimo elemento di testo nell'elenco.Ecco la formula che può farlo:

=CERCA.VERT("zzz",$A$1:$A$8,1, TRUE )

Segui la stessa logica.Excel esamina tutti i nomi e poiché zzz è considerato più grande di qualsiasi nome/testo che inizia con una lettera prima di zzz, restituirà l'ultimo elemento nell'elenco.

Esempio 6 - Ricerca parziale utilizzando caratteri jolly e VLOOKUP

I caratteri jolly di Excel sono utili in molte situazioni.

È questa pozione magica che conferisce superpoteri alla tua ricetta.

È necessaria una ricerca parziale quando è necessario cercare un valore in un elenco e non esiste una corrispondenza esatta.

Ad esempio, supponiamo di avere un set di dati come il seguente e di voler trovare la società ABC in un elenco, ma l'elenco contiene ABC Ltd anziché ABC.

Non è possibile utilizzare ABC come valore di ricerca perché non esiste una corrispondenza esatta nella colonna A.Le corrispondenze approssimative possono anche portare a risultati errati e l'elenco deve essere ordinato in ordine crescente.

Tuttavia, puoi utilizzare i caratteri jolly nella funzione CERCA.VERT per ottenere corrispondenze.

Immettere la seguente formula nella cella D2 e ​​trascinarla su altre celle:

=CERCA.VERT("*"&C2&"*",$A$2:$A$8,1,FALSO)

Come funziona questa formula?

Nella formula precedente, invece di utilizzare il valore di ricerca così com'è, è circondato da asterischi jolly (*) - "*"&C2&"*"

Gli asterischi sono caratteri jolly in Excel che possono rappresentare un numero qualsiasi di caratteri.

Utilizzare asterischi attorno al valore di ricerca per indicare a Excel che deve trovare qualsiasi testo che contenga la parola in C2.Può avere un numero qualsiasi di caratteri prima o dopo il testo in C2.

Ad esempio, la cella C2 contiene ABC, quindi la funzione CERCA.VERT esamina i nomi in A2:A8 e cerca ABC.Trova una corrispondenza nella cella A2 perché contiene ABC di ABC Ltd. Non importa se ci sono caratteri a sinistra oa destra di ABC.Sarà considerata una corrispondenza finché non è presente ABC nella stringa di testo.

Nota: la funzione CERCA.VERT restituisce sempre il primo valore corrispondente e interrompe ulteriori ricerche.Quindi se c'è ABC nella lista Ltd.e ABC Corporation, restituirà il primo e ignorerà il resto.

Esempio 7 - CERCA.VERT restituisce un errore nonostante la corrispondenza del valore di ricerca

Ti farà impazzire quando vedrai che esiste un valore di ricerca corrispondente e la funzione CERCA.VERT restituisce un errore.

Ad esempio, nel caso seguente, c'è una corrispondenza (Matt), ma la funzione VLOOKUP restituisce comunque un errore.

Ora, mentre possiamo vedere che c'è una corrispondenza, quello che non possiamo vedere ad occhio nudo è che potrebbero esserci spazi iniziali o finali.Se hai questi spazi extra prima, dopo o tra il valore di ricerca, non è una corrispondenza esatta.

Questo di solito accade quando si importano dati da un database o si ottengono dati da qualcun altro.Questi spazi iniziali/finali hanno la tendenza a insinuarsi.

La soluzione qui è la funzione TRIM.Rimuove eventuali spazi iniziali o finali o spazi extra tra le parole.

Ecco la formula che ti darà il risultato corretto.

= CERCA.VERT("Matt",TRIM($A$2:$A$9),1,0)

Poiché si tratta di una formula di matrice, utilizzare Ctrl+Maiusc+Invio invece di Invio.

Un altro approccio potrebbe essere quello di elaborare prima l'array di ricerca con la funzione TRIM per assicurarsi che tutti gli spazi extra siano spariti, quindi utilizzare la funzione VLOOKUP come al solito.

Esempio 8 - Esecuzione di una ricerca con distinzione tra maiuscole e minuscole

Per impostazione predefinita, i valori di ricerca nella funzione CERCA.VERT non fanno distinzione tra maiuscole e minuscole.Ad esempio, se il valore di ricerca è MATT, opaco o Matt, è lo stesso per la funzione CERCA.VERT.Restituisce il primo valore corrispondente indipendentemente dal caso.

Ma se si desidera una ricerca con distinzione tra maiuscole e minuscole, è necessario utilizzare la funzione ESATTO e la funzione CERCA.VERT.

Questo è un esempio:

Come puoi vedere, le tre celle hanno lo stesso nome (in A2, A4 e A5) ma con lettere maiuscole diverse.Sulla destra, abbiamo tre nomi (Matt, MATT e matt) e i loro punteggi in matematica.

La funzione VLOOKUP non è attualmente attrezzata per gestire i valori di ricerca con distinzione tra maiuscole e minuscole.Nell'esempio sopra, restituisce sempre 38, che è il punteggio di Matt in A2.

Per fare distinzione tra maiuscole e minuscole, è necessario utilizzare una colonna helper (come mostrato di seguito):

Per ottenere il valore nella colonna della guida, utilizzare la funzione =ROW().Otterrà solo il numero di riga nella cella.

Una volta che hai la colonna di supporto, ecco la formula che fornisce un risultato di ricerca con distinzione tra maiuscole e minuscole.

=VLOOKUP(MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))),$B$2:$C$9,2,0)

Ora analizziamolo e capiamo cosa fa:

• EXACT(E2,$A$2:$A$9) – Questa parte confronta il valore di ricerca in E2 con tutti i valori in A2:A9.Restituisce un array di VERO/FALSO, dove viene restituito VERO in caso di corrispondenza esatta.In questo caso restituirà il seguente array: {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
• EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9) - questa parte moltiplica l'array VERO/FALSO con il numero di riga. Se è VERO, darà il numero di riga , altrimenti restituisce 0. In questo caso restituisce {2;0;0;0;0;0;0;0}.
• MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))) – 這部分返回數字數組中的最大值。In questo caso restituirà 2 (che è il numero esatto di riga).
• Ora usiamo semplicemente questo numero come valore di ricerca e utilizziamo l'array di ricerca come B2:C9

Nota: poiché si tratta di una formula di matrice, utilizzare Ctrl + Maiusc + Invio invece di digitare.

Esempio 9 - Utilizzo di VLOOKUP con più condizioni

La funzione CERCA.VERT di Excel, nella sua forma di base, cerca un valore di ricerca e restituisce il valore corrispondente da una riga specificata.

Ma di solito VLOOKUP è necessario in Excel con più criteri.

Supponiamo di avere un dato contenente i nomi degli studenti, i tipi di test e i punteggi di matematica (mostrati di seguito):

L'uso della funzione CERCA.VERT per ottenere il punteggio di matematica di ogni studente al rispettivo livello di test può essere una sfida.

Ad esempio, se si tenta di utilizzare CERCA.VERT con Matt come valore di ricerca, restituirà sempre 91, il punteggio per la prima occorrenza di Matt nell'elenco.Per ottenere il punteggio Matt per ogni tipo di esame (unità, intermedio e finale), è necessario creare un valore di ricerca univoco.

Questo può essere fatto usando le colonne di supporto.Il primo passaggio consiste nell'inserire una colonna di supporto a sinistra della frazione.

Ora, per creare un qualificatore univoco per ogni istanza di un nome, usa la seguente formula in C2: =A2&”|”&B2

Copia questa formula in tutte le celle nella colonna helper.Questo creerà un valore di ricerca univoco per ogni istanza del nome (come mostrato di seguito):

Ora, mentre ci sono nomi duplicati, quando i nomi sono combinati con i livelli di esame, non ci sono duplicati.

Questo è facile perché ora puoi usare il valore della colonna helper come valore di ricerca.

Questa è la formula che ti dà il risultato in G3:I8.

= CERCA.VERT($F3&"|"&G$2,$C$2:$D$19,2,0)

Qui combiniamo il nome dello studente e il livello dell'esame per ottenere il valore di ricerca, utilizziamo questo valore di ricerca per controllare nella colonna dell'helper i record corrispondenti.

Nota: nell'esempio sopra, abbiamo usato | Utilizzato come delimitatore quando si aggiunge testo a colonne ausiliarie.In alcuni casi molto rari (ma possibili), potresti avere due criteri diversi, ma combinarli finirà per dare lo stesso risultato.Questo è un esempio:

Nota che mentre A2 e A3 sono diversi e B2 e B3 sono diversi, la combinazione è in definitiva la stessa.Tuttavia, se usi i delimitatori, anche la combinazione sarà diversa (D2 e D3).

Ecco un tutorial su come utilizzare VLOOKUP con più condizioni senza utilizzare colonne di supporto.

Esempio 10 - Gestione degli errori quando si utilizza la funzione CERCA.VERT

La funzione CERCA.VERT di Excel restituisce un errore quando non è possibile trovare il valore di ricerca specificato.Se VLOOKUP non riesce a trovare un valore, probabilmente non vuoi che brutti valori di errore interferiscano con l'estetica dei tuoi dati.

Puoi rimuovere facilmente i valori di errore con testo completo di qualsiasi significato, come "Non disponibile" o "Non trovato".

Ad esempio, nell'esempio seguente, quando si tenta di trovare il punteggio di Brad nell'elenco, viene restituito un errore perché il nome di Brad non è nell'elenco.

Per rimuovere questo errore e sostituirlo con qualcosa di significativo, avvolgi la funzione CERCA.VERT in una funzione SE ERRORE.

Ecco la formula:

=SEERRORE(VLOOKUP(D2,$A$2:$B$7,2,0),"Non trovato")

La funzione IFERROR verifica se il valore restituito dal primo argomento (in questo caso, la funzione VLOOKUP) è un errore.Se non è un errore, restituisci il valore tramite la funzione CERCA.VERT, altrimenti restituisci Non trovato.

La funzione SEERRORE è disponibile da Excel 2007.Se stai utilizzando una versione precedente, utilizza le seguenti funzioni:

=IF(ISERROR(VLOOKUP(D2,$A$2:$B$7,2,0)),"Not Found",VLOOKUP(D2,$A$2:$B$7,2,0))

Questo è tutto per questo tutorial VLOOKUP.

Sto cercando di presentare l'esempio principale dell'utilizzo della funzione Vlookup in Excel.Fammi sapere nella sezione commenti se desideri vedere altri esempi aggiunti a questo elenco.

Funzioni di Excel correlate:

• Excel funzione CERCA.
• Funzione XLOOKUP di Excel
• Funzione di indice di Excel.
• Funzione indiretta di Excel.
• Funzione di corrispondenza di Excel.
• Funzione di offset di Excel.