Index Match: la migliore alternativa a Vlookup

VLOOKUP è una di quelle funzioni che rendono Excel eccezionale. Sebbene molti possano non trovarlo intuitivo all'inizio, diventa presto una seconda natura e indispensabile. Alcuni addirittura trovano che cambia la vita. VLOOKUP ha i suoi problemi, per esempio, legge solo da sinistra a destra. Se devi cercare un valore a sinistra di una colonna di riferimento, dovrai riorganizzare il foglio di lavoro per utilizzare la funzione.

CERCA.VERT inoltre ingrandisce molte colonne quando in realtà ne stai usando solo due. la sintassi standard fa riferimento solo ai valori relativi delle colonne, se modifichi la tabella sei sfortunato ancora una volta e devi fare un po' di riorganizzazione e riscrittura delle funzioni, che può essere molto noiosa in grandi cartelle di lavoro. Ciò crea anche problemi nella copia della funzione in altre colonne.

C'è però un'alternativa, INDICE + CONFRONTA. Non solo replica la funzionalità, ma ha i suoi vantaggi e bonus. È qualcosa che potresti non aver sentito prima (soprattutto se non leggi Excel per divertimento) ma entro la fine di questo articolo sarai in grado di metterlo in pratica e raccogliere i frutti.

Scomposizione dell'INDICE + CORRISPONDENZA

Qualcosa che molti utenti trascurano è che le funzioni di Excel possono essere concatenate e combinate per creare qualcosa di ancora più potente delle parti costituenti. Ogni funzione accetta un argomento, se hai bisogno di un po' più di potenza o funzionalità estese, questi argomenti possono essere costituiti da altre funzioni.

INDICE

=INDICE(matrice, numero di riga)

La funzione INDEX accetta un array utente, un insieme di valori come una colonna e restituisce il valore della cella in quella particolare posizione. Ad esempio, ho una colonna di dati che registra le posizioni di una gara tenuta in un certo momento in passato. Voglio sapere quale pilota è arrivato terzo, per trovarlo posso semplicemente entrare,

=INDICE(B2:B6, 3)

e questo restituirà Rusty Shackleford.

Semplice ma utile. Ora abbiamo la possibilità di restituire valori all'interno di un array.

INCONTRO

=MATCH(valore di ricerca, array di ricerca, tipo di corrispondenza)

CONFRONTA() è un'altra semplice funzione, correlata alla funzione INDICE che abbiamo appena trattato. Invece di restituire un valore, restituisce invece un numero che indica la posizione relativa di un valore all'interno di un array. CONFRONTA richiede i seguenti argomenti; il valore, l'array da cercare e il tipo di corrispondenza. Il tipo di corrispondenza di solito è impostato su 0, ma dipende dall'utilizzo. 0 significa che vuoi solo una corrispondenza esatta, 1 o -1 significa che se non c'è una corrispondenza esatta che vuoi il valore successivo più vicino.

Usando il nostro esempio di gara sopra, conosco il pilota che devo trovare ma non conosco la sua posizione nella classifica finale della gara. Poiché la posizione è relativa, devo assicurarmi di coprire l'intero risultato a partire dalla prima posizione.

=MATCH("Trota Kilgore", B2:B6, 0)

Questo restituirà un valore di 4. Dato che siamo partiti dall'alto, sappiamo che Trout ha terminato in quarta posizione in questa particolare gara.

INDICE CORRISPONDENZA

Forse hai visto dove siamo diretti ora o forse hai saltato avanti per arrivare alle cose buone. Usando INDEX + MATCH possiamo combinare queste due semplici funzioni per creare qualcosa di simile ma più flessibile del nostro fidato VLOOKUP. Non preoccuparti se hai bisogno di leggerlo un paio di volte, all'inizio può essere un po' complicato. Una volta compreso cosa sta succedendo, è davvero facile ricostruirlo dai componenti.

INDEX restituisce un valore specifico. Questa è la funzione primaria di CERCA.VERT, dato un insieme di parametri; puoi trovare rapidamente un valore necessario. Ora l'unica cosa che ci manca è un modo per trovare la posizione della riga. Se devi trovarlo manualmente, perdi l'utilità di una funzione. Usando MATCH nel secondo argomento possiamo prendere il suo output, una posizione relativa, e inserirla nella funzione Index, risolvendo il problema. Ora abbiamo qualcosa che fa esattamente la stessa cosa di CERCA.VERT, con solo un po' più di digitazione.

Mettere in pratica la funzione

Utilizzando la tabella seguente, siamo interessati a capire quale tipo di prodotto è associato a un ID prodotto specifico.

Per usare INDEX MATCH iniziamo con INDEX poiché alla fine vogliamo che venga restituito un valore specifico piuttosto che una posizione. All'interno dell'INDICE includiamo CONFRONTA per restituire una posizione per la nostra funzione INDICE.

=INDICE(A2:A6,CONFRONTA(8316,B2:B6,0))

Quello che sta succedendo qui è che cercherò nella colonna A il valore della riga nella colonna B, fornito dalla funzione Match.

All'interno della funzione match, specifico che devo cercare tra i codici Product ID in B2:B6, e rispedire la posizione del valore 8316. In questo caso, è nella posizione 4 che è l'equivalente di scrivere

=INDICE(A2:A6, 4)

Excel esegue la funzione e trova la posizione nella matrice del numero ID specificato. Prende quella posizione e cerca la stessa riga nella nostra colonna A, restituendo il tipo di camion per l'ID specifico. Quindi, alla fine, otteniamo gli stessi risultati con un po' più di flessibilità negli input.

I vantaggi della funzione

La potenza di INDEX + MATCH deriva dalla selezione di dati più piccola. Se hai 15 colonne ma stai usando solo i numeri 1 e 15, perché inserire tutto il resto? Questo può aiutare ad accelerare il tempo di esecuzione per grandi set di dati.

La seconda funzionalità avanzata è che Index può leggere a sinistra oa destra, indipendentemente da dove metti le colonne, INDEX è indifferente se si trova a sinistra oa destra dell'array iniziale, a differenza di CERCA.VERT. Se guardi di nuovo l'esempio sopra, è proprio quello che abbiamo fatto, lavorando da destra a sinistra. La funzione può anche fungere da controllo di garanzia della qualità poiché è necessario specificare manualmente le due colonne necessarie anziché inserire un numero di colonna relativo da qualche parte all'interno della selezione.

Hai bisogno di più immagini? Guarda una registrazione dello schermo di un esempio di Index Match in cui inseriamo gli URL finali in un modello di testo pubblicitario.

Conclusione

Vai avanti e gioca con INDEX + MATCH. È una funzione molto potente che può migliorare le tue capacità di ricerca. Una volta fatto clic, sono sicuro che verrai venduto e non guarderai mai indietro. C'è sempre un po' di curva di apprendimento quando si prova qualcosa di nuovo, impegnarsi a utilizzare Index Match per un po'. All'inizio sarà goffo, ma presto vedrai i benefici.

———-
Post aggiornato da Jenna Kelly (data post precedente: 2/3/14)