Per quelli di voi che sono esperti in Excel , molto probabilmente conoscete molto bene la funzione CERCA.VERT^(VLOOKUP) . La funzione CERCA.VERT^(VLOOKUP) viene 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^(VLOOKUP) , puoi controllare il mio post precedente su come utilizzare CERCA.VERT in Excel^{(how to use 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 introdurrà un'altra funzione in Excel chiamata INDEX-MATCH che può risolvere il problema.

CORRISPONDENZA INDICE Esempio di Excel

Utilizzando il seguente foglio di calcolo Excel di esempio , abbiamo un elenco di nomi di proprietari di auto e il nome dell'auto. In questo esempio, cercheremo di acquisire l'  ID auto^{(Car ID)} in base al modello di auto^{(Car Model)} elencato sotto più proprietari, come mostrato di seguito:

In un foglio separato chiamato CarType , abbiamo un semplice database di auto con l'  ID , il modello^{(Car Model)} e il colore^(Color) dell'auto .

Con questa impostazione della tabella, la  funzione CERCA.VERT^(VLOOKUP) può funzionare solo se i dati che vogliamo recuperare si trovano nella colonna a destra di ciò che stiamo cercando di abbinare ( campo Modello auto^{(Car Model )} ).

In altre parole, con questa struttura di tabella, dal momento che stiamo cercando di abbinarla in base al Modello dell'auto^{(Car Model)} , l'unica informazione che possiamo ottenere è Colore^{(Color )} (non ID poiché la colonna ID  si trova a sinistra della  colonna Modello dell'auto^{(Car Model )} .)

Questo perché con VLOOKUP , il valore di ricerca deve apparire nella prima colonna e le colonne di ricerca devono essere a 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, si tratta in realtà di combinare due funzioni di Excel che possono funzionare singolarmente: la funzione INDICE e la funzione ^(INDEX)CONFRONTA^(MATCH) .

Tuttavia, ai fini di questo articolo, parleremo solo della combinazione dei due con lo scopo di replicare la funzione di VLOOKUP .

La formula può sembrare un po' lunga e intimidatoria all'inizio. Tuttavia, dopo averlo usato più volte, imparerai la sintassi a memoria.

Questa è la formula completa nel nostro esempio:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Ecco la suddivisione per ciascuna sezione

=INDEX( – Il “=” indica l'inizio della formula nella cella e INDICE^(INDEX) è la prima parte della funzione di Excel che stiamo usando.

CarType!$A$2:$A$5 – le colonne del foglio CarType  in cui sono contenuti i dati che vorremmo recuperare. In questo esempio, l' ID  di ciascun modello di auto.^{(Car Model.)}

MATCH( – La seconda parte della funzione di Excel che stiamo usando.

B4 – La cella che contiene il testo di ricerca che stiamo utilizzando ( Modello auto^{(Car Model)} ) .

CarType!$B$2:$B$5 – Le colonne sul foglio CarType   con i dati che useremo per confrontare il testo di ricerca.

0)) – Per indicare che il testo di ricerca deve corrispondere esattamente al testo nella colonna corrispondente (ad esempio CarType!$B$2:$B$5 ). Se non viene trovata la corrispondenza esatta, la formula restituisce #N/A .

Nota: ricorda la doppia parentesi di chiusura alla fine di questa funzione “))” e le virgole tra gli argomenti.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

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 VLOOKUP :

1. Calcoli più veloci^{(Faster Calculations)}

Quando lavoriamo con set di dati di grandi dimensioni in cui il calcolo stesso può richiedere molto tempo a causa di molte funzioni VLOOKUP , scoprirai che una volta sostituite tutte quelle formule con INDEX-MATCH , il calcolo generale verrà calcolato più velocemente.

1. Non c'è bisogno di contare le colonne relative^{(No Need to Count Relative Columns)}

Se la nostra tabella di riferimento ha il testo chiave che vogliamo cercare nella colonna C e i dati che dobbiamo ottenere sono nella colonna AQ , dovremo sapere/contare quante colonne ci sono tra la colonna C e la colonna AQ quando si utilizza VLOOKUP .

Con le  funzioni INDEX-MATCH , possiamo selezionare direttamente la colonna dell'indice (es. colonna AQ) dove dobbiamo ottenere i dati e selezionare la colonna da abbinare (es. colonna C).

1. Sembra più complicato^{(It Looks More Complicated)}

VLOOKUP è abbastanza comune al giorno d'oggi, ma non molti sanno come usare insieme le funzioni INDEX-MATCH.

La stringa più lunga nella funzione INDEX-MATCH ti aiuta a farti sembrare un esperto nella gestione di funzioni Excel complesse e avanzate . Divertiti!

When to Use Index-Match Instead of VLOOKUP in Excel

For those of you who are well-versed in Excel, you are most likely very famіliar with the VLOOKUP function. The VLOOKUP function is used to find a value in a different cell based on some matching text within the same row.

If you are still new to VLOOKUP function, you can check out my previous post on how to use VLOOKUP in Excel.

As powerful as it is, VLOOKUP has a limitation on how the matching reference table needs to be structured in order for the formula to work.

This article will show you the limitation where VLOOKUP cannot be used and introduce another function in Excel called INDEX-MATCH that can solve the issue.

INDEX MATCH Excel Example

Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab the Car ID based on the Car Model listed under multiple owners as shown below:

On a separate sheet called CarType, we have a simple car database with the ID, Car Model and Color.

With this table setup, the VLOOKUP function can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Model field).

In other words, with this table structure, since we are trying to match it based on the Car Model, the only information that we can get is Color (Not ID as the ID column is located to the left of the Car Model column.)

This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.

The good news is, INDEX-MATCH will be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually: INDEX function and MATCH function.

However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function of VLOOKUP.

The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.

This is the full formula in our example:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Here is the breakdown for each section

=INDEX( – The “=” indicates the beginning of formula in the cell and INDEX is the first part of the Excel function that we are using.

CarType!$A$2:$A$5 – the columns on sheet CarType where the data we would like to retrieve is contained. In this example, the ID of each Car Model.

MATCH( – The second part of the Excel function that we are using.

B4 – The cell that contain search text that we are using (Car Model).

CarType!$B$2:$B$5 – The columns on sheet CarType  with the data which we will use to match against the search text.

0)) – To indicate that the search text has to exactly match with the text in the matching column (i.e. CarType!$B$2:$B$5). If the exact match is not found, the formula returns #N/A.

Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.

Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.

The INDEX-MATCH functions also have other benefits as compared to VLOOKUP:

1. Faster Calculations

When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.

1. No Need to Count Relative Columns

If our reference table is having the key text that we want to search in column C and the data that we need to get is in column AQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.

With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).

1. It Looks More Complicated

VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.

The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!

Related posts

• Come correggere gli errori #N/D in formule di Excel come CERCA.VERT

• Come eliminare le righe vuote in Excel

• Come utilizzare la funzione Speak Cells di Excel

• Come inserire un foglio di lavoro Excel in un documento di Word

• Come utilizzare l'analisi What-If di Excel

• Conserva i riferimenti di cella durante la copia di una formula in Excel

• Che cos'è un array VBA in Excel e come programmarne uno

• Come utilizzare i riferimenti assoluti in Excel

• Come rimuovere le righe duplicate in Excel

• Come inserire CSV o TSV in un foglio di lavoro Excel

• Come utilizzare la funzione PMT in Excel

• Collegamento di Excel a MySQL

• Come rimuovere le linee della griglia in Excel

• Come creare una tabella pivot semplice in Excel

• Come utilizzare il riempimento Flash in Excel

• Come unire celle, colonne e righe in Excel

• Centrare i dati del foglio di lavoro in Excel per la stampa

• Usa i nomi degli intervalli dinamici in Excel per i menu a discesa flessibili

• Differenze tra Microsoft Excel Online ed Excel per desktop

• 10 suggerimenti e trucchi di Excel per il 2019