La funzione VLOOKUP è una delle funzioni più popolari in Excel. Questo tutorial introdurrà come utilizzare la funzione VLOOKUP in Excel con dozzine di esempi di base e avanzati passo dopo passo.

Introduzione della funzione CERCA.VERT - Sintassi e argomenti

In Excel, la funzione CERCA.VERT è una funzione potente per la maggior parte degli utenti di Excel, ti consente di cercare un valore nella parte più a sinistra dell'intervallo di dati e restituire un valore corrispondente nella stessa riga da una colonna specificata come mostrato nella seguente schermata .

La sintassi della funzione CERCA.VERT:

Argomenti:

Note sulle funzioni:

Esempi di CERCA.VERT di base

In questa sezione parleremo di alcune formule di Vlookup che hai usato frequentemente.

2.1 Corrispondenza esatta e corrispondenza approssimativa CERCA.VERT

 2.1.1 Eseguire una corrispondenza esatta CERCA.VERT

Normalmente, se stai cercando una corrispondenza esatta con la funzione VLOOKUP, devi solo usare FALSE come ultimo argomento.

Ad esempio, per ottenere i punteggi matematici corrispondenti in base ai numeri ID specifici, eseguire le seguenti operazioni:

Copia e incolla la formula seguente in una cella vuota (qui seleziono G2) e premi entrare tasto per ottenere il risultato:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

Come funziona la formula CERCA.VERT?

Innanzitutto, cerca l'ID - C1005 nella colonna più a sinistra della tabella. Va dall'alto verso il basso e trova il valore nella cella A6.

Non appena trova il valore, va a destra nella terza colonna ed estrae il valore in essa.

Quindi, otterrai il risultato come mostrato nell'immagine sottostante:

 2.1.2 Eseguire una corrispondenza approssimativa CERCA.VERT

La corrispondenza approssimativa è utile per la ricerca di valori tra intervalli di dati. Se la corrispondenza esatta non viene trovata, il VLOOKUP approssimativo restituirà il valore più grande che è inferiore al valore di ricerca.

Ad esempio, se disponi del seguente intervallo di dati e gli ordini specificati non sono nella colonna Ordini, come ottenere lo sconto più vicino nella colonna B?

Passaggio 1: applica la formula CERCA.VERT e riempila in altre celle

Copia e incolla la seguente formula in una cella in cui desideri inserire il risultato, quindi trascina verso il basso la maniglia di riempimento per applicare questa formula ad altre celle.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Risultato:

Ora otterrai le corrispondenze approssimative in base ai valori indicati, vedi screenshot:

2.2 Eseguire una CERCA.VERT con distinzione tra maiuscole e minuscole in Excel

Per impostazione predefinita, la funzione CERCA.VERT esegue una ricerca senza distinzione tra maiuscole e minuscole, il che significa che tratta i caratteri minuscoli e maiuscoli come identici. A volte, potrebbe essere necessario eseguire una ricerca con distinzione tra maiuscole e minuscole in Excel, la normale funzione CERCA.VERT potrebbe non risolverlo. In questo caso è possibile utilizzare funzioni alternative come INDICE e CONFRONTA con la funzione ESATTO oppure le funzioni CERCA ed ESATTO.

Ad esempio, ho il seguente intervallo di dati la cui colonna ID contiene una stringa di testo con lettere maiuscole o minuscole, ora voglio restituire il punteggio matematico corrispondente del numero ID specificato.

Passaggio 1: applica una formula qualsiasi e riempila con altre celle

Si prega di copiare e incollare una delle formule seguenti in una cella vuota in cui si desidera ottenere il risultato. Quindi, seleziona la cella della formula, trascina il quadratino di riempimento fino alle celle in cui desideri riempire questa formula.

Formula 1: Dopo aver incollato la formula, premere CTRL + MAIUSC + INVIO chiavi.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Formula 2: Dopo aver incollato la formula, premere entrare chiave.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Risultato:

