Come utilizzare la funzione INDICE e CONFRONTA in Excel

2024 Autore: Abigail Brown | [email protected]. Ultima modifica: 2023-12-17 06:52

Cosa sapere

La funzione INDEX può essere utilizzata da sola, ma l'annidamento della funzione MATCH al suo interno crea una ricerca avanzata.
Questa funzione nidificata è più flessibile di VLOOKUP e può produrre risultati più velocemente.

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

Quali sono le funzioni INDEX e MATCH?

INDICE e CONFRONTA sono funzioni di ricerca di Excel. 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.

Mentre INDEX potrebbe benissimo essere usato da solo, annidare 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.

INDEX e MATCH Sintassi e argomenti

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

=INDEX(array, row_num, [column_num])

array è l'intervallo di celle che utilizzerà la formula. Può essere una o più righe e colonne, ad esempio A1:D5. È obbligatorio.
row_num è la riga nell'array da cui restituire un valore, ad esempio 2 o 18. È obbligatorio a meno che column_num sia presente.
column_num è la colonna nell'array da cui restituire un valore, ad esempio 1 o 9. È facoltativo.

=MATCH(valore_ricerca, matrice_ricerca, [tipo_corrispondenza])

lookup_value è il valore che vuoi abbinare in lookup_array. Può essere un numero, un testo o un valore logico digitato manualmente o a 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, ad esempio A2:D2 o G1:G45. Questo è richiesto.
match_type può essere -1, 0 o 1. Specifica come lookup_value è abbinato ai valori in lookup_array (vedi sotto). 1 è il valore predefinito se questo argomento viene omesso.

Quale tipo di corrispondenza utilizzare
Tipo di corrispondenza	Cosa fa	Regola	Esempio
1	Trova il valore più grande minore o uguale a lookup_value.	I valori di lookup_array devono essere inseriti in ordine crescente (ad esempio, -2, -1, 0, 1, 2; o A-Z;, o FALSE, TRUE.	lookup_value è 25 ma manca da lookup_array, quindi viene invece restituita la posizione del numero più piccolo successivo, come 22.
0	Trova il primo valore esattamente uguale a lookup_value.	I valori lookup_array possono essere in qualsiasi ordine.	lookup_value è 25, quindi restituisce la posizione di 25.
-1	Trova il valore più piccolo maggiore o uguale a lookup_value.	I valori lookup_array devono essere inseriti in ordine decrescente (ad es. 2, 1, 0, -1, -2).	lookup_value è 25 ma manca da lookup_array, quindi viene invece restituita la posizione del numero più grande successivo, come 34.

Usa 1 o -1 per le volte in cui devi eseguire una ricerca approssimativa lungo una scala, come quando hai a che fare con i numeri e quando le approssimazioni vanno bene. Ma ricorda che se non specifichi match_type, 1 sarà il valore predefinito, il che può distorcere i risultati se desideri davvero una corrispondenza esatta.

Esempio di formule INDEX e MATCH

Prima di vedere come combinare INDICE e CONFRONTA in un'unica formula, dobbiamo capire come funzionano queste funzioni da sole.

Esempi INDICE

=INDICE(LA1:B2, 2, 2)

=INDICE(LA1:B1, 1)

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

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

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

Esempi di PARTITA

=CONFRONTA("Stacy", LA2:D2, 0)

=CONFRONTA(14, RE1:RE2)

=CONFRONTA(14, RE1:RE2, -1)=PARTITA(13, LA1:RE1, 0)

Ecco quattro semplici esempi della funzione MATCH:

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

INDEX-MATCH Esempi

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

Trova il riferimento della cella nella tabella

=INDICE(SI2:SI5, CORRISPONDENZA(FA1, LA2:LA5))

Questo esempio sta annidando la formula CORRISPONDENZA 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 dato che le stiamo annidando, ecco cosa sta succedendo:

MATCH(F1, A2:A5) cerca il valore F1 (8795) nel set di dati A2:A5. Se contiamo alla rovescia nella 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 ora può essere riscritta in questo modo poiché 2 è ciò che ha trovato MATCH: INDEX(B2:B5, 2, [column_num]).
Dato che column_num è opzionale, possiamo rimuoverlo per rimanere con questo: INDEX(B2:B5, 2).
Quindi ora, è come una normale formula INDEX in cui troviamo il valore del secondo elemento in B2:B5, che è rosso.

Ricerca per intestazioni di riga e colonna

=INDICE(SI2:MI13, CORRISPONDENZA(SOL1, LA2:LA13, 0), CORRISPONDENZA(SOL2, LA1:MI1, 0))

In questo esempio di MATCH e INDEX, stiamo facendo una ricerca bidirezionale. L'idea è di vedere quanti soldi abbiamo guadagnato con gli articoli verdi a maggio. Questo è molto simile all'esempio sopra, ma una formula MATCH aggiuntiva è nidificata in INDEX.

MATCH(G1, A2:A13, 0) è il primo elemento risolto in questa formula. Sta cercando G1 (la parola "Maggio") in A2:A13 per ottenere un valore particolare. Non lo vediamo qui, ma sono le 5.
MATCH(G2, B1:E1, 0) è la seconda formula MATCH, ed è molto simile alla prima ma cerca invece G2 (la parola "Verde") nelle intestazioni di colonna in B1:E1. Questo si risolve in 3.
Ora possiamo riscrivere la formula INDEX in questo modo per visualizzare cosa sta succedendo: =INDEX(B2:E13, 5, 3). Questo sta cercando nell'intera tabella, B2:E13, la quinta riga e la terza colonna, che restituisce $ 180.

Regole MATCH e INDEX

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

MATCH non fa distinzione tra maiuscole e minuscole, quindi le lettere maiuscole e minuscole vengono trattate allo stesso modo quando si confrontano i valori di testo.
MATCH restituisce N/D per diversi motivi: se match_type è 0 e lookup_value non viene trovato se match_type è -1 e lookup_array non è in ordine decrescente, se match_type è 1 e lookup_array non è ascendente ordine e se lookup_array non è una singola riga o colonna.
Puoi usare un carattere jolly nell'argomento lookup_value se match_type è 0 e lookup_value è una stringa di testo. Un punto interrogativo corrisponde a qualsiasi singolo carattere e un asterisco corrisponde a qualsiasi sequenza di caratteri (ad es.g., =MATCH("Jo", 1:1, 0)). Per utilizzare CONFRONTA per trovare un vero punto interrogativo o un asterisco, digita prima ~.
INDICE restituisce RIF! se row_num e column_num non puntano a una cella all'interno dell'array.

Funzioni di Excel correlate

La funzione CONFRONTA è simile a CERCA, ma CONFRONTA restituisce la posizione dell'elemento anziché l'elemento 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.