Funzione CERCA.VERT con alcuni esempi di base e avanzati in Excel

In Excel, la funzione CERCA.VERT è una funzione potente per la maggior parte degli utenti di Excel, che viene utilizzata per 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 nell'immagine sottostante . Questo tutorial parla di come utilizzare la funzione CERCA.VERT con alcuni esempi di base e avanzati in Excel.

Introduzione della funzione CERCA.VERT - Sintassi e argomenti

La sintassi della funzione CERCA.VERT:

Argomenti:

Valore di ricerca: Il valore che desideri cercare. Deve essere nella prima colonna dell'intervallo table_array.

Matrice di tabella: L'intervallo di dati o la tabella in cui si trovano la colonna del valore di ricerca e la colonna del valore del risultato.

Col_index_num: Il numero di colonne da cui verrà restituito il valore corrispondente. Inizia con 1 dalla colonna più a sinistra nella matrice della tabella.

Range_lookup: Un valore logico che determina se questa funzione CERCA.VERT restituirà una corrispondenza esatta o approssimativa.

• Corrispondenza approssimativa - 1 / VERO: Se non viene trovata una corrispondenza esatta, la formula cerca la corrispondenza più vicina, il valore più grande che è inferiore al valore di ricerca. In questo caso, è necessario ordinare la colonna di ricerca in ordine crescente.
  = VLOOKUP (lookup_value, table_array, col_index, TRUE)
  = VLOOKUP (lookup_value, table_array, col_index, 1)
• Corrispondenza esatta - 0 / FALSO: Viene utilizzato per cercare un valore esattamente uguale al valore di ricerca. Se non viene trovata una corrispondenza esatta, verrà restituito il valore di errore # N / D.
  = VLOOKUP (lookup_value, table_array, col_index, FALSE)
  = VLOOKUP (lookup_value, table_array, col_index, 0)

Note:

• 1. La funzione Vlookup cerca solo il valore da sinistra a destra.
• 2. Se sono presenti più valori corrispondenti in base al valore di ricerca, verrà restituito solo il primo abbinato utilizzando la funzione Vlookup.
• 3. Restituirà il valore di errore # N / D se il valore di ricerca non può essere trovato.

Esempi di CERCA.VERT di base

1. Fai una corrispondenza esatta con Vlookup e una corrispondenza approssimativa con Vlookup

Fai una corrispondenza esatta con Vlookup in Excel

Normalmente, se stai cercando una corrispondenza esatta con la funzione Vlookup, devi solo usare FALSE nell'ultimo argomento.

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

1. Applica la formula seguente in una cella vuota in cui desideri ottenere il risultato:

2. Quindi, trascina il quadratino di riempimento verso il basso sulle celle in cui desideri riempire questa formula e otterrai i risultati di cui hai bisogno. Vedi screenshot:

Note:

• 1. Nella formula sopra, F2 è il valore a cui vuoi restituire il valore corrispondente, A2: D7 è l'array della tabella, il numero 3 è il numero di colonna da cui viene restituito il valore corrispondente e il FALSO si riferisce alla corrispondenza esatta.
• 2. Se il valore dei criteri non viene trovato nell'intervallo di dati, verrà visualizzato un valore di errore # N / A.

Fai una corrispondenza approssimativa con Vlookup in Excel

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

Ad esempio, se si dispone dei seguenti dati di intervallo, gli ordini specificati non sono nella colonna Ordini, come ottenere lo sconto più vicino nella colonna B?

1. Immettere la seguente formula in una cella in cui si desidera inserire il risultato:

2. Quindi, trascina il quadratino di riempimento verso il basso sulle celle per applicare questa formula e otterrai le corrispondenze approssimative in base ai valori forniti, vedi screenshot:

Note:

• 1. Nella formula sopra, D2 è il valore a cui vuoi restituire le informazioni relative, A2: B9 è l'intervallo di dati, il numero 2 indica il numero di colonna in cui viene restituito il valore corrispondente e il TRUE si riferisce alla corrispondenza approssimativa.
• 2. La corrispondenza approssimativa restituirà il valore più grande che è inferiore al valore di ricerca specifico.
• 3. Per utilizzare la funzione Vlookup per ottenere un valore di corrispondenza approssimativo, è necessario ordinare la colonna più a sinistra dell'intervallo di dati in ordine crescente, altrimenti restituirà un risultato errato.

2. Esegui un Vlookup case sensitive in Excel

Per impostazione predefinita, la funzione Vlookup 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, le funzioni Indice, Abbina ed Esatto o Ricerca ed Esatto possono farti un favore.

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.

Formula 1: utilizzo delle funzioni ESATTO, INDICE, CONFRONTA

1. Inserisci o copia la seguente formula di matrice in una cella vuota in cui desideri ottenere il risultato:

2. Quindi, premere CTRL + MAIUSC + INVIO tasti contemporaneamente per ottenere il primo risultato, quindi selezionare la cella della formula, trascinare il quadratino di riempimento verso il basso sulle celle in cui si desidera riempire questa formula, quindi si otterranno i risultati corretti necessari. Vedi screenshot:

Note:

• 1. Nella formula sopra, A2: A10 è la colonna che contiene i valori specifici in cui vuoi cercare, F2 è il valore di ricerca, C2: C10 è la colonna da cui verrà restituito il risultato.
• 2. Se vengono trovate più corrispondenze, questa formula restituirà sempre la prima corrispondenza.

Formula 2: utilizzo delle funzioni Lookup ed Exact

1. Si prega di applicare la formula seguente in una cella vuota in cui si desidera ottenere il risultato:

2. Quindi, trascina il quadratino di riempimento verso il basso sulle celle in cui desideri copiare questa formula e otterrai i valori corrispondenti con distinzione tra maiuscole e minuscole come mostrato nell'immagine sottostante:

Note:

• 1. Nella formula sopra, A2: A10 è la colonna che contiene i valori specifici in cui vuoi cercare, F2 è il valore di ricerca, C2: C10 è la colonna da cui verrà restituito il risultato.
• 2. Se vengono trovate più corrispondenze, questa formula restituirà sempre l'ultima corrispondenza.

3. Valori di Vlookup 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 un Vlookup inverso, il che significa cercare un valore specifico a 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à ...

4. Visualizza 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 di corrispondenza con la funzione Vlookup.

Vlookup e restituisce il secondo o l'ennesimo valore corrispondente

Supponi di avere un elenco di nomi nella colonna A, il corso di formazione che hanno acquistato nella colonna B e ora stai cercando di trovare il 2 ° o l'ennesimo corso di formazione acquistato da un dato cliente. Vedi screenshot:

1. Per ottenere il secondo o l'ennesimo valore corrispondente in base ai criteri forniti, applica la seguente formula di matrice in una cella vuota:

2. Quindi premere CTRL + MAIUSC + INVIO chiavi insieme per ottenere il primo risultato, quindi selezionare la cella della formula, trascinare il quadratino di riempimento verso il basso sulle celle in cui si desidera riempire questa formula e tutti i secondi valori corrispondenti in base ai nomi dati sono stati visualizzati contemporaneamente, vedere screenshot:

Nota:

• In questa formula, A2: A14 è l'intervallo con tutti i valori per la ricerca, B2: B14 è l'intervallo dei valori corrispondenti da cui vuoi tornare, E2 è il valore di ricerca e l'ultimo numero 2 indica il secondo valore corrispondente che si desidera ottenere, se si desidera restituire il terzo valore corrispondente, è sufficiente modificarlo in 3 come necessario.

Vlookup e restituisce l'ultimo valore corrispondente

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

5. Valori di corrispondenza di Vlookup tra due valori o date dati

A volte, potresti voler cercare valori tra due valori o date e restituire i risultati corrispondenti come mostrato nell'immagine sottostante, in questo caso, puoi usare la funzione CERCA e una tabella ordinata.

Valori di corrispondenza di Vlookup tra due valori o date con formula

1. Innanzitutto, la tabella originale dovrebbe essere un intervallo di dati ordinato. Quindi, copia o inserisci la seguente formula in una cella vuota:

2. Quindi, trascina il quadratino di riempimento per riempire questa formula su altre celle di cui hai bisogno e ora otterrai tutti i record corrispondenti in base al valore dato, vedi screenshot:

Note:

• 1. Nella formula sopra, A2: A6 è l'intervallo di valori più piccoli e B2: B6 è l'intervallo di numeri più grandi nell'intervallo di dati, il E2 è il valore dato che vuoi ottenere il valore corrispondente, C2: C6 sono i dati della colonna da cui si desidera estrarre.
• 2. Questa formula può essere utilizzata anche per estrarre i valori corrispondenti tra due date come mostrato nell'immagine sottostante:
  

Vlookup abbina i valori tra due valori o date con una funzione utile

Se sei doloroso con la formula sopra, qui introdurrò uno strumento facile: Kutools for Excel, Con la sua RICERCA tra due valori è possibile restituire l'elemento corrispondente in base al valore o alla data specifici tra due valori o date senza ricordare alcuna formula.   Fai clic per scaricare Kutools per Excel ora!

6. Utilizzo di caratteri jolly per corrispondenze parziali nella funzione Vlookup

In Excel, i caratteri jolly possono essere utilizzati all'interno della funzione Vlookup, che consente di eseguire una corrispondenza parziale su un valore di ricerca. Ad esempio, puoi utilizzare Vlookup per restituire un valore corrispondente da una tabella in base a 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?