Quindi otterrai i risultati corretti di cui hai bisogno. Vedi schermata:

2.3 CERCA.VERT valori da destra a sinistra in Excel

La funzione VLOOKUP cerca sempre un valore nella colonna più a sinistra di un intervallo di dati e restituisce il valore corrispondente da una colonna a destra. Se si desidera eseguire una CERCA.VERT inversa, il che significa cercare un valore specifico nella colonna di destra e restituire il valore corrispondente nella colonna di sinistra come mostrato nell'immagine sottostante:

Fare clic per conoscere i dettagli passo dopo passo su questa attività ...

2.4 VLOOKUP il secondo, l'ennesimo o l'ultimo valore corrispondente in Excel

Normalmente, se vengono trovati più valori corrispondenti quando si utilizza la funzione Vlookup, verrà restituito solo il primo record corrispondente. In questa sezione parlerò di come ottenere il secondo, l'ennesimo o l'ultimo valore corrispondente in un intervallo di dati.

 2.4.1 CERCA.VERT e restituisce il secondo o l'ennesimo valore corrispondente

Supponiamo di avere un elenco di nomi nella colonna A, il corso di formazione che hanno acquistato nella colonna B. Ora, stai cercando di trovare il secondo o l'ennesimo corso di formazione acquistato dal cliente specificato. Vedi schermata:

Qui, la funzione VLOOKUP potrebbe non risolvere direttamente questo compito. Ma puoi usare la funzione INDICE come alternativa.

Passaggio 1: applica e riempi la formula su altre celle

Ad esempio, per ottenere il secondo valore corrispondente in base ai criteri indicati, applica la seguente formula in una cella vuota e premi CTRL + MAIUSC + INVIO chiavi insieme per ottenere il primo risultato. Quindi, seleziona la cella della formula, trascina la maniglia di riempimento verso il basso sulle celle in cui desideri riempire questa formula.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Risultato:

Ora, tutti i secondi valori corrispondenti basati sui nomi dati sono stati visualizzati contemporaneamente.

 2.4.2 CERCA.VERT e restituisce l'ultimo valore corrispondente

Se si desidera visualizzare e restituire l'ultimo valore corrispondente come mostrato nell'immagine sottostante, questo CERCA.VERT e restituisce l'ultimo valore corrispondente tutorial può aiutarti a ottenere l'ultimo valore corrispondente nei dettagli.

2.5 CERCA.VERT che corrisponde a valori tra due valori o date dati

A volte, potresti voler cercare valori tra due valori o date e restituire i risultati corrispondenti come mostrato nello screenshot qui sotto. In tal caso, puoi utilizzare la funzione CERCA invece della funzione CERCA.VERT con una tabella ordinata.

 2.5.1 VLOOKUP che corrisponde a valori tra due dati valori o date con formula

Passaggio 1: disporre i dati e applicare la seguente formula

La tua tabella originale dovrebbe essere un intervallo di dati ordinato. E poi, copia o inserisci la seguente formula in una cella vuota. Quindi, trascina il quadratino di riempimento per riempire questa formula su altre celle di cui hai bisogno.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Risultato:

E ora otterrai tutti i record corrispondenti in base al valore specificato, vedi screenshot:

 2.5.2 VLOOKUP che corrisponde a valori tra due dati valori o date con una funzione utile

Se trovi difficile ricordare e comprendere la formula di cui sopra, qui presenterò uno strumento semplice: Kutools for Excel, Con la sua RICERCA tra due valori funzione, puoi restituire facilmente l'articolo corrispondente in base al valore o alla data specifici tra due valori o date.

1. Clicchi Kutools > Super CERCA > RICERCA tra due valori per attivare questa funzionalità.
2. Quindi specifica le operazioni dalla finestra di dialogo in base ai tuoi dati.

2.6 Utilizzo di caratteri jolly per corrispondenze parziali nella funzione CERCA.VERT

