=CONFRONTA(E2;A2:A6;0) restituisce la posizione del valore di E2 dentro A2:A6: 1 per la prima cella, 2 per la seconda, e così via. Lo 0 chiede una corrispondenza esatta. CONFRONTA restituisce un numero, non il valore stesso, ed è per questo che di solito viene abbinata a INDICE. In Excel inglese si chiama MATCH, ed è così che la mostra la tabella: =MATCH(E2,A2:A6,0). Puoi scrivere le formule anche in italiano, con il punto e virgola.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | Position | |
| 2 | Apple | Fruit | $1.20 | Bread | 4 | |
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
=CONFRONTA(E2;A2:A6;0)Bread è la quarta cella di A2:A6, quindi F2 mostra 4. Il conteggio parte dalla prima cella dell'intervallo, non dalla riga 1 del foglio: cambia l'intervallo in A1:A6 e la risposta diventa 5. Scrivi Kiwi in E2 e CONFRONTA restituisce #N/D (in inglese #N/A, come lo mostra la tabella).
Sintassi di CONFRONTA e tipi di corrispondenza
=MATCH(lookup_value, lookup_array, [match_type])
| match_type | Trova | L'elenco deve essere |
|---|---|---|
0 | Il primo valore esattamente uguale a lookup_value. Caratteri jolly ammessi per il testo. | in qualsiasi ordine |
1 oppure omesso | Il valore più grande minore o uguale a lookup_value. | in ordine crescente |
-1 | Il valore più piccolo maggiore o uguale a lookup_value. | in ordine decrescente |
Il predefinito è 1, non 0. Un CONFRONTA senza il terzo argomento su un elenco di nomi non ordinato non si lamenta: restituisce una posizione che può appartenere a un'altra riga. Scrivi lo 0 ogni volta che cerchi testo, codici o ID.
Corrispondenza approssimata: in quale fascia cade un valore
Con il tipo 1, CONFRONTA risponde a "quale fascia": la posizione dell'ultima soglia che il valore ha raggiunto. Qui un punteggio di 75 ha superato 0, 60 e 70, quindi è nella fascia 3, voto C.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Min score | Grade | Score | Band | Grade | |
| 2 | 0 | F | 75 | 3 | C | |
| 3 | 60 | D | 59 | 1 | F | |
| 4 | 70 | C | 90 | 5 | A | |
| 5 | 80 | B | 100 | 5 | A | |
| 6 | 90 | A |
=CONFRONTA(D2;$A$2:$A$6;1)59 è ancora sotto 60, quindi è nella fascia 1 (F). 90 corrisponde esattamente all'ultima soglia, e 100 la supera, quindi entrambi finiscono nella fascia 5 (A). Le soglie devono restare in ordine crescente: è questo che permette a CONFRONTA di fermarsi nel punto giusto.
Il tipo -1 è l'immagine speculare per un elenco ordinato dal più grande al più piccolo. Restituisce la posizione del valore più piccolo che è ancora maggiore o uguale al valore cercato: con le misure {50;25;12;5} in una colonna, =CONFRONTA(18;D2:D5;-1) restituisce 2, la misura 25, la più piccola che contiene 18.
Caratteri jolly e maiuscole
CONFRONTA ignora maiuscole e minuscole, e con il tipo 0 accetta i caratteri jolly * (caratteri qualsiasi) e ? (esattamente un carattere). Per un confronto che distingua le maiuscole, confronta l'elenco con IDENTICO (in inglese EXACT) e cerca il primo VERO.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Category | Price | Starts with "C" | 3 |
| 2 | Apple | Fruit | $1.20 | Lower case "milk" | 5 |
| 3 | Pear | Fruit | $1.50 | Exactly "milk" | #N/A |
| 4 | Carrot | Vegetable | $0.80 | ||
| 5 | Bread | Bakery | $2.40 | ||
| 6 | Milk | Dairy | $1.10 |
=CONFRONTA("C*";A2:A6;0)"C*" corrisponde a Carrot, posizione 3. "milk" trova Milk in posizione 5, perché le maiuscole vengono ignorate. La versione con IDENTICO confronta ogni nome con "milk" tenendo conto delle maiuscole, non trova nessun VERO e restituisce #N/D. Cambia A6 in milk e restituisce 5. In Excel 2019 e versioni precedenti, conferma la formula con IDENTICO premendo Ctrl+Maiusc+Invio (Cmd+Maiusc+Invio su Mac); in Excel 2021 e Microsoft 365 basta Invio. Una tilde rende letterale un carattere jolly: "~*" cerca un vero asterisco.
Verificare se un valore è in un elenco
CONFRONTA restituisce un numero quando trova il valore e #N/D quando non lo trova. VAL.NUMERO lo trasforma in VERO o FALSO, ed ecco un rapido test "è nell'elenco?".
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ordered | In stock? | In stock | |
| 2 | Pear | Yes | Apple | |
| 3 | Kiwi | No | Pear | |
| 4 | Milk | Yes | Bread | |
| 5 | Plum | No | Milk | |
| 6 | Bread | Yes |
=SE(VAL.NUMERO(CONFRONTA(A2;$D$2:$D$5;0));"Yes";"No")Pear, Milk e Bread sono in D2:D5, quindi mostrano Yes. Kiwi e Plum mostrano No. In Excel italiano la formula di B2 è =SE(VAL.NUMERO(CONFRONTA(A2;$D$2:$D$5;0));"Yes";"No"). CONTA.SE($D$2:$D$5;A2)>0 dà la stessa risposta; CONFRONTA si ferma al primo risultato, CONTA.SE li conta tutti.
CONFRONTA, CONFRONTA.X e INDICE
CONFRONTA è metà della ricerca classica =INDICE(C2:C6;CONFRONTA(E2;A2:A6;0)), spiegata nella pagina INDICE e CONFRONTA. Excel 2021 e Microsoft 365 aggiungono CONFRONTA.X, che ha come predefinita la corrispondenza esatta, può cercare dall'ultimo elemento verso l'alto e gestisce "il successivo più grande" senza un ordinamento decrescente. CONFRONTA funziona ancora ovunque, anche nei file che devono aprirsi in Excel 2019.
Esercizio: la posizione di un dipendente
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Team | Start year | Name | Position | |
| 2 | Ana | Sales | 2019 | Dev | ||
| 3 | Ben | Support | 2021 | |||
| 4 | Cara | Sales | 2018 | |||
| 5 | Dev | Finance | 2022 | |||
| 6 | Eli | Support | 2020 | |||
| 7 | Fay | Finance | 2023 |
Tocca a te: In F2, restituisci la posizione del nome in E2 nell'elenco della colonna A.
Domande frequenti
Cosa restituisce CONFRONTA in Excel?
Una posizione, non un valore: =CONFRONTA("Bread";A2:A6;0) restituisce 4 quando Bread è la quarta cella di A2:A6. Abbinala a INDICE per ottenere il valore in quella posizione da un'altra colonna.
Che differenza c'è tra i tipi di corrispondenza 0, 1 e -1?
0 trova una corrispondenza esatta in qualsiasi ordine. 1 (il predefinito) trova il valore più grande minore o uguale al valore cercato e richiede l'elenco ordinato in modo crescente. -1 trova il valore più piccolo maggiore o uguale e richiede l'elenco ordinato in modo decrescente.
CONFRONTA distingue maiuscole e minuscole?
No. =CONFRONTA("milk";A2:A6;0) trova Milk. Per un confronto che distingua le maiuscole usa =CONFRONTA(VERO;IDENTICO(A2:A6;"milk");0), che in Excel 2019 e versioni precedenti va confermata con Ctrl+Maiusc+Invio (Cmd+Maiusc+Invio su Mac).
Come si verifica con CONFRONTA se un valore è in un elenco?
CONFRONTA restituisce #N/D quando il valore manca, quindi verifica se ha restituito un numero: =VAL.NUMERO(CONFRONTA(A2;$D$2:$D$5;0)) dà VERO o FALSO. Racchiudila in SE per avere un tuo testo.
