Come utilizzare la funzione INDICE e CONFRONTA in Excel

Questo articolo spiega come usare insieme le funzioni INDICE e CONFRONTA in tutte le versioni di Excel, inclusi Excel 2019 e Microsoft 365.

Cosa sono le funzioni INDICE e CONFRONTA?

INDEX e MATCH sono ricerche in Excel funzioni. Sebbene siano due funzioni completamente separate che possono essere utilizzate da sole, possono anche essere combinate per creare formule avanzate.

La funzione INDICE restituisce un valore o il riferimento a un valore all'interno di una particolare selezione. Ad esempio, potrebbe essere utilizzato per trovare il valore nella seconda riga di un set di dati o nella quinta riga e nella terza colonna.

Anche se INDEX potrebbe essere usato da solo, l'annidamento di MATCH nella formula lo rende un po' più utile. La funzione CONFRONTA cerca un elemento specificato in un intervallo di celle e quindi restituisce la posizione relativa dell'elemento nell'intervallo. Ad esempio, potrebbe essere utilizzato per determinare che un nome specifico è il terzo elemento in un elenco di nomi.

Sintassi e argomenti INDEX e MATCH

Ecco come devono essere scritte entrambe le funzioni affinché Excel le comprenda:

=INDICE(Vettore, numero_riga, [numero_colonna])

• Vettore è l'intervallo di celle che verrà utilizzato dalla formula. Può essere una o più righe e colonne, ad esempio A1:D5. È richiesto.
• numero_riga è la riga nell'array da cui restituire un valore, ad esempio 2 o 18. È obbligatorio a meno che numero_colonna è presente.
• numero_colonna è la colonna nell'array da cui restituire un valore, ad esempio 1 o 9. È facoltativo.

= CORRISPONDENZA(valore di ricerca, lookup_array, [match_type])

• valore di ricerca è il valore in cui vuoi far corrispondere lookup_array. Può essere un numero, un testo o un valore logico digitato manualmente oa cui si fa riferimento tramite un riferimento di cella. Questo è richiesto.
• lookup_array è l'intervallo di celle da esaminare. Può essere una singola riga o una singola colonna, come A2:D2 o G1:G45. Questo è richiesto.
• match_type può essere -1, 0, o 1. Specifica come valore di ricerca è abbinato ai valori in lookup_array (vedi sotto). 1 è il valore predefinito se questo discussione è omesso.