In Excel, i caratteri jolly possono essere utilizzati all'interno della funzione CERCA.VERT, che consente di eseguire una corrispondenza parziale su un valore di ricerca. Ad esempio, puoi utilizzare CERCA.VERT per restituire un valore corrispondente da una tabella basata su una parte di un valore di ricerca.

Supponendo di avere un intervallo di dati come mostrato nell'immagine sottostante, ora voglio estrarre il punteggio in base al nome (non al nome completo). Come potrebbe risolvere questa attività in Excel?

Passaggio 1: applica e riempi la formula su altre celle

Copia o inserisci la seguente formula in una cella vuota, quindi trascina il quadratino di riempimento per riempire questa formula in altre celle di cui hai bisogno:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Risultato:

E tutti i punteggi corrispondenti sono stati restituiti come mostrato nell'immagine sottostante:

2.7 VLOOKUP valori da un altro foglio di lavoro

Di solito, potresti dover lavorare con più di un foglio di lavoro, la funzione VLOOKUP può essere utilizzata per cercare i dati da un altro foglio come su un foglio di lavoro.

Ad esempio, hai due fogli di lavoro come mostrato nell'immagine sottostante, per cercare e restituire i dati corrispondenti dal foglio di lavoro specificato, esegui i seguenti passaggi:

Passaggio 1: applica e riempi la formula su altre celle

Inserisci o copia la formula seguente in una cella vuota in cui desideri ottenere gli elementi corrispondenti. Quindi, trascina il quadratino di riempimento verso il basso sulle celle a cui desideri applicare questa formula.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Risultato:

Otterrai i risultati corrispondenti di cui hai bisogno, vedi screenshot:

2.8 VLOOKUP valori da un'altra cartella di lavoro

Questa sezione parlerà della ricerca e restituirà i valori corrispondenti da una cartella di lavoro diversa utilizzando la funzione CERCA.VERT.

Ad esempio, supponiamo che tu abbia due cartelle di lavoro. La prima cartella di lavoro contiene un elenco di prodotti e i rispettivi costi. Nella seconda cartella di lavoro, si desidera estrarre il costo corrispondente per ciascun articolo del prodotto come mostrato nell'immagine sottostante.

Passaggio 1: applicare e compilare la formula

Apri entrambe le cartelle di lavoro che desideri utilizzare, quindi applica la seguente formula in una cella in cui desideri inserire il risultato nella seconda cartella di lavoro. Quindi, trascina e copia questa formula nelle altre celle di cui hai bisogno

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Risultato:

2.9 Restituisce testo vuoto o specifico invece di 0 o errore #N/D

Di solito, quando si utilizza la funzione VLOOKUP per restituire un valore corrispondente, se la cella corrispondente è vuota, restituirà 0. E se il valore corrispondente non viene trovato, si otterrà un valore di errore di #N/D come mostrato nel schermata qui sotto. Se vuoi visualizzare una cella vuota o un valore specifico invece di 0 o #N/D, questo CERCA.VERT per restituire un valore vuoto o specifico invece di 0 o N/D tutorial potrebbe farti un favore.

Esempi di CERCA.VERT avanzata

3.1 Ricerca bidirezionale (CERCA.VERT in riga e colonna)

A volte, potrebbe essere necessario eseguire una ricerca bidimensionale, il che significa cercare un valore sia nella riga che nella colonna contemporaneamente. Ad esempio, se disponi del seguente intervallo di dati e potresti dover ottenere il valore per un particolare prodotto in un trimestre specifico. Questa sezione introdurrà una formula per gestire questo lavoro in Excel.

In Excel è possibile utilizzare una combinazione di funzioni CERCA.VERT e CONFRONTA per eseguire una ricerca bidirezionale.

Applicare la seguente formula in una cella vuota, quindi premere entrare chiave per ottenere il risultato.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3.2 VLOOKUP valore di corrispondenza basato su due o più criteri

