Questi esempi ti insegneranno come visualizzare più criteri di Vlookup, restituire unistanza specifica o tutte le corrispondenze, eseguire Vlookup dinamico in più fogli e altro ancora.
È la seconda parte della serie che ti aiuterà a sfruttare la potenza di Excel CERCA.VERT. Gli esempi implicano che tu sappia come funziona questa funzione. In caso contrario, è logico iniziare con gli usi di base di CERCA.VERT in Excel.
Prima di proseguire, lascia che ti ricordi brevemente la sintassi:
Ora che tutti sono sulla stessa pagina, diamo uno sguardo più da vicino agli esempi di formula CERCA.VERT avanzata:
- Come visualizzare più criteri in Excel
- Vlookup e restituisce lennesima corrispondenza
- Vlookup più valori
- Vlookup in base ai valori di riga e colonna
- Formula di Vlookup annidata
- CERCA.VERT e INDIRETTO per estrarre dati da più fogli
Come eseguire la ricerca su più criteri
La funzione CERCA.VERT di Excel è davvero utile quando si tratta di cercare in un database per un certo valore. Tuttavia, manca di una caratteristica importante: la sua sintassi consente un solo valore di ricerca. Ma cosa succede se vuoi cercare con diverse condizioni? Ci sono alcune soluzioni diverse tra cui scegliere.
Formula 1. CERCA.VERT basato su tw o valori
Supponiamo di avere un elenco di ordini e di voler trovare la quantità in base a 2 criteri, Nome cliente e Prodotto. Un fattore complicante è che ogni cliente ha ordinato più prodotti, come mostrato nella tabella seguente:
Una normale formula CERCA.VERT non funziona in questo situazione perché restituisce la prima corrispondenza trovata in base a un singolo valore di ricerca specificato.
Per ovviare a questo problema, è possibile aggiungere una colonna helper e concatenare i valori da due colonne di ricerca (cliente e prodotto) lì. È importante che la colonna helper sia la colonna più a sinistra nella matrice della tabella perché è dove Excel CERCA.VERT cerca sempre il valore di ricerca.
Quindi, aggiungi una colonna a sinistra della tabella e copia la formula seguente in quella colonna. Questo popolerà la colonna helper con i valori delle colonne B e C (il carattere spazio è concatenato tra di loro per una migliore leggibilità):
=B2&" "&C2
Quindi, utilizza una formula VLOOKUP standard e inserisci entrambi i criteri nellargomento lookup_value, separati da uno spazio:
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
Oppure, inserisci i criteri in celle separate (G1 e G2 nel nostro caso) e concatena tali celle:
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
Come abbiamo vogliamo restituire un valore dalla colonna D, che è la quarta nella matrice della tabella, usiamo 4 per col_index_num. Largomento range_lookup è impostato su FALSE per Vlookup una corrispondenza esatta. Lo screenshot seguente mostra il risultato:
Nel caso in cui la tabella di ricerca si trovi in un altro foglio, includi il nome del foglio nella formula CERCA. Ad esempio:
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
In alternativa, crea un intervallo denominato per la tabella di ricerca (ad esempio, Ordini) per semplificare la formula- da leggere:
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
Per ulteriori informazioni, vedere Come eseguire il Vlookup da un altro foglio in Excel.
Formula 2. CERCA.VERT in Excel con più condizioni
In teoria, puoi utilizzare lapproccio sopra per Vlookup più di due criteri. H Tuttavia, ci sono un paio di avvertenze. In primo luogo, un valore di ricerca è limitato a 255 caratteri e, in secondo luogo, il design del foglio di lavoro potrebbe non consentire laggiunta di una colonna helper.
Fortunatamente, Microsoft Excel spesso fornisce più di un modo per fare la stessa cosa. Per visualizzare più criteri di Vlookup, è possibile utilizzare una combinazione INDEX MATCH o la funzione XLOOKUP recentemente introdotta in Office 365.
Ad esempio, per cercare in base a 3 diversi valori (data, nome del cliente e prodotto), utilizza una delle seguenti formule:
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
Dove:
Per la spiegazione dettagliata delle formule, vedere:
- XLOOKUP con più criteri
- Formula INDICE MATCH con più criteri
Come utilizzare CERCA.VERT per ottenere la seconda, la terza o lennesima corrispondenza
Come già saprai, CERCA.VERT di Excel può recuperare solo un valore corrispondente, più precisamente, restituisce la prima corrispondenza trovata. Ma cosa succede se ci sono più corrispondenze nel tuo array di ricerca e desideri ottenere la seconda o la terza istanza? Lattività sembra piuttosto complicata, ma la soluzione esiste!
Formula 1. Vlookup Ennesima istanza
Supponi di avere i nomi dei clienti in una colonna, i prodotti che hanno acquistato in unaltra e tu stanno cercando di trovare il secondo o il terzo prodotto acquistato da un determinato cliente.
Il modo più semplice è aggiungere una colonna helper a sinistra della tabella come abbiamo fatto nel primo esempio. Ma questa volta lo popoleremo con i nomi dei clienti e i numeri di occorrenza come “John Doe1”, “John Doe2”, ecc.
Per ottenere loccorrenza, utilizza la funzione COUNTIF con un riferimento a intervallo misto (il il primo riferimento è assoluto e il secondo è relativo come $ B $ 2: B2). Poiché il riferimento relativo cambia in base a una posizione della cella in cui viene copiata la formula, nella riga 3 diventerà $ B $ 2: B3, nella riga 4 – $ B $ 2: B4 e così via.
Concatenata al nome del cliente (B2), la formula assume questa forma:
=B2&COUNTIF($B$2:B2, B2)
La formula precedente va ad A2 e quindi lo copi su tutte le celle necessarie.
Dopodiché, inserisci il nome di destinazione e il numero di occorrenza in celle separate (F1 e F2) e utilizza la formula seguente per Vlookup unoccorrenza specifica:
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
Formula 2. Vlookup 2a occorrenza
Se stai cercando la seconda istanza del valore di ricerca, puoi fare a meno della colonna helper. Invece, crea larray di tabelle dinamicamente utilizzando la funzione INDIRECT insieme a MATCH:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
Dove:
- E1 è il valore di ricerca
- A2: A11 è lintervallo di ricerca
- B11 è lultima cella (in basso a destra) della tabella di ricerca
Tieni presente che la formula sopra è scritta per un caso specifico in cui le celle di dati nella tabella di ricerca iniziano nella riga 2. Se la tua tabella è da qualche parte in al centro del foglio, utilizza questa formula universale, dove A1 è la cella in alto a sinistra della tabella di ricerca contenente unintestazione di colonna:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
Come funziona questa formula
Ecco la parte fondamentale della formula che crea un intervallo di vlookup dinamico:
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
La funzione CONFRONTA configurata per la corrispondenza esatta (0 nellultimo argomento) confronta il nome di destinazione (E1) con lelenco dei nomi (A2: A11) e restituisce la posizione della prima corrispondenza trovata, che è 3 io no il tuo caso. Questo numero verrà utilizzato come coordinata della riga iniziale per lintervallo di vlookup, quindi aggiungiamo 2 (+1 per escludere la prima istanza e +1 per escludere la riga 1 con le intestazioni di colonna). In alternativa, puoi utilizzare 1 + ROW (A1) per calcolare automaticamente la regolazione necessaria in base alla posizione della riga di intestazione (A1 nel nostro caso).
Come risultato, otteniamo la seguente stringa di testo, quale INDIRETTO converte in un riferimento di intervallo:
INDIRECT("A"&5&":B11") -> A5:B11
Questo intervallo va allargomento table_array di VLOOKUP costringendolo a iniziare la ricerca in riga 5, tralasciando la prima istanza del valore di ricerca:
VLOOKUP(E1, A5:B11, 2, FALSE)
Come visualizzare Vlookup e restituire più valori in Excel
La funzione CERCA.VERT di Excel è progettata per restituire solo una corrispondenza. Cè un modo per Vlookup più istanze? Sì, cè, anche se non facile. Ciò richiede un uso combinato di diverse funzioni come INDICE, PICCOLO e RIGA è una formula di matrice.
Ad esempio, il seguente può trovare tutte le occorrenze del valore di ricerca F2 nellintervallo di ricerca B2: B16 e ritorno corrispondenze multiple dalla colonna C:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Esistono 2 modi per inserire la formula nel foglio di lavoro:
- Digita la formula nella prima cella, premi Ctrl + Maiusc + Invio, quindi trascinala verso il basso fino a qualche altra cella.
- Seleziona più celle adiacenti in una singola colonna (F1: F11 in lo screenshot qui sotto), digita la formula e premi Ctrl + Maiusc + Invio per completarla.
In ogni caso, il numero di celle in cui inserisci la formula dovrebbe essere uguale o maggiore di il numero massimo di corrispondenze possibili.
Per la spiegazione dettagliata della logica della formula e altri esempi, vedere Come CERCA.VERT più valori in Excel .
Come visualizzare Vlookup in righe e colonne (ricerca bidirezionale)
Ricerca a due vie (nota anche come ricerca a matrice o ricerca bidimensionale) è una parola di fantasia per cercare un valore allintersezione di una determinata riga e colonna.Esistono diversi modi per eseguire la ricerca bidimensionale in Excel, ma poiché il fulcro di questo tutorial è sulla funzione CERCA.VERT, la useremo naturalmente.
Per questo esempio, prenderemo la sotto la tabella con le vendite mensili ed elaborare una formula CERCA.VERT per recuperare la cifra delle vendite per un articolo specifico in un dato mese.
Con i nomi degli articoli in A2: A9, i nomi dei mesi in B1: F1, larticolo di destinazione in I1 e il mese target in I2, la formula è la seguente:
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
Come funziona questa formula
Il nucleo della formula è la funzione CERCA.VERT standard che cerca una corrispondenza esatta con il valore di ricerca in I1. Ma poiché non sappiamo in quale colonna si trovano esattamente le vendite per un mese specifico, non possiamo fornire il numero di colonna direttamente allargomento col_index_num. Per trovare quella colonna, utilizziamo la seguente funzione MATCH:
MATCH(I2, A1:F1, 0)
Tradotta in inglese, la formula dice: cerca il valore I2 in A1: F1 e restituisce la sua posizione relativa nella matrice. Fornendo 0 al terzo argomento, istruisci a MATCH per trovare il valore esattamente uguale al valore di ricerca (è come usare FALSE per largomento range_lookup di CERCA.VERT).
Poiché Mar è nella quarta colonna nellarray di ricerca, la funzione MATCH restituisce 4, che va direttamente allargomento col_index_num di VLOOKUP:
VLOOKUP(I1, A2:F9, 4, FALSE)
Si prega di prestare attenzione che sebbene i nomi dei mesi inizino nella colonna B, usiamo A1: I1 per larray di ricerca. Questo viene fatto in modo che il numero restituito da MATCH corrisponda alla posizione della colonna in table_array di VLOOKUP.
Per apprendere altri modi per eseguire la ricerca di matrici in Excel, vedere CORRISPONDENZA INDICE e altre formule per la ricerca bidimensionale.
Come eseguire più Vlookup in Excel (Vlookup nidificato)
A volte può accadere che la tabella principale e la tabella di ricerca non abbiano una singola colonna in comune, il che impedisce di eseguire un Vlookup tra due tabelle. Tuttavia, esiste unaltra tabella, che non contiene le informazioni che stai cercando ma ha una colonna comune con la tabella principale e unaltra colonna comune con la tabella di ricerca.
Nellimmagine sottostante viene illustrata la situazione:
Lobiettivo è copiare i prezzi nella tabella principale in base agli ID articolo. Il problema è che la tabella contenente i prezzi non ha gli ID articolo, il che significa che dovremo fare due Vlookup in una formula.
Per comodità, creiamo prima un paio di intervalli denominati :
- La tabella di ricerca 1 è denominata Products (D3: E3).
- La tabella di ricerca 2 è denominata Prezzi (G3: H3).
Le tabelle possono essere nello stesso foglio di lavoro o in fogli di lavoro diversi.
E ora eseguiremo il cosiddetto doppio Vlookup, noto anche come Vlookup annidato.
Innanzitutto, effettua un CERCA.VERT formula per trovare il nome del prodotto nella tabella di ricerca 1 (denominata Prodotti) in base allID articolo (A3):
=VLOOKUP(A3, Products, 2, FALSE)
Successivamente, inserisci la formula precedente nellargomento lookup_value di unaltra funzione CERCA.VERT per estrarre i prezzi dalla tabella di ricerca 2 (denominata Prezzi) in base al nome del prodotto restituito dal CERCA.VERT nidificato:
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
Lo screenshot qui sotto mostra la nostra formula Vlookup nidificata in azione:
Come visualizzare più Vlookup fogli dinamicamente
A volte, potresti avere dati nello stesso formato suddivisi su più fogli di lavoro. E il tuo scopo è estrarre i dati da un foglio specifico a seconda del valore della chiave in una determinata cella.
Questo può essere più facile da capire da un esempio. Supponiamo che tu abbia alcuni rapporti sulle vendite regionali nello stesso formato e che tu stia cercando di ottenere i dati sulle vendite per un prodotto specifico in determinate regioni:
Come nellesempio precedente, iniziamo con la definizione di alcuni nomi:
- Intervallo A2: B5 nel foglio CA è denominato CA_Sales.
- Intervallo A2 : B5 nel foglio FL è denominato FL_Sales.
- Intervallo A2: B5 nel foglio KS è denominato KS_Sales.
Come puoi vedere, tutti gli intervalli denominati hanno un comune parte (Vendite) e parti uniche (CA, FL, KS). Assicurati di denominare i tuoi intervalli in modo simile poiché è essenziale per la formula che stiamo per costruire.
Formula 1. CERCA.INDIRETTO per estrarre dinamicamente i dati da fogli diversi
Se il tuo compito è recuperare dati da più fogli, una formula CERCA.VERT INDIRETTO è la soluzione migliore: compatta e di facile comprensione.
Per questo esempio, organizziamo la tabella di riepilogo in questo modo:
- Inserisci i prodotti di interesse in A2 e A3. Questi sono i nostri valori di ricerca.
- Immettere le parti univoche degli intervalli denominati in B1, C1 e D1.
E ora, concateniamo la cella contenente la parte univoca (B1) con la parte comune (“_Sales”) e forniamo la stringa risultante a INDIRECT:
INDIRECT(B$1&"_Sales")
La funzione INDIRETTO trasforma la stringa in un nome che Excel può capire e tu la metti nellargomento table_array di CERCA.VERT:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
La formula precedente va in B2, quindi la copi in basso a destra.
Fai attenzione che, nel valore di ricerca ($ A2), abbiamo bloccato la coordinata della colonna con un riferimento di cella assoluto in modo che la colonna rimanga fissa quando la formula viene copiata a destra. Nel riferimento B $ 1, abbiamo bloccato la riga perché vogliamo che la colonna coordinare per modificare e fornire una parte del nome appropriata a INDIRETTO a seconda della colonna in cui viene copiata la formula:
Se la tabella principale è organizzata diversamente, i valori di ricerca in una riga e le parti univoche dei nomi degli intervalli in una colu mn, dovresti bloccare la coordinata della riga nel valore di ricerca (B $ 1) e la coordinata della colonna nelle parti del nome ($ A2):
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
Formula 2. CERCA.VERT e IF nidificati per cercare più fogli
In situazioni in cui hai solo due o tre fogli di ricerca, puoi utilizzare una formula CERCA.VERT abbastanza semplice con funzioni IF nidificate per selezionare il foglio corretto in base al valore della chiave in una cella particolare:
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
Dove $ A2 è il valore di ricerca (nome elemento) e B $ 1 è il valore chiave (stato):
In questo caso, non è necessario definire i nomi e puoi utilizzare riferimenti esterni per fare riferimento a un altro foglio o cartella di lavoro.
Per altri esempi di formule, vedi Come CERCA.VERT su più fogli in Excel .
Ecco come utilizzare CERCA.VERT in Excel. Ti ringrazio per la lettura e spero di vederti sul nostro blog la prossima settimana!
Pratica cartella di lavoro da scaricare
Esempi di formule CERCA.VERT avanzato (file .xlsx)
- Esercitazione su CERCA.VERT di Excel per principianti
- Come visualizzare più corrispondenze in Excel
- Come utilizzare CERCA.VERT & SOMMA o SOMMA.SE in Excel
- INDICE & CONFRONTA funzioni in Excel: unalternativa migliore a CERCA.VERT
- 4 modi per eseguire un Vlookup con distinzione tra maiuscole e minuscole in Excel
- CERCA.VERT in Excel non funzionante: errori N / A, NOME e VALORE