INDICE+CONFRONTA: Piu Potente di CERCA.VERT nei Casi Difficili

Quando CERCA.VERT non basta

Hai gia letto da qualche parte che CERCA.VERT ha dei limiti. Cerca solo a destra, si rompe se aggiungi colonne, non gestisce criteri multipli. Per molti casi CERCA.X risolve questi problemi in modo piu moderno e leggibile. Ma c'e una combo che esiste da decenni, funziona su qualsiasi versione di Excel e ha una flessibilita che vale la pena conoscere: INDICE e CONFRONTA insieme.

Non e la soluzione piu semplice da scrivere, ma quando capisci come funziona, diventa uno strumento che ti tiri fuori in tante situazioni scomode.

Come funziona CONFRONTA da sola

Prima di combinarle, capiscono cosa fa ognuna separatamente.

CONFRONTA cerca un valore in un intervallo e restituisce la sua posizione (un numero).

Sintassi: =CONFRONTA(valore; intervallo; [tipo])

Il terzo argomento e il tipo di corrispondenza: 0 per corrispondenza esatta (quello che usi quasi sempre), 1 per approssimazione verso il basso, -1 per approssimazione verso l'alto.

Esempio: hai i nomi dei dipendenti in A1:A10. Vuoi sapere in che posizione si trova "Laura Bianchi": =CONFRONTA("Laura Bianchi"; A1:A10; 0) restituisce 4 se Laura e nella quarta riga.

Da sola, CONFRONTA restituisce un numero - non e molto utile. Ma quel numero diventa prezioso quando lo passi a INDICE.

Come funziona INDICE da sola

INDICE prende un intervallo e una posizione (riga e/o colonna) e restituisce il valore che si trova in quella posizione.

Sintassi: =INDICE(intervallo; riga; [colonna])

Esempio: vuoi il valore nella quinta riga della colonna C: =INDICE(C:C; 5) restituisce il valore in C5.

Anche INDICE da sola non e che si usi molto spesso. Il punto e combinarle.

INDICE+CONFRONTA insieme

L'idea e questa: CONFRONTA trova la posizione del valore cercato, e INDICE usa quella posizione per restituire il valore corrispondente da un'altra colonna.

Formula base: =INDICE(colonna_risultato; CONFRONTA(valore_cercato; colonna_ricerca; 0))

Esempio pratico: hai una tabella con i codici prodotto in colonna B e i prezzi in colonna E. Vuoi trovare il prezzo del prodotto "P042": =INDICE(E:E; CONFRONTA("P042"; B:B; 0))

CONFRONTA trova la riga dove si trova "P042" nella colonna B, e INDICE restituisce il valore della stessa riga dalla colonna E.

Questo e equivalente a =CERCA.VERT("P042"; B:E; 4; FALSO) - stesso risultato, piu flessibile.

Caso 1: cercare a sinistra

Il caso classico dove CERCA.VERT fallisce. Hai una tabella con i prezzi in colonna A e i codici prodotto in colonna B. Vuoi cercare per codice (che sta a destra) e trovare il prezzo (che sta a sinistra). Con CERCA.VERT non puoi - la colonna di ricerca deve essere la prima.

Con INDICE+CONFRONTA non c'e nessun vincolo sulla posizione: =INDICE(A:A; CONFRONTA("P042"; B:B; 0))

Cerchi in B (a destra), restituisci da A (a sinistra). Fine.

Caso 2: criteri multipli

Vuoi cercare un record che soddisfa due condizioni contemporaneamente. Ad esempio: nella tabella dipendenti, vuoi trovare lo stipendio di "Mario" che lavora nel reparto "Vendite" - perche ci potrebbero essere piu Mario in azienda.

Questa si chiama formula array e si scrive cosi: =INDICE(C:C; CONFRONTA(1; (A:A="Mario")*(B:B="Vendite"); 0))

La logica: (A:A="Mario") crea un array di VERO/FALSO, (B:B="Vendite") idem. Moltiplicarli insieme (*) fa si che il risultato sia 1 solo dove entrambe le condizioni sono vere. CONFRONTA cerca il primo 1 in quell'array e restituisce la posizione.

Nelle versioni piu recenti di Excel puoi premere Invio normalmente. Nelle versioni piu vecchie (prima di Excel 365) questa formula richiede di confermarla con Ctrl+Shift+Invio invece del semplice Invio - Excel mette le parentesi graffe attorno automaticamente: {=INDICE(...)}. Non aggiungere le parentesi graffe a mano, non funziona.

Caso 3: corrispondenza su piu colonne di risultato

Vuoi restituire sia il nome che il reparto e lo stipendio di un dipendente in un colpo solo? Con INDICE+CONFRONTA puoi usare la stessa posizione trovata da CONFRONTA per estrarre da colonne diverse:

In D2 scrivi il codice cercato. In E2, F2 e G2 metti rispettivamente: =INDICE(B:B; CONFRONTA($D2; A:A; 0)) - nome =INDICE(C:C; CONFRONTA($D2; A:A; 0)) - reparto =INDICE(D:D; CONFRONTA($D2; A:A; 0)) - stipendio

Il riferimento assoluto su $D2 fa si che quando copi la formula nelle celle adiacenti, la colonna di ricerca non si sposta.

INDICE+CONFRONTA vs CERCA.X: cosa scegliere oggi?

Se hai Excel 365 o Excel 2021, CERCA.X gestisce la ricerca a sinistra e il valore predefinito piu semplicemente. Per i criteri multipli, CERCA.X da sola non ci arriva nativamente - li serve ancora la logica array o usare CERCA.X annidata in modo creativo.

INDICE+CONFRONTA resta la scelta quando:

• lavori con file che devono girare su versioni vecchie di Excel
• hai bisogno di criteri multipli e sei gia abituato alla sintassi
• vuoi estrarre risultati da piu colonne con un'unica operazione CONFRONTA

Impara entrambe le tecniche. Non si escludono.

Nella guida alle formule di ricerca e riferimento trovi tutti i dettagli su INDICE, CONFRONTA e le loro combinazioni.