È facile per te cercare il valore corrispondente in base a un criterio, ma se hai due o più criteri, cosa puoi fare?

 3.2.1 VLOOKUP valore di corrispondenza basato su due o più criteri con formule

In questo caso, le funzioni CERCA o CONFRONTA e INDICE in Excel possono aiutarti a risolvere questo lavoro in modo rapido e semplice.

Ad esempio, ho la tabella dati di seguito, per restituire il prezzo abbinato in base al prodotto e alle dimensioni specifici, le seguenti formule potrebbero aiutarti.

Passaggio 1: applicare una formula qualsiasi

Formula 1: Dopo aver incollato la formula, premere entrare chiave.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Formula 2: Dopo aver incollato la formula, premere CTRL + MAIUSC + INVIO chiavi.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Risultato:

 3.2.2 VLOOKUP valore di corrispondenza basato su due o più criteri con una funzione intelligente

Può essere difficile ricordare le formule complesse di cui sopra che devono essere applicate ripetutamente, il che può rallentare l'efficienza del tuo lavoro. Tuttavia, Kutools for Excel offre Ricerca multi-condizione funzionalità che consente di restituire il risultato corrispondente in base a una o più condizioni con solo pochi clic.

1. Clicchi Kutools > Super CERCA > Ricerca multi-condizione per attivare questa funzionalità.
2. Quindi specifica le operazioni dalla finestra di dialogo in base ai tuoi dati.

3.3 CERCA.VERT per restituire più valori con uno o più criteri

In Excel, la funzione CERCA.VERT cerca un valore e restituisce solo il primo valore corrispondente se sono stati trovati più valori corrispondenti. A volte, potresti voler restituire tutti i valori corrispondenti in una riga, in una colonna o in una singola cella. Questa sezione parlerà di come restituire più valori corrispondenti con una o più condizioni in una cartella di lavoro.

 3.3.1 VLOOKUP tutti i valori corrispondenti basati su una o più condizioni in orizzontale

Supponendo che tu abbia una tabella di dati che contiene paese, città e nomi nell'intervallo A1: C14, e ora, vuoi restituire tutti i nomi in orizzontale che provengono da "US" come mostrato nell'immagine sottostante. Per risolvere questo compito, per favore clicca qui per ottenere il risultato passo dopo passo.

 3.3.2 CERCA.VERT verticalmente tutti i valori corrispondenti basati su una o più condizioni

Se hai bisogno di Vlookup e restituisci tutti i valori corrispondenti verticalmente in base a criteri specifici come mostrato nell'immagine sottostante, fare clic qui per ottenere la soluzione in dettaglio.

 3.3.3 VLOOKUP tutti i valori corrispondenti basati su una o più condizioni in una singola cella

Se vuoi Vlookup e restituire più valori corrispondenti in una singola cella con separatore specificato, la nuova funzione di TEXTJOIN può aiutarti a risolvere questo lavoro in modo rapido e semplice.

3.4 CERCA.VERT per restituire l'intera riga di una cella corrispondente

In questa sezione parlerò di come recuperare l'intera riga di un valore corrispondente utilizzando la funzione CERCA.VERT.

Passaggio 1: applicare e compilare la seguente formula

Si prega di copiare o digitare la formula seguente in una cella vuota in cui si desidera visualizzare il risultato e premere entrare chiave per ottenere il primo valore. Quindi, trascina la cella della formula verso destra finché non vengono visualizzati i dati dell'intera riga.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Risultato:

Ora puoi vedere che vengono restituiti i dati dell'intera riga. Vedi schermata:

3.5 VLOOKUP annidato in Excel

A volte, potrebbe essere necessario cercare valori interconnessi tra più tabelle. In questo caso, puoi nidificare più funzioni VLOOKUP insieme per ottenere il valore finale.

