Normalmente, la funzione VLOOKUP restituisce la prima corrispondenza che trova. Ma la questione si complica quando c'è più di una corrispondenza e vogliamo ottenere un'occorrenza specifica, diciamo la seconda corrispondenza.
In questo articolo, impareremo come ottenere la seconda, la terza o l'ennesima corrispondenza utilizzando VLOOKUP. Per ottenere ciò dovremo utilizzare una colonna di supporto. Se non si desidera utilizzare una colonna di supporto poiché non è fattibile con tutti i dati, è possibile utilizzare l'approccio INDEX-MATCH. Ma se puoi usare una colonna di supporto, ecco la formula generica per trovare l'ennesima corrispondenza usando la funzione CERCA.VERT.
Formula generica
| =CERCA.VERT(valore_ricerca&occorrenza,array_tabella,colonna,0) |
Valore di ricerca: È il valore di ricerca che si desidera cercare.
Evento:L'occorrenza del valore di ricerca che si desidera far corrispondere.
Matrice di tabella:È l'array di tabelle in cui si desidera cercare i dati. Assicurati che la prima colonna di questa tabella sia la colonna helper.
Colonna: Il numero di colonna in matrice di tabella da cui si desidera recuperare il valore.
0: È per una corrispondenza esatta. Se non vuoi fare una corrispondenza esatta, omettila o usa 1 o TRUE.
Ora usiamolo in un esempio:
Esempio: trova la seconda vendita effettuata da un dipendente nella tabella di Excel
Abbiamo una tabella che registra le vendite effettuate dai venditori in diversi mesi. I nomi dei venditori possono essere ripetuti nel registro. Dobbiamo far corrispondere la seconda occorrenza di Micky e poi ottenere le vendite di Jan.
Nota che abbiamo aggiunto una colonna di supporto. Questa colonna scrive il nome del venditore e poi vi concatena l'occorrenza di quel nome. Abbiamo usato il conteggio parziale per ottenere l'occorrenza.
La formula nella colonna helper è:
| =B3&CONTA.SE($B$3:B3,B3) |
Quindi, abbiamo il tavolo pronto. Qui il valore di ricerca è nella cella P3, il numero dell'occorrenza è in Q3, la matrice della tabella è A3: N10 e il numero della colonna è 3.
Ora scrivi questa formula nella cella P4:
| =CERCA.VERT(P3&Q3,A3:N10,3,0) |
Premi invio e bam! hai il tuo valore di ricerca dell'occorrenza di cui avevi bisogno.
Come funziona?
La funzionalità è semplice. Inizialmente, non abbiamo alcun ID univoco da utilizzare come valore di ricerca. Quindi ne creiamo uno. Usiamo la formula del nome e del conteggio parziale per creare un ID univoco. Ci assicuriamo che sia la prima colonna nella tabella da sinistra poiché utilizzeremo per recuperare i valori dalle colonne a destra.
Ora la formula di ricerca crea l'id univoco usando l'operatore di concatenazione & (P3&Q3). Questo lo rende un ID unico
Successivamente, la formula CERCA.VERT lo prende come valore di ricerca e cerca la sua posizione nella tabellaA3:N10. Qui, CERCA.VERT trova il valore nella sesta riga della tabella. Ora si sposta nella terza colonna e restituisce il valore.
Questo è il modo più semplice per ottenere l'ennesima corrispondenza in Excel. Ma non è sempre fattibile. Aggiunge dati e calcoli extra al foglio che possono rendere il file excel pesante e lento.
Se hai una piccola quantità di dati, questo è ottimo ma con dati di grandi dimensioni, potresti voler utilizzare una formula indipendente che non necessita di una colonna di supporto. In tal caso, puoi utilizzare una formula di matrice che utilizza le funzioni INDICE e CONFRONTA.
Quindi sì ragazzi, ecco come ottenere l'ennesima corrispondenza in Excel utilizzando la funzione CERCA.VERT. Spero di essere stato abbastanza esplicativo e di essere stato utile. In caso di dubbi su questo articolo o su qualsiasi altro argomento correlato a Excel/VBA, chiedere nella sezione commenti qui sotto.
Articoli Correlati:
Cerca l'ennesima corrispondenza usando la funzione INDICE & CONFRONTA | Per ottenere l'ennesima corrispondenza senza utilizzare una colonna di supporto, utilizziamo la funzione INDICE e CONFRONTA. Usiamo la logica booleana per ottenere l'indice dalla tabella.
Come CERCARE più valori | Per recuperare tutti i valori corrispondenti da un elenco, utilizziamo la funzione INDICE CONFRONTA. CERCA.VERT può recuperare più valori solo quando utilizziamo una colonna di supporto.
Valore di ricerca con criteri multipli | Se devi cercare più di una tabella di ricerca, come usi CERCA.VERT da due o più tabelle di ricerca. Questo articolo risolve questo problema molto facilmente
Valore di ricerca con criteri multipli | Possiamo semplicemente usare la funzione VLOOKUP. Ma quando non hai quella colonna univoca nei tuoi dati e devi cercare in più colonne per abbinare un valore, CERCA.VERT non aiuta
Come cercare l'indirizzo in Excel |Ci saranno momenti in cui vorresti ottenere l'indirizzo della cella da cui viene recuperato un valore. Usando quell'indirizzo puoi facilmente recuperare i valori adiacenti usando la funzione OFFSET
Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
Come utilizzare la funzione CERCA.VERT di Excel| Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come usare Excel Funzione CONTA.SE| Conta i valori con le condizioni usando questa fantastica funzione. Non è necessario filtrare i dati per contare un valore specifico. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel | Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.