Perché INDEX e MATCH sono migliori di VLOOKUP in Excel

In precedenza abbiamo spiegato ai principianti come utilizzare le funzioni di base di VLOOKUP (in inglese VLOOKUP, l'abbreviazione sta per "funzione di ricerca verticale"). E agli utenti esperti sono state mostrate diverse formule più complicate.

E in questo articolo cercheremo di fornire informazioni su un altro metodo di lavoro con la ricerca verticale.

Ti starai chiedendo: "Perché è necessario?". E questo è necessario per mostrare tutti i possibili metodi di ricerca. Inoltre, numerose restrizioni VLOOKUP spesso impediscono di ottenere il risultato desiderato. A questo proposito, INDEX() MATCH() è molto più funzionale e diversificato e ha anche meno restrizioni.

Nozioni di base INDICE MATCH

Poiché lo scopo di questa guida è mostrare quanto sia valida questa funzionalità, noi Diamo un'occhiata alle informazioni di base relative ai principi del suo funzionamento. E mostreremo esempi e considereremo anche perché è meglio di VLOOKUP ().

Sintassi e utilizzo della funzione INDEX

Questa funzione aiuta a trovare il valore desiderato tra le aree di ricerca specificate in base alla colonna o al numero di riga. Sintassi:

=INDEX(array, numero riga, numero colonna):

• array – l'area in cui avverrà la ricerca;
• numero di riga: il numero della riga in cui eseguire la ricerca nell'array specificato. Se il numero di riga è sconosciuto, è necessario specificare il numero di colonna;
• numero di colonna: il numero della colonna da trovare nell'array specificato. Se il valore è sconosciuto, è richiesto un numero di riga.

Un esempio di una formula semplice:

=INDICE(A1:S10,2,3)

La funzione cercherà nell'intervallo da A1 a C10. I numeri mostrano da quale riga (2) e colonna (3) mostrare il valore desiderato. Il risultato sarà la cella C2.

Abbastanza semplice, vero? Ma quando lavori con documenti reali, è improbabile che tu abbia informazioni sui numeri di colonna o sulle celle. Ecco a cosa serve la funzione MATCH().

Sintassi e utilizzo della funzione MATCH

La funzione CONFRONTA() ricerca il valore desiderato e ne mostra il numero approssimativo nell'area di ricerca specificata.

La sintassi searchpos() è simile a questa:

=MATCH(valore da cercare, array da cercare, tipo di corrispondenza)

• valore di ricerca – il numero o il testo da trovare;
• array ricercato – l'area in cui avverrà la ricerca;
• tipo di corrispondenza – specifica se cercare il valore esatto o i valori ad esso più vicini:
  • 1 (o nessun valore specificato): restituisce il valore più grande uguale o inferiore al valore specificato;
  • 0 – mostra una corrispondenza esatta con il valore cercato. Nella combinazione INDEX() MATCH() avrai quasi sempre bisogno di una corrispondenza esatta, quindi scriviamo 0;
  • -1 – Mostra il valore più piccolo maggiore o uguale al valore specificato nella formula. L'ordinamento viene eseguito in ordine decrescente.

Ad esempio, nell'intervallo B1:B3 sono registrate New York, Parigi, Londra. La formula seguente mostrerà il numero 3 perché Londra è la terza nella lista:

=ESPOSIZIONE(Londra,B1:B3,0)

Come lavorare con la funzione INDEX MATCH 

Probabilmente hai già iniziato a capire il principio con cui si costruisce il lavoro congiunto di queste funzioni. Insomma, allora INDEX() cerca il valore desiderato tra le righe e le colonne specificate. E MATCH() mostra i numeri di questi valori:

=INDEX(colonna da cui viene restituito il valore, MATCH(valore in cui cercare, colonna in cui cercare, 0))

Hai ancora difficoltà a capire come funziona? Forse un esempio spiegherà meglio. Supponiamo di avere un elenco di capitali mondiali e la loro popolazione:

Per conoscere la dimensione della popolazione di una certa capitale, ad esempio la capitale del Giappone, utilizziamo la seguente formula:

=INDICE(C2:C10, PARTITA(Giappone, LA2:LA10,0))

Spiegazione:

• La funzione MATCH() cerca il valore – "Giappone" nell'array A2:A10 e restituisce il numero 3, perché il Giappone è il terzo valore nell'elenco. 
• Questa cifra va anumero di riga” nella formula INDEX() e indica alla funzione di stampare un valore da questa riga.

Quindi la formula sopra diventa la formula standard INDICE(C2:C10,3). La formula ricerca dalle celle C2 a C10 e restituisce i dati dalla terza cella in questo intervallo, ovvero C4, perché il conto alla rovescia inizia dalla seconda riga.

Non vuoi prescrivere il nome della città nella formula? Quindi scrivilo in qualsiasi cella, ad esempio F1, e usalo come riferimento nella formula MATCH(). E ti ritrovi con una formula di ricerca dinamica:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Importante! Numero di righe in schieramento INDEX() deve essere uguale al numero di righe in array considerato in MATCH(), altrimenti otterrai il risultato sbagliato.

Aspetta un minuto, perché non usare semplicemente la formula VLOOKUP()?

= CERCA.VERT(FA1, LA2:DO10, 3, Falso)

 Che senso ha perdere tempo cercando di capire tutte queste complessità di INDEX MATCH?

In questo caso, non importa quale funzione utilizzare. Questo è solo un esempio per capire come funzionano insieme le funzioni INDEX() e MATCH(). Altri esempi mostreranno di cosa sono capaci queste funzioni in situazioni in cui VLOOKUP è impotente. 

CORRISPONDENZA INDICE o VLOOKUP

Quando si decide quale formula di ricerca utilizzare, molti concordano sul fatto che INDEX() e MATCH() sono di gran lunga superiori a VLOOKUP. Tuttavia, molte persone usano ancora VLOOKUP(). In primo luogo, VLOOKUP() è più semplice e, in secondo luogo, gli utenti non comprendono appieno tutti i vantaggi di lavorare con INDEX() e MATCH(). Senza questa conoscenza, nessuno accetterà di dedicare il proprio tempo allo studio di un sistema complesso.

Ecco i principali vantaggi di INDEX() e MATCH() rispetto a VLOOKUP():

• Cerca da destra a sinistra. VLOOKUP() non può cercare da destra a sinistra, quindi i valori che stai cercando devono essere sempre nelle colonne più a sinistra della tabella. Ma INDEX() e MATCH() possono gestirlo senza problemi. Questo articolo ti spiegherà come appare in pratica: come trovare il valore desiderato sul lato sinistro.

1. Aggiunta o rimozione sicura di colonne. La formula VLOOKUP() mostra risultati errati durante la rimozione o l'aggiunta di colonne perché VLOOKUP() ha bisogno del numero esatto di colonna per avere successo. Naturalmente, quando le colonne vengono aggiunte o rimosse, cambiano anche i loro numeri. 

E nelle formule INDEX() e MATCH() viene specificato un intervallo di colonne, non singole colonne. Di conseguenza, puoi tranquillamente aggiungere e rimuovere colonne senza dover aggiornare la formula ogni volta.

1. Nessun limite sui volumi di ricerca. Quando usi CERCA.VERT(), il numero totale dei criteri di ricerca non deve superare i 255 caratteri o otterrai un #VALORE! Quindi, se i tuoi dati contengono un numero elevato di caratteri, INDEX() e MATCH() sono l'opzione migliore.
2. Alta velocità di elaborazione. Se i tuoi tavoli sono relativamente piccoli, è improbabile che noterai alcuna differenza. Ma, se la tabella contiene centinaia o migliaia di righe e, di conseguenza, ci sono centinaia e migliaia di formule, INDEX () e MATCH () faranno fronte molto più velocemente di VLOOKUP (). Il fatto è che Excel elaborerà solo le colonne specificate nella formula, invece di elaborare l'intera tabella. 

L'impatto sulle prestazioni di VLOOKUP() sarà particolarmente evidente se il foglio di lavoro contiene un numero elevato di formule come VLOOKUP() e SUM(). Sono necessari controlli separati delle funzioni VLOOKUP() per analizzare ogni valore in una matrice. Quindi Excel deve elaborare un'enorme quantità di informazioni e questo rallenta notevolmente il lavoro.

Esempi di formule 

Abbiamo già capito l'utilità di queste funzioni, quindi possiamo passare alla parte più interessante: l'applicazione pratica della conoscenza.

Formula per la ricerca da destra a sinistra

Come già accennato, VLOOKUP non può eseguire questa forma di ricerca. Quindi, se i valori desiderati non sono nella colonna più a sinistra, VLOOKUP() non produrrà un risultato. Le funzioni INDEX() e MATCH() sono più versatili e la posizione dei valori non gioca un ruolo importante per il loro funzionamento.

Ad esempio, aggiungeremo una colonna di rango sul lato sinistro della nostra tabella e cercheremo di capire quale rango in termini di popolazione occupa la capitale del Nostro Paese.

Nella cella G1 scriviamo il valore da trovare, quindi utilizziamo la seguente formula per cercare nell'intervallo C1:C10 e restituire il valore corrispondente da A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Richiesta. Se prevedi di utilizzare questa formula per più celle, assicurati di correggere gli intervalli utilizzando l'indirizzamento assoluto (ad esempio, $A$2: $A$10 e $C$2: 4C$10).

INDICE PIÙ ESPOSTI PIÙ ESPOSTI  per cercare in colonne e righe

Negli esempi precedenti, abbiamo utilizzato queste funzioni in sostituzione di VLOOKUP() per restituire valori da un intervallo predefinito di righe. Ma cosa succede se è necessario eseguire una ricerca a matrice o a due lati?

Sembra complicato, ma la formula per tali calcoli è simile alla formula standard INDEX() MATCH(), con una sola differenza: la formula MATCH() deve essere utilizzata due volte. La prima volta per ottenere il numero di riga e la seconda volta per ottenere il numero di colonna:

=INDEX(array, MATCH(valore di ricerca verticale, colonna di ricerca, 0), MATCH(valore di ricerca orizzontale, riga di ricerca, 0))

Diamo un'occhiata alla tabella seguente e proviamo a fare una formula INDICE() ESPRESSO() ESPRESSO() per visualizzare i dati demografici in un paese specifico per un anno selezionato.

Il paese di destinazione è nella cella G1 (ricerca verticale) e l'anno di destinazione è nella cella G2 (ricerca orizzontale). La formula sarà simile a questa:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Come funziona questa formula

Come con qualsiasi altra formula complessa, sono più facili da capire suddividendole in singole equazioni. E poi puoi capire cosa fa ogni singola funzione:

• PARTITA(G1,LA2:LA11,0) – cerca un valore (G1) nel range A2:A11 e mostra il numero di questo valore, nel nostro caso è 2;
• RICERCA(G2,B1:D1,0) – cerca un valore (G2) nell'intervallo B1:D1. In questo caso il risultato è stato 3.

I numeri di riga e colonna trovati vengono inviati al valore corrispondente nella formula INDEX():

=INDICE(B2:D11,2,3)

Di conseguenza, abbiamo un valore che si trova in una cella all'intersezione di 2 righe e 3 colonne nell'intervallo B2:D11. E la formula mostra il valore desiderato, che si trova nella cella D3.

Cerca per più condizioni con INDEX e MATCH

Se hai letto la nostra guida a VLOOKUP(), probabilmente hai provato più formule di ricerca. Ma questo metodo di ricerca ha una limitazione significativa: la necessità di aggiungere una colonna ausiliaria.

Ma la buona notizia è questa Con INDEX() e MATCH() puoi cercare più condizioni senza dover modificare o cambiare il tuo foglio di lavoro.

Ecco la formula di ricerca multi-condizione generale per INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

La nota: questa formula deve essere utilizzata insieme alla scorciatoia da tastiera CTRL+MAIUSC+INVIO.

Supponiamo di dover trovare il valore che stai cercando in base a 2 condizioni: Acquirente и Prodotto.

Ciò richiede la seguente formula:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

In questa formula, C2:C10 è l'intervallo in cui avverrà la ricerca, F1 - questa condizione, LA2:LA10 — è l'intervallo per confrontare la condizione, F2 – condizione 2, V2: V10 – intervallo per il confronto della condizione 2.

Non dimenticare di premere la combinazione alla fine del lavoro con la formula CTRL + MAIUSC + INVIO – Excel chiuderà automaticamente la formula tra parentesi graffe, come mostrato nell'esempio:

Se non vuoi usare una formula di matrice per il tuo lavoro, aggiungi un altro INDEX() alla formula e premi INVIO, apparirà come nell'esempio:

Come funzionano queste formule

Questa formula funziona allo stesso modo della formula standard INDEX() MATCH(). Per cercare più condizioni, è sufficiente creare più condizioni False e Vere che rappresentano le singole condizioni corrette e errate. E poi queste condizioni si applicano a tutti gli elementi corrispondenti dell'array. La formula converte gli argomenti False e True rispettivamente in 0 e 1 e restituisce un array in cui 1 è i valori corrispondenti che sono stati trovati nella stringa. MATCH() troverà il primo valore che corrisponde a 1 e lo passerà alla formula INDEX(). E, a sua volta, restituirà il valore già desiderato nella riga specificata dalla colonna desiderata.

Una formula senza un array dipende dalla capacità di INDEX() di gestirli da solo. Il secondo INDEX() nella formula corrisponde a false (0), quindi passa l'intero array con quei valori alla formula MATCH(). 

Questa è una spiegazione piuttosto lunga della logica alla base di questa formula. Per maggiori informazioni leggi l'articolo “INDICE MATCH con più condizioni'.

MEDIA, MAX e MIN in INDEX e MATCH

Excel ha le sue funzioni speciali per trovare medie, massimi e minimi. Ma cosa succede se si desidera ottenere i dati dalla cella associata a quei valori? In questo caso AVERAGE, MAX e MIN devono essere utilizzati insieme a INDEX e MATCH.

INDICE MATCH e MAX

Per trovare il valore più grande nella colonna D e visualizzarlo nella colonna C, utilizzare la formula: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

CORRISPONDENZA INDICE e MIN

Per trovare il valore più piccolo nella colonna D e visualizzarlo nella colonna C, utilizzare la formula seguente:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

RICERCA INDICE e SERPENTE

Per trovare il valore medio nella colonna D e visualizzare questo valore in C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

A seconda di come vengono scritti i dati, il terzo argomento di MATCH() è 1, 0 o -1:

• se le colonne sono ordinate in ordine crescente, impostare 1 (quindi la formula calcolerà il valore massimo, che è minore o uguale al valore medio);
• se l'ordinamento è decrescente, allora -1 (la formula produrrà il valore minimo maggiore o uguale alla media);
• se l'array di ricerca contiene un valore esattamente uguale alla media, impostalo su 0. 

 Nel nostro esempio, la popolazione è ordinata in ordine decrescente, quindi mettiamo -1. E il risultato è Tokyo, poiché il valore della popolazione (13,189) è il più vicino al valore medio (000).

VLOOKUP() può anche eseguire tali calcoli, ma solo come formula di matrice: VLOOKUP con AVERAGE, MIN e MAX.

INDEX MATCH e ESND/IFERROR

Probabilmente hai già notato che se la formula non riesce a trovare il valore desiderato, genera un errore #N / A. Puoi sostituire il messaggio di errore standard con qualcosa di più informativo. Ad esempio, imposta l'argomento nella formula Nel XNUMX:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Con questa formula, se inserisci dati che non sono nella tabella, il modulo ti darà il messaggio specificato.

Se vuoi catturare tutti gli errori, allora tranne Nel XNUMX può essere utilizzata IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Qualcosa è andato storto!")

Ma ricorda che mascherare gli errori in questo modo non è una buona idea, perché gli errori standard segnalano le violazioni nella formula.

Ci auguriamo che tu abbia trovato utile la nostra guida all'uso della funzione INDEX MATCH().