Ad esempio, ho un foglio di lavoro che contiene due tabelle separate. La prima tabella elenca tutti i nomi dei prodotti insieme al venditore corrispondente. La seconda tabella elenca le vendite totali di ciascun venditore. Ora, se vuoi trovare le vendite di ciascun prodotto, come mostrato nello screenshot seguente, puoi nidificare la funzione CERCA.VERT per eseguire questa operazione.

La formula generica per la funzione CERCA.VERT nidificata è:

Passaggio 1: applicare e compilare la seguente formula

Si prega di applicare la seguente formula in una cella vuota, quindi trascinare il quadratino di riempimento sulle celle in cui si desidera applicare questa formula.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Risultato:

Ora otterrai il risultato come mostrato nello screenshot seguente:

3.6 Verificare se il valore esiste in base a un elenco di dati in un'altra colonna

La funzione VLOOKUP può anche aiutarti a verificare se i valori esistono in base all'elenco di dati in un'altra colonna. Ad esempio, se si desidera cercare i nomi nella colonna C e restituire semplicemente Sì o No se il nome viene trovato o meno nella colonna A come mostrato nell'immagine sottostante.

Passaggio 1: applicare e compilare la seguente formula

Si prega di applicare la seguente formula in una cella vuota, quindi trascinare il quadratino di riempimento verso il basso sulle celle in cui si desidera riempire questa formula.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Risultato:

E otterrai il risultato di cui hai bisogno, vedi screenshot:

3.7 CERCA.VERT e somma tutti i valori corrispondenti in righe o colonne

Quando si lavora con dati numerici, potrebbe essere necessario estrarre i valori corrispondenti da una tabella e sommare i numeri in più colonne o righe. Questa sezione introdurrà alcune formule che possono aiutarti a svolgere questo compito.

 3.7.1 CERCA.VERT e somma tutti i valori corrispondenti in una o più righe

Supponiamo di avere un elenco di prodotti con vendite per diversi mesi, come mostrato nello screenshot seguente. Ora devi sommare tutti gli ordini in tutti i mesi in base ai prodotti indicati.

Passaggio 1: applicare e compilare la seguente formula

Si prega di copiare o inserire la seguente formula in una cella vuota, quindi premere CTRL + MAIUSC + INVIO chiavi insieme per ottenere il primo risultato. Quindi, trascina verso il basso la maniglia di riempimento per copiare questa formula in altre celle di cui hai bisogno.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Risultato:

Tutti i valori in una riga del primo valore corrispondente sono stati sommati insieme, vedi screenshot:

 3.7.2 CERCA.VERT e somma tutti i valori corrispondenti in una o più colonne

Se vuoi sommare il valore totale per i mesi specifici come mostrato nello screenshot qui sotto. La normale funzione CERCA.VERT potrebbe non aiutarti, qui dovresti applicare insieme le funzioni SOMMA, INDICE e CONFRONTA per creare una formula.

Passaggio 1: applicare la seguente formula

Applica la formula seguente in una cella vuota, quindi trascina verso il basso la maniglia di riempimento per copiare questa formula in altre celle.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Risultato:

Ora, i primi valori corrispondenti basati sul mese specifico in una colonna sono stati sommati insieme, vedi screenshot:

 3.7.3 CERCA.VERT e somma i primi valori corrispondenti o tutti i valori corrispondenti con una potente funzionalità

Forse le formule di cui sopra sono difficili da ricordare, in questo caso, ti consiglierò una funzione potente: Ricerca e somma of Kutools for Excel, con questa funzione, puoi visualizzare e sommare la prima corrispondenza o tutti i valori corrispondenti in righe o colonne nel modo più semplice possibile.

1. Clicchi Kutools > Super CERCA > CERCA e Somma per attivare questa funzionalità.
2. Quindi specificare le operazioni dalla finestra di dialogo in base alle proprie necessità.

 3.7.4 CERCA.VERT e somma tutti i valori corrispondenti sia nelle righe che nelle colonne

