=INDICE(C2:C6;CONFRONTA(F2;A2:A6;0)) trova la riga in cui F2 compare in A2:A6 e restituisce il valore della stessa riga di C2:C6. CONFRONTA (in inglese MATCH) trova la posizione, INDICE (in inglese INDEX) prende il valore in quella posizione. Funziona in ogni versione di Excel, e può cercare verso sinistra. La tabella mostra la formula in inglese, =INDEX(C2:C6,MATCH(F2,A2:A6,0)), ma puoi scriverla anche in italiano, con il punto e virgola.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | P-101 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | P-102 | |||
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
=INDICE(C2:C6;CONFRONTA(F2;A2:A6;0))Cambia F2 in Milk e G2 restituisce $1.10. Cambia C2:C6 in B2:B6 e restituisce invece la categoria.
Come lavorano insieme INDICE e CONFRONTA
La formula fa due passaggi in una sola cella. Qui sono in celle separate, così puoi vedere cosa restituisce ciascuna parte.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Look for | Bread | |
| 2 | Apple | Fruit | $1.20 | P-101 | Position | 4 | |
| 3 | Pear | Fruit | $1.50 | P-102 | Price | $2.40 | |
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
=INDICE(C2:C6;G2)CONFRONTA(G1;A2:A6;0) restituisce 4, perché Bread è il quarto elemento di A2:A6. INDICE(C2:C6;4) restituisce il quarto elemento di C2:C6, $2.40. Metti CONFRONTA dentro INDICE al posto di G2 e hai la formula in una sola cella. Due regole la fanno funzionare:
- I due intervalli devono iniziare sulla stessa riga e avere la stessa altezza.
CONFRONTA(...;A2:A6;0)conta dalla riga 2, quindi INDICE deve leggereC2:C6, nonC1:C6(che restituirebbe la riga sopra). - Termina CONFRONTA con 0. Senza, CONFRONTA fa una corrispondenza approssimata che presuppone la colonna A ordinata, e su un elenco di nomi può restituire la posizione della riga sbagliata. La pagina su CONFRONTA spiega i suoi tre tipi di corrispondenza.
Se il valore non è nell'elenco, CONFRONTA restituisce #N/D (in inglese #N/A, come lo mostra la tabella), e così l'intera formula. =SE.NON.DISP.(INDICE(C2:C6;CONFRONTA(F2;A2:A6;0));"Not found") mostra invece un testo.
Ricerca verso sinistra
CERCA.VERT restituisce colonne a destra di quella in cui cerca. INDICE e CONFRONTA non badano all'ordine: cerca nella colonna D, restituisci la colonna A.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Code | Product | |
| 2 | Apple | Fruit | $1.20 | P-101 | P-310 | Bread | |
| 3 | Pear | Fruit | $1.50 | P-102 | |||
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
=INDICE(A2:A6;CONFRONTA(F2;D2:D6;0))P-310 restituisce Bread. Scrivi P-205 in F2 per avere Carrot. Con CERCA.VERT dovresti prima spostare la colonna Code all'inizio della tabella.
Ricerca bidimensionale: INDICE con due CONFRONTA
INDICE accetta un numero di riga e un numero di colonna. Dagli un'intera tabella e lascia che un CONFRONTA trovi la riga e un altro trovi la colonna.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar |
| 2 | North | 4,200 | 3,900 | 4,800 |
| 3 | South | 3,100 | 3,600 | 3,300 |
| 4 | East | 5,200 | 4,700 | 5,600 |
| 5 | West | 2,800 | 3,000 | 3,400 |
| 6 | Region | East | ||
| 7 | Month | Mar | ||
| 8 | Sales | 5,600 |
=INDICE(B2:D5;CONFRONTA(B6;A2:A5;0);CONFRONTA(B7;B1:D1;0))East è la riga 3 di A2:A5 e Mar è la colonna 3 di B1:D1, quindi INDICE restituisce la riga 3, colonna 3 di B2:D5: 5,600. Il CONFRONTA della riga cerca lungo la prima colonna, il CONFRONTA della colonna cerca lungo la riga delle intestazioni, ed entrambi gli intervalli sono allineati con la tabella B2:D5. In Excel italiano la formula è =INDICE(B2:D5;CONFRONTA(B6;A2:A5;0);CONFRONTA(B7;B1:D1;0)).
Perché INDICE e CONFRONTA batte CERCA.VERT
=VLOOKUP(F2, A2:D6, 3, FALSE)
=INDEX(C2:C6, MATCH(F2, A2:A6, 0))
In Excel italiano: =CERCA.VERT(F2;A2:D6;3;FALSO) e =INDICE(C2:C6;CONFRONTA(F2;A2:A6;0)). Entrambe restituiscono il prezzo. La differenza emerge quando il foglio cambia:
- Inserire una colonna. Inserisci una colonna tra Category e Price, e CERCA.VERT chiede ancora la colonna 3, che ora è la nuova colonna vuota. Nella versione con INDICE Excel adatta
C2:C6inD2:D6e la formula continua a funzionare. - Cercare a sinistra. Mostrato sopra: CERCA.VERT non può, INDICE e CONFRONTA sì.
In Excel 2021 e Microsoft 365, CERCA.X fa entrambe le cose in una sola funzione con argomenti più semplici. INDICE e CONFRONTA restano la scelta per i file che devono aprirsi in Excel 2019 o versioni precedenti, e la parte INDICE è utile anche da sola. Per le ricerche su due condizioni insieme, vedi la ricerca con più criteri.
Esercizio: cerca verso sinistra
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Code | Price | Stock | Product | Look for | Price | |
| 2 | P-101 | $1.20 | 40 | Apple | Milk | ||
| 3 | P-102 | $1.50 | 25 | Pear | |||
| 4 | P-205 | $0.80 | 60 | Carrot | |||
| 5 | P-310 | $2.40 | 15 | Bread | |||
| 6 | P-412 | $1.10 | 30 | Milk |
Tocca a te: I nomi dei prodotti sono nell'ultima colonna. In G2, restituisci il prezzo del prodotto in F2 con INDICE e CONFRONTA.
Esercizio: una ricerca bidimensionale
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Math | Science | Art |
| 2 | Ana | 78 | 85 | 92 |
| 3 | Ben | 64 | 71 | 88 |
| 4 | Cara | 95 | 89 | 73 |
| 5 | Dev | 82 | 67 | 79 |
| 6 | ||||
| 7 | Student | Cara | ||
| 8 | Subject | Science | ||
| 9 | Score |
Tocca a te: In B9, restituisci il voto dello studente in B7 per la materia in B8.
Domande frequenti
Come funziona INDICE e CONFRONTA?
CONFRONTA trova la posizione di un valore in una colonna, e INDICE restituisce il valore in quella posizione in un'altra colonna. In =INDICE(C2:C6;CONFRONTA("Pear";A2:A6;0)), CONFRONTA restituisce 2 perché Pear è il secondo elemento di A2:A6, e INDICE restituisce il secondo elemento di C2:C6.
Perché usare INDICE e CONFRONTA invece di CERCA.VERT?
Può restituire una colonna a sinistra di quella in cui cerca, e non si rompe quando si inserisce una colonna dentro la tabella (non c'è un numero di colonna che diventa vecchio). In Excel 2021 e Microsoft 365, CERCA.X offre gli stessi vantaggi in una sola funzione.
Cosa significa lo 0 in CONFRONTA?
Chiede una corrispondenza esatta. Senza di esso CONFRONTA usa il tipo 1, una corrispondenza approssimata che si aspetta la colonna ordinata in modo crescente, e su un elenco non ordinato può restituire la posizione della riga sbagliata.
Come si fa una ricerca bidimensionale con INDICE e CONFRONTA?
Dai a INDICE un'intera tabella e due CONFRONTA, uno per la riga e uno per la colonna: =INDICE(B2:D5;CONFRONTA("South";A2:A5;0);CONFRONTA("Feb";B1:D1;0)) restituisce il valore all'incrocio tra la riga South e la colonna Feb.
