Per quelli di voi che sono esperti in Excel, è molto probabile che abbiate familiarità con la funzione CERCA.VERT . La funzione CERCA.VERT è utilizzata per trovare un valore in una cella diversa in base a un testo corrispondente all'interno della stessa riga.
Se sei ancora nuovo alla funzione CERCA.VERT, puoi controllare il mio post precedente su come utilizzare VLOOKUP in Excel.
Per quanto potente, VLOOKUP ha una limitazione su come la tabella di riferimento corrispondente deve essere strutturata affinché la formula funzioni.
Questo articolo ti mostrerà la limitazione in cui VLOOKUP non può essere utilizzato e introduce un'altra funzione in Excel chiamata INDICE-MATCH che può risolvere il problema.
Esempio di Excel per la corrispondenza
Utilizzando il seguente foglio di calcolo Excel di esempio, abbiamo un elenco di nomi di proprietari di auto e il nome della vettura. In questo esempio, cercheremo di prendere l' ID auto basato sul modello di auto elencato in più proprietari come mostrato di seguito:
Su un foglio separato chiamato CarType, abbiamo un semplice database di auto con ID, modello di auto e colore .
Con questa impostazione tabella, la funzione CERCA.VERT può funzionare solo se i dati che vogliamo recuperare si trovano nella colonna a destra di ciò che stiamo tentando di abbinare (campo Modello vettura ).
In altre parole, con questa struttura di tabelle, dal momento che stiamo cercando di associarlo in base al modello di auto, l'unica informazione che possiamo ottenere è Colore (non ID poiché la colonna ID si trova a sinistra della colonna Modello auto ).
Questo perché con CERCA.VERT, il valore di ricerca deve apparire nella prima colonna e le colonne di ricerca devono essere sulla destra. Nessuna di queste condizioni è soddisfatta nel nostro esempio.
La buona notizia è che INDEX-MATCH sarà in grado di aiutarci a raggiungere questo obiettivo. In pratica, questo è in realtà unendo due funzioni di Excel che possono funzionare individualmente: la funzione INDICE e la funzione MATCH .
Tuttavia, ai fini di questo articolo, parleremo solo della combinazione dei due con l'obiettivo di replicare la funzione di VLOOKUP .
La formula può sembrare un po 'lunga e intimidatoria all'inizio. Tuttavia, dopo averlo usato più volte, imparerai a memoria la sintassi.
Questa è la formula completa nel nostro esempio:
= INDEX (CarType $ A $ 2:!! $ A $ 5, MATCH (B4, CarType $ B $ 2: $ B $ 5, 0))
Ecco la ripartizione per ogni sezione
= INDICE ( - "=" indica l'inizio della formula nella cella e INDICE è la prima parte della funzione di Excel che stiamo usando.
CarType! $ A $ 2: $ A $ 5 - le colonne sul foglio CarType in cui sono contenuti i dati che vorremmo recuperare. In questo esempio, l' ID di ciascun modello di auto.
MATCH ( - La seconda parte della funzione di Excel che stiamo usando.
B4 - La cella che contiene il testo di ricerca che stiamo utilizzando ( modello di auto ) .
CarType! $ B $ 2: $ B $ 5 - Le colonne sul foglio CarType con i dati che useremo per abbinare il testo di ricerca.
0)) - Per indicare che il testo di ricerca deve corrispondere esattamente al testo nella colonna corrispondente (es. CarType! $ B $ 2: $ B $ 5 ). Se la corrispondenza esatta non viene trovata, la formula restituisce # N / D.
Nota : ricorda la doppia parentesi di chiusura alla fine di questa funzione "))" e le virgole tra gli argomenti.
Personalmente mi sono allontanato da VLOOKUP e ora uso INDEX-MATCH in quanto è in grado di fare più di VLOOKUP.
Le funzioni INDEX-MATCH hanno anche altri vantaggi rispetto a CERCA.VERT :
- Calcoli più veloci
Quando lavoriamo con dataset di grandi dimensioni in cui il calcolo stesso può richiedere molto tempo a causa di molte funzioni CERCA.VERT, scoprirai che una volta sostituite tutte queste formule con INDEX-MATCH, il calcolo complessivo verrà calcolato più rapidamente.
- Non è necessario contare le colonne relative
Se la nostra tabella di riferimento sta avendo il testo chiave che vogliamo cercare nella colonna C e i dati che dobbiamo ottenere è nella colonna AQ, dovremo sapere / contare quante colonne sono tra la colonna C e la colonna AQ quando si usa VLOOKUP .
Con le funzioni INDEX-MATCH, possiamo selezionare direttamente la colonna indice (ad es. Colonna AQ) dove dobbiamo ottenere i dati e selezionare la colonna da abbinare (es. Colonna C).
- Sembra più complicato
VLOOKUP è abbastanza comune al giorno d'oggi, ma non molti sanno di usare insieme le funzioni INDEX-MATCH.
La stringa più lunga nella funzione INDEX-MATCH ti aiuta a diventare un esperto nella gestione di funzioni Excel complesse e avanzate. Godere!