Se si desidera sommare i valori quando è necessario abbinare sia la colonna che la riga, ad esempio, per ottenere il valore totale del prodotto Maglione nel mese Mar come mostrato nell'immagine sottostante.

Qui è possibile utilizzare la funzione SUMPRODCT per eseguire questa attività.

Applicare la seguente formula in una cella, quindi premere entrare tasto per ottenere il risultato, vedi screenshot:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3.8 CERCA.VERT per unire due tabelle in base a colonne chiave

Nel tuo lavoro quotidiano, durante l'analisi dei dati, potresti dover raccogliere tutte le informazioni necessarie in un'unica tabella basata su una o più colonne chiave. Per eseguire questa operazione, è possibile utilizzare le funzioni INDICE e CONFRONTA invece della funzione CERCA.VERT.

 3.8.1 CERCA.VERT per unire due tabelle in base a una colonna chiave

Ad esempio, hai due tabelle, la prima tabella contenente i dati dei prodotti e dei nomi e la seconda tabella contiene i dati dei prodotti e degli ordini, ora vuoi combinare queste due tabelle abbinando la colonna del prodotto comune in una tabella.

Passaggio 1: applicare e compilare la seguente formula

Si prega di applicare la seguente formula in una cella vuota. Quindi, trascina il quadratino di riempimento verso il basso sulle celle a cui desideri applicare questa formula

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Risultato:

Ora otterrai una tabella unita con la colonna dell'ordine che si unisce alla prima tabella in base ai dati della colonna chiave.

 3.8.2 CERCA.VERT per unire due tabelle basate su più colonne chiave

Se le due tabelle che vuoi unire hanno più colonne chiave, per unire le tabelle in base a queste colonne comuni, procedi nel seguente modo.

La formula generica è:

Passaggio 1: applicare la seguente formula

Si prega di applicare la formula seguente in una cella vuota in cui si desidera inserire il risultato, quindi premere CTRL + MAIUSC + INVIO tasti insieme per ottenere il primo valore corrispondente, vedi screenshot:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Passaggio 2: riempire la formula in altre celle

Quindi, seleziona la prima cella della formula e trascina il quadratino di riempimento per copiare questa formula in altre celle di cui hai bisogno:

3.9 VLOOKUP che corrisponde ai valori su più fogli di lavoro

Hai mai avuto bisogno di eseguire una CERCA.VERT su più fogli di lavoro in Excel? Ad esempio, se disponi di tre fogli di lavoro con intervalli di dati e desideri recuperare valori specifici in base a criteri da questi fogli, puoi seguire il tutorial passo passo VLOOKUP Valori su più fogli di lavoro per eseguire questa operazione.

I valori corrispondenti CERCA.VERT mantengono la formattazione delle celle

Durante la ricerca di valori corrispondenti, la formattazione originale della cella come il colore del carattere, il colore dello sfondo, il formato dei dati e così via non verrà mantenuta. Per mantenere la cella o la formattazione dei dati, questa sezione introdurrà alcuni trucchi per risolvere i lavori.

4.1 CERCA.VERT corrispondente al valore e mantieni il colore della cella, la formattazione del carattere

Come tutti sappiamo, la normale funzione CERCA.VERT può solo recuperare il valore corrispondente da un altro intervallo di dati. Tuttavia, potrebbero esserci casi in cui si desidera ottenere il valore corrispondente insieme alla formattazione della cella, come il colore di riempimento, il colore del carattere e lo stile del carattere. In questa sezione, discuteremo come recuperare i valori corrispondenti preservando la formattazione di origine in Excel.

Si prega di eseguire i seguenti passaggi per cercare e restituire il valore corrispondente insieme alla formattazione della cella:

Passaggio 1: copia il codice 1 nel modulo Codice foglio

1. Nel foglio di lavoro contiene i dati che vuoi CERCA.VERT, fai clic con il pulsante destro del mouse sulla scheda del foglio e seleziona Visualizza codice dal menu contestuale. Vedi screenshot:
   