Quale tipo di corrispondenza usare
Tipo di corrispondenza	Cosa fa	Regola	Esempio
1	Trova il valore più grande minore o uguale a valore di ricerca.	Il lookup_array i valori devono essere posti in ordine crescente (es. -2, -1, 0, 1, 2; o A-Z;, o FALSO, VERO.	valore di ricerca è 25 ma manca da lookup_array, quindi viene restituita la posizione del numero successivo più piccolo, ad esempio 22.
0	Trova il primo valore che è esattamente uguale a valore di ricerca.	Il lookup_array i valori possono essere in qualsiasi ordine.	valore di ricerca è 25, quindi restituisce la posizione di 25.
-1	Trova il valore più piccolo maggiore o uguale a valore di ricerca.	Il lookup_array i valori devono essere posti in ordine decrescente (es. 2, 1, 0, -1, -2).	valore di ricerca è 25 ma manca da lookup_array, quindi viene restituita la posizione del numero successivo più grande, ad esempio 34.

Esempio di formule INDICE e CONFRONTA

Prima di esaminare come combinare INDEX e MATCH in un'unica formula, dobbiamo capire come funzionano queste funzioni da sole.

INDICE Esempi

=INDICE(A1:B2,2,2)
=INDICE(A1:B1,1)
=INDICE(2:2,1)
=INDICE(B1:B2,1)

In questo primo esempio, ci sono quattro formule INDICE che possiamo usare per ottenere valori diversi:

• =INDICE(A1:B2,2,2) guarda attraverso A1: B2 per trovare il valore nella seconda colonna e nella seconda riga, che è Stacy.
• =INDICE(A1:B1,1) guarda attraverso A1: B1 per trovare il valore nella prima colonna, che è Jon.
• =INDICE(2:2,1) esamina tutto nella seconda riga per individuare il valore nella prima colonna, che è Tim.
• =INDICE(B1:B2,1) guarda attraverso B1:B2 per individuare il valore nella prima riga, che è Amy.

Esempi di partita

=CONFRONTA("Stacy",A2:D2,0)
= CORRISPONDENZA(14, D1: D2)
=CONFRONTA(14,D1:D2,-1)
=CONFRONTA(13,A1:D1,0)

Ecco quattro semplici esempi della funzione CONFRONTA:

• =CONFRONTA("Stacy",A2:D2,0) sta cercando Stacy nell'intervallo A2: D2 e ritorna 3 come risultato.
• = CORRISPONDENZA(14, D1: D2) sta cercando 14 nell'intervallo D1: D2, ma poiché non si trova nella tabella, CONFRONTA trova il valore successivo più grande minore o uguale a 14, che in questo caso è 13, che è in posizione 1 di lookup_array.
• =CONFRONTA(14,D1:D2,-1) è identico alla formula sopra, ma poiché l'array non è in ordine decrescente come -1 richiede, otteniamo un errore.
• =CONFRONTA(13,A1:D1,0) sta cercando 13 nella prima riga del foglio, che restituisce 4 poiché è il quarto elemento in questo array.

INDEX-MATCH Esempi

Ecco due esempi in cui possiamo combinare INDEX e MATCH in un'unica formula:

Trova riferimento cella nella tabella

=INDICE(B2:B5,CONFRONTA(F1,A2:A5))

Questo esempio nidifica la formula CONFRONTA all'interno della formula INDICE. L'obiettivo è identificare il colore dell'articolo utilizzando il numero dell'articolo.

Se guardi l'immagine, puoi vedere nelle righe "Separate" come verrebbero scritte le formule da sole, ma poiché le stiamo annidando, questo è ciò che sta accadendo:

• CONFRONTA(F1,A2:A5) sta cercando il F1 valore (8795) nel set di dati A2: A5. Se contiamo alla rovescia la colonna, possiamo vedere che è 2, quindi è quello che ha appena scoperto la funzione CONFRONTA.
• L'array INDEX è B2: B5 poiché alla fine stiamo cercando il valore in quella colonna.
• La funzione INDEX potrebbe ora essere riscritta in questo modo poiché 2 è quello che ha trovato MATCH: INDICE(B2:B5, 2, [num_colonna]).
• Da quando numero_colonna è facoltativo, possiamo rimuovere quello che rimane con questo: INDICE(B2:B5,2).
• Quindi ora, questa è come una normale formula INDICE in cui troviamo il valore del secondo elemento in B2: B5, che è rosso.

Ricerca per intestazioni di riga e colonna

=INDICE(B2:E13,CONFRONTA(G1,A2:A13,0),CONFRONTA(G2,B1:E1,0))

In questo esempio di MATCH e INDEX, stiamo eseguendo una ricerca bidirezionale. L'idea è di vedere quanti soldi abbiamo guadagnato Verde articoli in Maggio. Questo è molto simile all'esempio sopra, ma una formula MATCH aggiuntiva è nidificata in INDEX.

• CONFRONTA(G1,A2:A13,0) è il primo elemento risolto in questa formula. sta cercando G1 (la parola "maggio") in A2: A13 per ottenere un determinato valore. Non lo vediamo qui, ma è 5.
• CONFRONTA(SOL2,LA1:MI1,0) è la seconda formula MATCH, ed è molto simile alla prima ma sta invece cercando G2 (la parola "Verde") nelle intestazioni delle colonne a SI1: MI1. Questo si risolve a 3.
• Ora possiamo riscrivere la formula INDICE in questo modo per visualizzare cosa sta succedendo: =INDICE(B2:E13,5,3). Questo sta cercando in tutta la tabella, B2: MI13, per la quinta riga e la terza colonna, che restituisce $180.

Regole PARTITA e INDICE

Ci sono diverse cose da tenere a mente quando si scrivono formule con queste funzioni:

• MATCH non lo è che tiene conto del maiuscolo o minuscolo, quindi le lettere maiuscole e minuscole vengono trattate allo stesso modo durante la corrispondenza dei valori di testo.
• MATCH ritorna #N / A per molteplici ragioni: se match_type è 0 e valore di ricerca non si trova se match_type è -1 e lookup_array non è in ordine decrescente, se match_type è 1 e lookup_array non è in ordine crescente e se lookup_array non è una singola riga o colonna.
• Puoi usare un carattere jolly in valore di ricerca argomento se match_type è 0 e valore di ricerca è una stringa di testo. Un punto interrogativo corrisponde a qualsiasi singolo carattere e un asterisco a qualsiasi sequenza di caratteri (ad es. =CONFRONTA("Gio*",1:1,0)). Per utilizzare MATCH per trovare un vero punto interrogativo o un asterisco, digita prima ~.
• INDICE ritorna #RIF! Se numero_riga e numero_colonna non puntare a una cella all'interno dell'array.

Funzioni di Excel correlate

La funzione CONFRONTA è simile a CERCA, ma CONFRONTA restituisce il posizione dell'oggetto invece dell'oggetto stesso.

CERCA.VERT è un'altra funzione di ricerca che puoi utilizzare in Excel, ma a differenza di CONFRONTA che richiede INDICE per ricerche avanzate, le formule CERCA.VERT richiedono solo quella funzione.