1. La normale funzione Vlookup non funziona correttamente, è necessario unire il testo o il riferimento di cella con un carattere jolly, copiare o inserire la seguente formula in una cella vuota:

2. Quindi, trascina il quadratino di riempimento per riempire questa formula in altre celle di cui hai bisogno e tutti i punteggi corrispondenti sono stati restituiti come mostrato nell'immagine sottostante:

Note:

• 1. Nella formula sopra, E2 & "*" è il valore di ricerca, il valore in E2 della scuola e il * carattere jolly ("*" indica uno o più caratteri), A2: C11 è l'intervallo di ricerca, il numero 3 la colonna che contiene il valore da restituire.
• 2. Vlookup quando si utilizzano i caratteri jolly, è necessario impostare la modalità di corrispondenza esatta con FALSE o 0 per l'ultimo argomento nella funzione Vlookup.

7. Valori di Vlookup da un altro foglio di lavoro

Di solito, potrebbe essere necessario 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:

1. Inserisci o copia la formula seguente in una cella vuota in cui desideri ottenere gli elementi corrispondenti:

2. Quindi, trascina il quadratino di riempimento verso il basso sulle celle a cui desideri applicare questa formula e otterrai i risultati corrispondenti di cui hai bisogno, vedi screenshot:

Nota: Nella formula precedente:

• A2 rappresenta il valore di ricerca;
• Scheda dati è il nome del foglio di lavoro da cui si desidera cercare i dati, (se il nome del foglio contiene spazi o caratteri di punteggiatura, è necessario racchiudere virgolette singole attorno al nome del foglio, altrimenti è possibile utilizzare direttamente il nome del foglio come = CERCA.VERT (A2, Scheda tecnica! $ A $ 2: $ C $ 15,3,0));
• A2: C15 è l'intervallo di dati nel foglio dati in cui stiamo cercando i dati;
• il numero 3 è il numero di colonna che contiene i dati corrispondenti da cui si desidera tornare.

8. Valori di Vlookup 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 Vlookup.

Ad esempio, la prima cartella di lavoro contiene gli elenchi di prodotti e costi, ora si desidera estrarre il costo corrispondente nella seconda cartella di lavoro in base all'articolo del prodotto come mostrato nell'immagine sottostante.

1. Per recuperare il costo relativo da un'altra cartella di lavoro, innanzitutto apri entrambe le cartelle di lavoro che desideri utilizzare, quindi applica la seguente formula in una cella in cui desideri inserire il risultato:

2. Quindi, trascina e copia questa formula su altre celle di cui hai bisogno, vedi screenshot:

Note:

• 1. Nella formula sopra:
  B2 rappresenta il valore di ricerca;
  [Product list.xlsx] Foglio1 è il nome della cartella di lavoro e del foglio di lavoro da cui si desidera cercare i dati, (il riferimento alla cartella di lavoro è racchiuso tra parentesi quadre e l'intera cartella di lavoro + foglio è racchiusa tra virgolette singole);
  A2: B6 è l'intervallo di dati nel foglio di lavoro di un'altra cartella di lavoro in cui stiamo cercando i dati;
  il numero 2 è il numero di colonna che contiene i dati corrispondenti da cui si desidera tornare.
• 2. Se la cartella di lavoro di ricerca è chiusa, il percorso completo del file per la cartella di lavoro di ricerca verrà mostrato nella formula come mostrato nella seguente schermata:
  

9. Vlookup e restituisce un testo vuoto o specifico invece del valore di errore 0 o # N / D

Normalmente, quando si applica la funzione vlookup per restituire il valore corrispondente, se la cella corrispondente è vuota, restituirà 0 e se il valore corrispondente non viene trovato, verrà visualizzato un errore # N / A come mostrato nell'immagine sottostante. Invece di visualizzare il valore 0 o # N / A con cella vuota o altro valore che ti piace, questo Vlookup per restituire un valore vuoto o specifico invece di 0 o N / A tutorial potrebbe farti un favore passo dopo passo.

Esempi di CERCA.VERT avanzata

1. Ricerca bidirezionale con la funzione Vlookup (Vlookup in riga e colonna)

A volte, potrebbe essere necessario eseguire una ricerca bidimensionale, il che significa Vlookup sia nella riga che nella colonna allo stesso tempo. Supponiamo che se disponi del seguente intervallo di dati e ora, potresti dover ottenere il valore per un particolare prodotto in un trimestre specificato. Questa sezione introdurrà alcune formule per gestire questo lavoro in Excel.

Formula 1: utilizzo delle funzioni CERCA.VERT e CONFRONTA

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 Entra chiave per ottenere il risultato.

Nota: Nella formula precedente:

• H1: il valore di ricerca nella colonna su cui si desidera ottenere il valore corrispondente;
• A2: E6: l'intervallo di dati comprese le intestazioni di riga;
• H2: il valore di ricerca nella riga su cui si desidera ottenere il valore corrispondente in base;
• A1: E1: le celle delle intestazioni di colonna.

Formula 2: utilizzo delle funzioni INDICE e CONFRONTA

Ecco un'altra formula che può anche aiutarti a eseguire una ricerca bidimensionale, applica la formula seguente e quindi premi Entra chiave per ottenere il risultato di cui hai bisogno.

Nota: Nella formula precedente:

• B2: E6: l'intervallo di dati da cui restituire l'elemento corrispondente;
• H1: il valore di ricerca nella colonna su cui si desidera ottenere il valore corrispondente;
• A2: A6: le intestazioni di riga contengono il prodotto che vuoi cercare.
• H2: il valore di ricerca nella riga su cui si desidera ottenere il valore corrispondente in base;
• B1: E1: le intestazioni delle colonne contengono il trimestre che vuoi cercare.

2. Valore di corrispondenza di Vlookup basato su due o più criteri

È facile per te cercare il valore di corrispondenza in base a un criterio, ma se hai due o più criteri, cosa puoi fare? 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.

Formula 1: utilizzo della funzione CERCA

Si prega di applicare la formula seguente in una cella in cui si desidera ottenere il risultato, quindi premere entrare chiave, vedere screenshot:

Note:

• 1. Nella formula sopra:
  A2: A12 = G1: significa ricercare i criteri di G1 nel range A2: A12;
  B2: B12 = G2: significa ricercare i criteri di G2 nel range B2: B12;
  D2: D12: l'intervallo a cui si desidera restituire il valore corrispondente.
• 2. Se hai più di due criteri, devi solo unire gli altri criteri nella formula, come ad esempio: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
  

Formula 2: utilizzo delle funzioni INDICE e CONFRONTA

La combinazione di indice e funzione di corrispondenza può anche essere utilizzata per restituire il valore corrispondente in base a più criteri. Copia o inserisci la seguente formula:

Quindi, premi Ctrl + Maiusc + Invio chiavi insieme per ottenere il valore relativo di cui hai bisogno. Vedi screenshot:

Note:

• 1. Nella formula sopra:
  A2: A12 = G1: significa ricercare i criteri di G1 nel range A2: A12;
  B2: B12 = G2: significa ricercare i criteri di G2 nel range B2: B12;
  D2: D12: l'intervallo a cui si desidera restituire il valore corrispondente.
• 2. Se hai più di due criteri, devi solo unire i nuovi criteri nella formula, ad esempio: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
  

3. Vlookup per restituire più valori corrispondenti con una o più condizioni

In Excel, la funzione Vlookup cerca un valore e restituisce il primo valore corrispondente solo 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.

Visualizza tutti i valori corrispondenti in base a una o più condizioni in orizzontale

Vlookup tutti i valori corrispondenti basati su una condizione in orizzontale:

Per Vlookup e restituire tutti i valori corrispondenti in base a un valore specifico in orizzontale, la formula generica è:

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

2. Quindi, seleziona la prima cella della formula e trascina il quadratino di riempimento sulle celle a destra finché non viene visualizzata la cella vuota e tutti gli elementi corrispondenti sono stati estratti, vedi screenshot:

Vlookup tutti i valori corrispondenti in base a due o più condizioni in orizzontale:

Per Vlookup e restituire tutti i valori corrispondenti in base a valori più specifici in orizzontale, la formula generica è:

1. Applicare la seguente formula in una cella vuota in cui si desidera visualizzare il risultato:

2. Quindi, seleziona la cella della formula e trascina il quadratino di riempimento sulle celle di destra finché non viene visualizzata la cella vuota e tutti i valori corrispondenti in base ai criteri specifici sono stati restituiti, vedi screenshot:

Note:: Per più criteri, devi solo unire lookup_value e lookup_range nella formula, ad esempio: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").

Visualizza tutti i valori corrispondenti in base a una o più condizioni verticalmente

Vlookup tutti i valori corrispondenti in base a una condizione in verticale:

Per Vlookup e restituire verticalmente tutti i valori corrispondenti in base a un valore specifico, la formula generica è:

1. Copia o digita la seguente formula in una cella in cui desideri ottenere il risultato, quindi premi CTRL + MAIUSC + INVIO tasti insieme per ottenere il primo valore corrispondente, vedi screenshot:

2. Quindi, seleziona la prima cella della formula e trascina il quadratino di riempimento verso il basso su altre celle finché non viene visualizzata una cella vuota e tutti gli elementi corrispondenti sono stati elencati in una colonna, vedi screenshot:

Vlookup tutti i valori corrispondenti in base a due o più condizioni verticalmente:

Per Vlookup e restituire verticalmente tutti i valori corrispondenti basati su valori più specifici, la formula generica è:

1. Copia la formula seguente in una cella vuota, quindi premi CTRL + MAIUSC + INVIO le chiavi insieme per ottenere il primo elemento abbinato.

2. Quindi trascina la cella della formula verso il basso su altre celle finché non viene visualizzata una cella vuota, vedi screenshot:

Note:: Per più criteri, devi solo unire lookup_value e lookup_range nella formula, ad esempio: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").

Vlookup tutti i valori corrispondenti in base a due 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.

Vlookup tutti i valori corrispondenti in base a una condizione in una singola cella:

Si prega di applicare la seguente semplice formula in una cella vuota, quindi premere CTRL + MAIUSC + INVIO tasti insieme per ottenere il risultato:

Vlookup tutti i valori corrispondenti in base a due o più condizioni in una singola cella:

Per gestire più condizioni quando si restituiscono tutti i valori corrispondenti in una singola cella, applicare la formula seguente, quindi premere CTRL + MAIUSC + INVIO tasti insieme per ottenere il risultato:

Note:

1. La funzione TEXTJOIN è disponibile solo in Excel 2019 e Office 365.

2. Se utilizzi Excel 2016 e versioni precedenti, utilizza la funzione definita dall'utente degli articoli seguenti:

• Vlookup per restituire più valori in una cella in Excel
• Vlookup e restituisce più valori senza duplicati in Excel

4. Vlookup per restituire l'intera o l'intera riga di una cella corrispondente

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

1. Si prega di copiare o digitare la formula seguente in una cella vuota in cui si desidera visualizzare il risultato e premere Entra chiave per ottenere il primo valore.

2. Quindi, trascina la cella della formula sul lato destro finché non vengono visualizzati i dati dell'intera riga, vedi screenshot:

Note:: Nella formula sopra, F2 è il valore di ricerca su cui desideri restituire l'intera riga, A1: D12 è l'intervallo di dati che desideri utilizzare, A1 indica il numero della prima colonna all'interno dell'intervallo di dati.

5. Esegui più funzioni Vlookup (Vlookup annidato) in Excel

A volte, potresti voler cercare valori in più tabelle, se una qualsiasi delle tabelle contiene il valore di ricerca dato come mostrato nell'immagine sottostante, in questo caso, puoi combinare una o più funzioni Vlookup insieme alla funzione SE.ERRORE per eseguire più ricerche.

La formula generica per la funzione Vlookup nidificata è:

Nota:

• valore di ricerca: il valore che stai cercando;
• Table1, Table2, Table3, ...: le tabelle in cui esistono il valore di ricerca e il valore di ritorno;
• col: il numero di colonna nella tabella da cui si desidera restituire il valore corrispondente.
• 0: Viene utilizzato per una corrispondenza esatta.

1. Si prega di applicare la seguente formula in una cella vuota in cui si desidera inserire il risultato:

2. Quindi, trascina il quadratino di riempimento verso il basso sulle celle a cui desideri applicare questa formula e tutti i valori corrispondenti sono stati restituiti come mostrato nell'immagine sottostante:

Note:

• 1. Nella formula sopra, J3 è il valore che stai cercando; A3: B7, D3: E7, G3: H7 sono gli intervalli della tabella in cui esistono il valore di ricerca e il valore restituito; Il numero 2 è il numero di colonna nell'intervallo da cui restituire il valore corrispondente.
• 2. Se non è possibile trovare il valore di ricerca, viene visualizzato un valore di errore. Per sostituire l'errore con un testo leggibile, utilizzare questa formula: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))
  

6. Vlookup per verificare se il valore esiste in base ai dati di un elenco in un'altra colonna

La funzione Vlookup può anche aiutarti a verificare se i valori esistono in base a un altro elenco, ad esempio, se desideri cercare i nomi nella colonna C e restituire Sì o No se il nome viene trovato o meno nella colonna A come nell'immagine sottostante mostrato.

1. Si prega di applicare la seguente formula in una cella vuota:

2. Quindi, trascina il quadratino di riempimento verso il basso sulle celle in cui desideri riempire questa formula e otterrai il risultato di cui hai bisogno, vedi screenshot:

Note:: Nella formula sopra, C2 è il valore di ricerca che vuoi controllare; A2: A10 è l'elenco dell'intervallo da cui verranno trovati i valori di ricerca; il numero 1 è il numero della colonna da cui vuoi recuperare il valore nel tuo intervallo.

7. Visualizza e somma tutti i valori corrispondenti in righe o colonne

Se lavori con dati numerici, a volte, quando estrai i valori corrispondenti dalla tabella, potresti anche dover sommare i numeri in più colonne o righe. Questa sezione introdurrà alcune formule per completare questo lavoro in Excel.

Visualizza e somma tutti i valori corrispondenti in una riga o più righe

Supponiamo che tu abbia un elenco di prodotti con vendite per diversi mesi come mostrato nell'immagine sottostante, ora, devi sommare tutti gli ordini in tutti i mesi in base ai prodotti forniti.

Vlookup e somma i primi valori corrispondenti di seguito:

1. Copia o inserisci la seguente formula in una cella vuota, quindi premi CTRL + MAIUSC + INVIO tasti insieme per ottenere il primo risultato.

2. Quindi, trascina il quadratino di riempimento verso il basso per copiare questa formula in altre celle di cui hai bisogno e tutti i valori in una riga del primo valore corrispondente sono stati sommati insieme, vedi screenshot:

Note:: Nella formula sopra: H2 è la cella contenente il valore che stai cercando; A2: F9 è l'intervallo di dati (senza intestazioni di colonna) che include il valore di ricerca e i valori corrispondenti; Il numero {} 2,3,4,5,6 sono i numeri di colonna utilizzati per calcolare il totale dell'intervallo.

Vlookup e somma tutti i valori corrispondenti in più righe:

La formula sopra può solo sommare i valori in una riga per il primo valore abbinato. Se desideri sommare tutte le corrispondenze in più righe, utilizza la seguente formula, quindi trascina il quadratino di riempimento verso il basso sulle celle a cui desideri applicare questa formula e otterrai il risultato desiderato di cui hai bisogno, vedi screenshot:

Note:: Nella formula sopra: H2 è il valore di ricerca che stai cercando; A2: A9 sono le intestazioni di riga che contengono il valore di ricerca; B2: F9 l'intervallo di dati dei valori numerici che si desidera sommare.

Visualizza e somma tutti i valori corrispondenti in una colonna o più colonne

Vlookup e somma i primi valori corrispondenti in una colonna:

Se vuoi sommare il valore totale per i mesi specifici come mostrato nello screenshot qui sotto.

Applica la formula seguente in una cella vuota, quindi trascina il quadratino di riempimento verso il basso per copiare questa formula su altre celle, ora, i primi valori corrispondenti in base al mese specifico in una colonna sono stati sommati insieme, vedi screenshot:

Note:: Nella formula sopra: H2 è il valore di ricerca che stai cercando; B1: F1 sono le intestazioni di colonna che contengono il valore di ricerca; B2: F9 l'intervallo di dati dei valori numerici che si desidera sommare.

Vlookup e somma tutti i valori corrispondenti in più colonne:

Per visualizzare e sommare tutti i valori corrispondenti in più colonne, è necessario utilizzare la seguente formula:

Note:: Nella formula sopra: H2 è il valore di ricerca che stai cercando; B1: F1 sono le intestazioni di colonna che contengono il valore di ricerca; B2: F9 l'intervallo di dati dei valori numerici che si desidera sommare.

Vlookup e somma i primi valori o tutti i valori corrispondenti con una potente funzionalità

Forse le formule di cui sopra sono difficili da ricordare, in questo caso, consiglierò una funzione utile: Ricerca e somma of Kutools for Excel, con questa funzione puoi ottenere il risultato il più facilmente possibile.    Fai clic per scaricare Kutools per Excel ora!

Vlookup e somma tutti i valori corrispondenti sia in righe che in 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.

Si prega di applicare la seguente formula in una cella, quindi premere entrare chiave per ottenere il risultato, vedi screenshot:

Note:: Nella formula sopra: B2: F9 è l'intervallo di dati dei valori numerici che si desidera sommare; B1: F1 is le intestazioni di colonna contengono il valore di ricerca su cui si desidera sommare; I2 è il valore di ricerca all'interno delle intestazioni di colonna che stai cercando; A2: A9 è che le intestazioni di riga contengono il valore di ricerca su cui si desidera sommare; H2 è il valore di ricerca all'interno delle intestazioni di riga che stai cercando.

8. Vlookup per unire due tabelle in base a una o più colonne chiave

Nel tuo lavoro quotidiano, quando analizzi i dati, potresti dover raccogliere tutte le informazioni necessarie in un'unica tabella basata su una o più colonne chiave. Per risolvere questo lavoro, anche la funzione Vlookup può farti un favore.

Vlookup per unire due tabelle in base a una colonna chiave

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

Formula 1: utilizzo della funzione CERCA.VERT

Per unire le due tabelle in una in base a una colonna chiave, applica la seguente formula in una cella vuota in cui desideri ottenere il risultato, quindi trascina il quadratino di riempimento verso il basso sulle celle in cui desideri applicare questa formula, lo farai ottenere una tabella unita con la colonna dell'ordine che si unisce ai dati della prima tabella in base ai dati della colonna chiave.

Note:: Nella formula sopra, A2 è il valore che stai cercando, E2: F8 è la tabella da cercare, il numero 2 è il numero di colonna nella tabella da cui recuperare il valore.

Formula 2: utilizzo delle funzioni INDICE e CONFRONTA

Se i tuoi dati comuni nella parte destra e i dati restituiti nella colonna di sinistra all'interno della seconda tabella, per unire la colonna dell'ordine, la funzione Vlookup non è in grado di svolgere il lavoro. Per cercare da destra a sinistra, puoi utilizzare le funzioni INDICE e CONFRONTA per sostituire la funzione Vlookup.

Copia o inserisci la formula seguente in una cella vuota, quindi copia la formula nella colonna e la colonna dell'ordine è stata unita alla prima tabella, vedi screenshot:

Note:: Nella formula sopra, A2 è il valore di ricerca che stai cercando, E2: E8 è l'intervallo di dati che desideri restituire, F2: F8 è l'intervallo di ricerca che contiene il valore di ricerca.

Vlookup per unire due tabelle in base a più colonne chiave

Se le due tabelle che desideri unire hanno più colonne chiave, per unire le tabelle in base a queste colonne comuni, le funzioni INDICE e CONFRONTA possono aiutarti.

La formula generica per unire due tabelle in base a più colonne chiave è:

1. 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:

Note:: Nella formula sopra, ciò che i riferimenti di cella rappresentano come mostrato nell'immagine sottostante:

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

9. Valori di corrispondenza di Vlookup su più fogli di lavoro

Hai mai provato a visualizzare i valori di Vlookup su più fogli di lavoro? Supponendo di avere i seguenti tre fogli di lavoro con intervallo di dati e ora, voglio ottenere parte dei valori corrispondenti in base ai criteri di questi tre fogli di lavoro per ottenere il risultato come mostrato nell'immagine sottostante. In questo caso, il file Valori di Vlookup su più fogli di lavoro tutorial potrebbe farti un favore passo dopo passo.

I valori corrispondenti CERCA.VERT mantengono la formattazione delle celle

1. Vlookup per ottenere la formattazione della cella (colore della cella, colore del carattere) insieme al valore di ricerca

Come tutti sappiamo, la normale funzione Vlookup può solo aiutarci a restituire il valore corrispondente da un altro intervallo di dati, ma a volte potresti voler restituire il valore corrispondente insieme alla formattazione della cella, come il colore di riempimento, il colore del carattere, lo stile del carattere come mostrato nell'immagine sottostante. Questa sezione parlerà di come ottenere la formattazione della cella con il valore restituito in Excel.

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

1. Nel foglio di lavoro contiene i dati che desideri Vlookup, 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 sotto il codice VBA nella finestra del codice.

Codice VBA 1: Vlookup per ottenere la formattazione della cella insieme al valore di ricerca

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

3. Ancora in Microsoft Visual Basic, Applications Edition finestra, fare clic inserire > Modulo, quindi copia il codice VBA 2 di seguito nella finestra del modulo.

Codice VBA 2: Vlookup per ottenere la formattazione della cella insieme al valore di ricerca

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. Dopo aver inserito i codici sopra, fare clic su Strumenti > Referenze nello Microsoft Visual Basic, Applications Edition finestra. Quindi controlla il file Runtime di Microsoft Script casella di controllo in Riferimenti - VBAProject la finestra di dialogo. Vedi screenshot:

5. Quindi, fare clic OK per chiudere la finestra di dialogo, quindi salvare e chiudere la finestra del codice, ora tornare al foglio di lavoro e quindi applicare questa formula: =LookupKeepFormat(E2,$A$1:$C$10,3) in una cella vuota in cui si desidera visualizzare il risultato, quindi premere il tasto Invio. Vedi screenshot:

Note:: Nella formula sopra, E2 è il valore che cercherete, A1: C10 è l'intervallo della tabella e il numero 3 è il numero di colonna della tabella in cui desideri restituire il valore corrispondente.

6. Quindi, seleziona la prima cella del risultato e trascina il quadratino di riempimento verso il basso per ottenere tutti i risultati insieme alla loro formattazione. Vedi screenshot.

2. Mantieni il formato della data da un valore restituito da Vlookup

Normalmente, quando si utilizza la funzione Vloook per cercare e restituire il valore del formato della data corrispondente, alcuni formati numerici verranno visualizzati come mostrato nell'immagine sottostante. Per mantenere il formato della data da un risultato restituito, è necessario racchiudere la funzione TESTO alla funzione Vlookup.

Si prega di applicare la formula seguente in una cella vuota, quindi trascinare il quadratino di riempimento per copiare questa formula su altre celle e tutte le date corrispondenti sono state restituite come mostrato nell'immagine sottostante:

Note:: Nella formula sopra, E2 è il valore del look, A2: C9 è l'intervallo di ricerca, il numero 3 è il numero di colonna di cui vuoi restituire il valore, mm / gg / aaaa è il formato della data che desideri mantenere.

3. Vlookup e restituisci il valore corrispondente con il commento della cella

Hai mai provato a Vlookup per restituire non solo i dati della cella abbinati, ma anche il commento della cella in Excel come mostrato nella seguente schermata? Per risolvere questo compito, la seguente funzione definita dall'utente può farti un favore.

1. Tieni premuto il ALT + F11 chiavi per aprire il Microsoft Visual Basic, Applications Edition finestra.

2. Clic inserire > Modulo, 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, inserisci questa formula: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) in una cella vuota per individuare il risultato, quindi trascinare il quadratino di riempimento per copiare questa formula su altre celle, ora, i valori corrispondenti ei commenti vengono restituiti contemporaneamente, vedi screenshot:

Note:: Nella formula sopra, D2 è il valore di ricerca che desideri restituire al valore corrispondente, A2: B9 è la tabella dati che desideri utilizzare, il numero 2 è il numero di colonna che contiene il valore corrispondente che si desidera restituire.

4. Gestisci il testo e i numeri reali in Vlookup

Ad esempio, ho un intervallo di dati, il numero ID nella tabella originale è in formato numerico, nella cella di ricerca che è memorizzata come testo, quando si applica la normale funzione Vlookup, viene visualizzato un risultato di errore # N / D come nell'immagine sottostante mostrato. In questo caso, come potresti ottenere le informazioni corrette se il numero di ricerca e il numero originale nella tabella hanno un formato dati diverso?

Per gestire il testo e i numeri reali nella funzione Vlookup, applica la seguente formula in una cella vuota, quindi trascina il quadratino di riempimento verso il basso per copiare questa formula e otterrai i risultati corretti come mostrato nell'immagine sottostante:

Note:

• 1. Nella formula sopra, D2 è il valore di ricerca che desideri restituire al valore corrispondente, A2: B8 è la tabella dati che desideri utilizzare, il numero 2 è il numero di colonna che contiene il valore corrispondente che si desidera restituire.
• 2. Questa formula funziona bene anche se non sei sicuro di dove hai i numeri e dove hai il testo.

Scarica i file di esempio CERCA.VERT

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip

I migliori strumenti per la produttività in ufficio

Kutools per Excel risolve la maggior parte dei tuoi problemi e aumenta la tua produttività dell'80%

• Bar Super Formula (modifica facilmente più righe di testo e formula); Layout di lettura (leggi e modifica facilmente un gran numero di celle); Incolla su intervallo filtrato...
• Unisci celle / righe / colonne e conservazione dei dati; Contenuto delle celle divise; Combina righe duplicate e somma / media... Impedisci celle duplicate; Confronta intervalli...
• Seleziona Duplica o Unico Righe; Seleziona Righe vuote (tutte le celle sono vuote); Super Find e Fuzzy Find in molte cartelle di lavoro; Selezione casuale ...
• Copia esatta Più celle senza modificare il riferimento della formula; Riferimenti di creazione automatica a più fogli; Inserisci punti elenco, Caselle di controllo e altro ...
• Formule preferite e di inserimento rapido, Intervalli, grafici e immagini; Crittografa celle con password; Crea mailing list e invia email ...
• Estrai testo, Aggiungi testo, Rimuovi per posizione, Rimuovi spazio; Creare e stampare totali parziali di paging; Converti contenuto e commenti tra celle...
• Super filtro (salva e applica schemi di filtri ad altri fogli); Ordinamento avanzato per mese / settimana / giorno, frequenza e altro; Filtro speciale in grassetto, corsivo ...
• Combina cartelle di lavoro e fogli di lavoro; Unisci tabelle in base a colonne chiave; Suddividi i dati in più fogli; Conversione in batch xls, xlsx e PDF...
• Raggruppamento tabelle pivot per numero della settimana, giorno della settimana e altro ... Mostra celle sbloccate e bloccate da diversi colori; Evidenzia le celle che hanno formula / nome...

Leggi di più... Funzionalità complete Prova gratuita di 30 giorni ... Acquista... 

---

Scheda Office: porta l'interfaccia a schede a Office e semplifica notevolmente il lavoro

• Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
• Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
• Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!

Leggi di più... Funzionalità complete Prova gratuita di 30 giorni ... Acquista...