2. In aperto Microsoft Visual Basic, Applications Edition finestra, copia il codice VBA sottostante nella finestra del codice.
3. Codice VBA 1: CERCA.VERT per ottenere la formattazione della cella insieme al valore di ricerca

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

Passaggio 2: copia il codice 2 nella finestra del modulo

1. Ancora nella Microsoft Visual Basic, Applications Edition finestra, fare clic inserire > Moduli, quindi copia il codice VBA 2 di seguito nella finestra del modulo.
2. Codice VBA 2: CERCA.VERT per ottenere la formattazione della cella insieme al valore di ricerca

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

Passaggio 3: selezionare l'opzione per VBAproject

1. Dopo aver inserito i codici di cui sopra, quindi fare clic su Strumenti > Riferimenti nel Microsoft Visual Basic, Applications Edition finestra. Quindi controlla il file Runtime di script Microsoft casella di controllo in Riferimenti - VBAProject la finestra di dialogo. Vedi screenshot:
2. Quindi, fare clic OK per chiudere la finestra di dialogo, quindi salvare e chiudere la finestra del codice.

Passaggio 4: digitare la formula per ottenere il risultato

1. Ora, torna al foglio di lavoro, applica la seguente formula. Quindi, trascina verso il basso la maniglia di riempimento per ottenere tutti i risultati insieme alla loro formattazione. Vedi schermata:
   

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4.2 Mantenere il formato della data da un valore restituito VLOOKUP

Quando si utilizza la funzione CERCA.VERT per cercare e restituire un valore con formato data, il risultato restituito potrebbe essere visualizzato come numero. Per mantenere il formato della data nel risultato restituito, è necessario racchiudere la funzione CERCA.VERT all'interno della funzione TESTO.

Passaggio 1: applicare e compilare la seguente formula

Si prega di applicare la formula seguente in una cella vuota. Quindi, trascina la maniglia di riempimento per copiare questa formula in altre celle.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Risultato:

Tutte le date abbinate sono state restituite come mostrato nell'immagine sottostante:

4.3 Restituisce il commento della cella da CERCA.VERT

Hai mai avuto bisogno di recuperare sia i dati della cella corrispondente che il commento associato utilizzando CERCA.VERT in Excel, come mostrato nello screenshot seguente? In tal caso, la funzione definita dall'utente fornita di seguito può aiutarti a svolgere questa attività.

Passaggio 1: copia il codice in un modulo

1. Tenere premuto il tasto ALT + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.
2. Clicchi inserire > Moduli, quindi copia e incolla il codice seguente nella finestra del modulo.
   Codice VBA: Vlookup e restituisce il valore corrispondente con il commento della cella:

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. Quindi salva e chiudi la finestra del codice.

Passaggio 2: digitare la formula per ottenere il risultato

1. Ora inserisci la seguente formula e trascina il quadratino di riempimento per copiare questa formula in altre celle. Restituirà contemporaneamente sia i valori che i commenti corrispondenti, vedi screenshot:
   

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4.4 CERCA.VERT numeri memorizzati come testo

Ad esempio, ho un intervallo di dati in cui il numero ID nella tabella originale è in formato numerico e il numero ID nelle celle di ricerca è memorizzato come testo, potresti riscontrare un errore #N/D quando utilizzi la normale funzione CERCA.VERT. In questo caso, per recuperare le informazioni corrette, è possibile racchiudere le funzioni TESTO e VALORE all'interno della funzione CERCA.VERT. Di seguito è riportata la formula per ottenere ciò:

Passaggio 1: applicare e compilare la seguente formula

Si prega di applicare la seguente formula in una cella vuota, quindi trascinare il quadratino di riempimento verso il basso per copiare questa formula.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Risultato:

Ora otterrai i risultati corretti come mostrato nell'immagine sottostante: