INDEX-MATCH è diventato uno strumento più popolare per Excel poiché risolve la limitazione della funzione CERCA.VERT ed è più facile da usare. La funzione INDICE-MATCH in Excel presenta numerosi vantaggi rispetto alla funzione CERCA.VERT:

1. INDICE e MATCH sono più flessibili e veloci di Vlookup
2. È possibile eseguire la ricerca orizzontale, la ricerca verticale, la ricerca bidirezionale, la ricerca a sinistra, la ricerca con distinzione tra maiuscole e minuscole e persino ricerche basate su più criteri.
3. Nei dati ordinati, INDEX-MATCH è il 30% più veloce di VLOOKUP. Ciò significa che in un set di dati più grande ha più senso una velocità del 30%.

Cominciamo con i concetti dettagliati di ciascun INDICE e MATCH.

Funzione INDICE

La funzione INDICE in Excel è molto potente e allo stesso tempo uno strumento flessibile che recupera il valore in una determinata posizione in un intervallo. In altre parole, restituisce il contenuto di una cella, specificato dall'offset di riga e colonna.

Sintassi:

=INDEX(reference, [row], [column])>

parametri:

riferimento: la matrice di celle in cui eseguire l'offset. Può essere un singolo intervallo o un intero set di dati in una tabella di dati. riga [facoltativo]: il numero di righe di offset. Significa che se scegliamo un intervallo di riferimento della tabella come A1:A5, la cella/contenuto che vogliamo estrarre si trova alla distanza verticale. Qui, per la riga A1 sarà 1, per la riga A2 = 2 e così via. Se diamo riga = 4 allora verrà estratto A4. Poiché la riga è facoltativa, quindi se non specifichiamo alcun numero di riga, estrae intere righe nell'intervallo di riferimento. In questo caso vanno da A1 ad A5. colonna [facoltativo]: il numero di colonne di offset. Significa che se scegliamo un intervallo di riferimento della tabella come A1:B5, la cella/contenuto che vogliamo estrarre si trova alla distanza orizzontale. Qui, per A1 la riga sarà 1 e la colonna sarà 1, per B1 la riga sarà 1 ma la colonna sarà 2 allo stesso modo per A2 riga = 2 colonna = 1, per B2 riga = 2 colonna = 2 e così via. Se diamo riga = 5 e colonna 2, verrà estratto B5. Poiché la colonna è facoltativa, se non specifichiamo alcuna riga n. quindi estrarrà l'intera colonna nell'intervallo di riferimento. Ad esempio, se diamo riga = 2 e colonna vuota, verrà estratto (A2:B2). Se non specifichiamo sia riga che colonna, verrà estratta l'intera tabella di riferimento ovvero (A1:B5).

Tabella di riferimento: La seguente tabella verrà utilizzata come tabella di riferimento per tutti gli esempi della funzione INDICE. La prima cella è in B3 (FOOD) e l'ultima cella diagonale è in F10 (180).

Esempi: Di seguito sono riportati alcuni esempi di funzioni Indice.

Caso 1: Non vengono menzionate righe e colonne.

Comando di input: =INDICE(B3:C10)

Caso 2: Sono menzionate solo le righe.

Comando di input: =INDICE(B3:C10,2)

Caso 3: Vengono menzionate sia le righe che le colonne.

Comando di input: =INDICE(B3:D10,4,2)

Caso 4: Sono menzionate solo le colonne.

Comando di input: =INDICE(B3 : D10 , , 2)

Problema con la funzione INDICE: Il problema con la funzione INDICE è che è necessario specificare righe e colonne per i dati che stiamo cercando. Supponiamo di avere a che fare con un set di dati di machine learning di 10000 righe e colonne, quindi sarà molto difficile cercare ed estrarre i dati che stiamo cercando. Ecco il concetto di Match Function, che identificherà righe e colonne in base ad alcune condizioni.

Funzione CONFRONTA

Recupera la posizione di un elemento/valore in un intervallo. È una versione meno raffinata di CERCA.VERT o CERCA.ORIZZ che restituisce solo le informazioni sulla posizione e non i dati effettivi. CONFRONTA non fa distinzione tra maiuscole e minuscole e non si preoccupa se l'intervallo è orizzontale o verticale.

Sintassi:

=MATCH(search_key, range, [search_type])>

parametri:

search_key: il valore da cercare. Ad esempio, 42, Gatti o I24. intervallo: l'array unidimensionale da cercare. Può essere una singola riga o una singola colonna.eg->A1:A10, A2:D2 ecc. search_type [opzionale]: il metodo di ricerca. = 1 (impostazione predefinita) trova il valore più grande inferiore o uguale a search_key quando l'intervallo è ordinato in ordine crescente.
• = 0 trova il valore esatto quando l'intervallo non è ordinato.
• = -1 trova il valore più piccolo maggiore o uguale a search_key quando l'intervallo viene ordinato in ordine decrescente.

Il numero di riga o il numero di colonna possono essere trovati utilizzando la funzione di corrispondenza e possono essere utilizzati all'interno della funzione di indice, quindi se sono presenti dettagli su un elemento, tutte le informazioni sull'elemento possono essere estratte trovando la riga/colonna dell'elemento utilizzando la corrispondenza quindi nidificandolo nella funzione indice.

Tabella di riferimento: La tabella seguente verrà utilizzata come tabella di riferimento per tutti gli esempi della funzione MATCH. La prima cella è in B3 (FOOD) e l'ultima cella diagonale è in F10 (180)

Esempi: Di seguito sono riportati alcuni esempi della funzione MATCH:

Caso 1: Tipo di ricerca 0, significa corrispondenza esatta.

Comando di input: =CONFRONTA(India meridionale,C3:C10,0)

Caso 2: Tipo di ricerca 1 (predefinito).

Comando di input: =MATCH(Indiano meridionale,C3:C10)

ciao mondo Java

Caso 3: Tipo di ricerca -1.

Comando di input: =PARTITA(India meridionale,C3:C10,-1)

INDICE-MATCH Insieme

Negli esempi precedenti, i valori statici di righe e colonne sono stati forniti nella funzione INDICE. Supponiamo che non vi sia alcuna conoscenza preliminare sulla posizione delle righe e delle colonne, quindi la posizione delle righe e delle colonne può essere fornita utilizzando la funzione CONFRONTA. Questo è un modo dinamico per cercare ed estrarre valore.

Sintassi:

 =INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition],  [Match(SearchKey,Range,Type)/StaticColumnPosition])>

Tabella di riferimento: Verrà utilizzata la seguente tabella di riferimento. La prima cella è in B3 (FOOD) e l'ultima cella diagonale è in F10 (180)

Esempio: Diciamo che il compito è trovare il costo di Masala Dosa. È noto che la colonna 3 rappresenta il costo degli articoli, ma non è nota la posizione nella riga di Masala Dosa. Il problema può essere suddiviso in due fasi:

Passo 1: Trova la posizione di Masala Dosa utilizzando la formula:

 =MATCH('Masala Dosa',B3:B10,0)>

Qui B3:B10 rappresenta la colonna alimentare e 0 significa corrispondenza esatta. Restituirà il numero di riga di Masala Dosa.

Passo 2: Trova il costo di Masala Dosa. Utilizza la funzione INDICE per trovare il costo di Masala Dosa. Sostituendo la funzione MATCH di cui sopra, interroga all'interno della funzione INDICE il punto in cui è richiesta la posizione esatta di Masala Dosa e il numero di colonna del costo è 3 che è già noto.

=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>

Ricerca bidirezionale con INDEX-MATCH insieme

Nell'esempio precedente, la posizione della colonna dell'attributo Costo era hardcoded. Quindi non era completamente dinamico.

Caso 1: Supponiamo che non si conosca anche il numero di colonna di Costo, quindi è possibile ottenerlo utilizzando la formula:

 =MATCH('Cost',B3:F3,0)>

Qui B3:F3 rappresenta la colonna di intestazione.

Caso 2: Quando la riga e il valore della colonna vengono forniti tramite la funzione MATCH (senza fornire un valore statico), si parla di ricerca bidirezionale. Può essere ottenuto utilizzando la formula:

 =INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>

Ricerca a sinistra

Uno dei principali vantaggi di INDICE e CONFRONTA rispetto alla funzione CERCA.VERT è la possibilità di eseguire una ricerca a sinistra. Significa che è possibile estrarre la posizione della riga di un elemento utilizzando qualsiasi attributo a destra e può essere estratto il valore di un altro attributo a sinistra.

Ad esempio, supponiamo di acquistare cibo il cui costo dovrebbe essere di 140 Rs. Indirettamente stiamo dicendo di acquistare Biryani. In questo esempio, il costo è Rs 140/-, è necessario estrarre il cibo. Poiché la colonna Costo è posizionata a destra della colonna Cibo. Se viene applicato CERCA.VERT, non sarà possibile eseguire la ricerca sul lato sinistro della colonna Costo. Ecco perché utilizzando CERCA.VERT non è possibile ottenere il nome del cibo.

Per superare questo svantaggio è possibile utilizzare la funzione INDEX-MATCH Ricerca a sinistra.
Passo 1: Prima estrai la posizione della riga del costo 140 Rs utilizzando la formula:

 =MATCH(140, D3:D10,0)>

Qui D3: D10 rappresenta la colonna Costo in cui viene eseguita la ricerca del numero di riga Costo 140 Rs.

Passo 2: Dopo aver ottenuto il numero di riga, il passaggio successivo consiste nell'utilizzare la funzione INDICE per estrarre il nome dell'alimento utilizzando la formula:

 =INDEX(B3:B10, MATCH(140, D3:D10,0))>

Qui B3:B10 rappresenta la colonna alimentare e 140 è il costo del prodotto alimentare.

Ricerca con distinzione tra maiuscole e minuscole

Di per sé, la funzione MATCH non fa distinzione tra maiuscole e minuscole. Ciò significa che se esiste un nome alimentare DHOKLA e la funzione MATCH viene utilizzata con la seguente parola di ricerca:

1. Dhokla
2. dhokla
3. DhOkLA

Tutto restituirà la posizione della riga di DHOKLA. Tuttavia, la funzione ESATTO può essere utilizzata con INDICE e CONFRONTA per eseguire una ricerca che rispetti le lettere maiuscole e minuscole.

Funzione esatta: La funzione ESATTO di Excel confronta due stringhe di testo, tenendo conto dei caratteri maiuscoli e minuscoli, e restituisce VERO se sono uguali e FALSO in caso contrario. ESATTO fa distinzione tra maiuscole e minuscole.

Esempi:

ESATTO(DHOKLA,DHOKLA): restituirà Vero. ESATTO(DHOKLA,Dhokla): restituirà False. ESATTO(DHOKLA,dhokla): restituirà False. ESATTO(DHOKLA,DhOkLA): restituirà False.

Esempio: Supponiamo che il compito sia cercare il tipo di cibo Dhokla ma distinguendo tra maiuscole e minuscole. Questo può essere fatto usando la formula-

 =INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))>

Qui la funzione EXACT restituirà True se il valore nella colonna B3:B10 corrisponde a Dhokla con lo stesso caso, altrimenti restituirà False. Ora la funzione CONFRONTA verrà applicata nella colonna B3:B10 e cercherà una riga con il valore esatto VERO. Successivamente la funzione INDICE recupererà il valore della colonna C3:C10 (colonna Tipo di cibo) nella riga restituita dalla funzione CONFRONTA.

Ricerca con criteri multipli

Uno dei problemi più complicati in Excel è una ricerca basata su più criteri. In altre parole, una ricerca che corrisponde a più colonne contemporaneamente. Nell'esempio seguente, le funzioni INDICE e CONFRONTA e la logica booleana vengono utilizzate per la corrispondenza su 3 colonne:

1. Cibo.
2. Costo.
3. Quantità.

Per estrarre il costo totale.

Esempio: Diciamo che il compito è calcolare il costo totale della Pasta dove

Cibo: Pasta. Costo: 60. Quantità: 1.

Quindi in questo esempio ci sono tre criteri per eseguire una partita. Di seguito sono riportati i passaggi per la ricerca basata su più criteri-

Passo 1: Per prima cosa abbina la Colonna Alimenti (B3:B10) con la Pasta utilizzando la formula:

 'PASTA' = B3:B10>

Questo convertirà i valori B3:B10 (colonna Alimenti) come booleani. Questo è vero dove il cibo è pasta altrimenti falso.

Passo 2: Successivamente, abbina i criteri di costo nel modo seguente:

 60 = D3:D10>

Ciò sostituirà i valori D3:D10 (colonna dei costi) come booleani. Questo è Vero dove Costo=60 altrimenti Falso.

Passaggio 3: Il passaggio successivo consiste nel far corrispondere il terzo criterio che è Quantità = 1 nel modo seguente:

 1 = E3:E10>

Ciò sostituirà la colonna E3:E10 (colonna Quantità) come Vero dove Quantità = 1 altrimenti sarà Falso.

Passaggio 4: Moltiplicare il risultato del primo, secondo e terzo criterio. Questa sarà l'intersezione di tutte le condizioni e convertirà il booleano Vero/Falso come 1/0.

Passaggio 5: Ora il risultato sarà una colonna con 0 e 1. Qui usa la funzione MATCH per trovare il numero di righe di colonne che contengono 1. Perché se una colonna ha il valore 1, significa che soddisfa tutti e tre i criteri.

Passaggio 6: Dopo aver ottenuto il numero di riga, utilizza la funzione INDICE per ottenere il costo totale di quella riga.

 =INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))>

Qui F3:F10 rappresenta la colonna del costo totale.