Cerca verticale | INDICE e CONFRONTA per risolvere problema della prima colonna (8/10)

samuele agnolin
  • 1 ottobre 2016
  • livello: avanzato   3 minuti
risultato finale cerca vert con indice confronta

Sappiamo che il CERCA.VERT cerca un valore all’interno della PRIMA COLONNA della matrice_tabella. In questa puntata del tutorial sul cerca verticale vediamo cosa fare quando la colonna dove cercare il valore NON è in prima posizione!

Spostiamo la colonna, direte! Sì, ma questo non è sempre possibile. Può essere infatti che siano già state applicate delle funzioni che necessitano di un particolare ordine. Magari proprio dei cerca.vert, che “salterebbero” se cambiassimo l’ordine delle colonne.

Dobbiamo quindi ricorrere a due funzioni alternative al cerca verticale: INDICE e CONFRONTA.

La situazione iniziale è la seguente: il cerca.vert associa il venditore a ciascun cliente sulla base dell’area di appartenenza di venditore e cliente

cerca vert corretto

Introduciamo il problema: cambiamo l’ordine delle colonne della matrice tabella! Il cerca vert non funziona più.

cerca vert problema colonne invertite

Risolviamo il problema con la funzione INDICE, la quale restituisce un valore di una tabella sulla base dell’incrocio di una riga e di una colonna specificate, una specie di battaglia navale.

=INDICE(H2:J6;4;1)

Dalla matrice H2:J6 mi devi restituire il valore che si trova all’intersezione della quarta riga e della prima colonna!

funzione indice

Ora dobbiamo solo cercare di rendere dinamici i parametri 4 e 1, relativi alla riga e alla colonna. Lo facciamo con la funzione CONFRONTA, la quale ci restituisce la posizione di una determinata cella all’interno di un intervallo di celle:

=CONFRONTA(C3;I2:I6;0)

Cerca C3 cioè “Sud” nell’intervallo I2:I6 e restituiscimi la posizione (4). Lo zero finale ha lo stesso significato che ha l’ultimo parametro del cerca.vert, quindi “ricerca esatta”.

funzione confronta

Rendiamo dinamico anche il parametro della colonna, sempre con la funzione CONFRONTA

=CONFRONTA(D2;H2:J2;0)

funzione confronta secondo parametro

Concludiamo annidando le funzioni INDICE e le due funzioni CONFRONTA. Impostando i necessari dollari per bloccare alcuni riferimenti.

=INDICE(H$2:J$6;CONFRONTA(C3;$I$2:$I$6;0);CONFRONTA(D$2;$H$2:$J$2;0))

risultato finale cerca vert con indice confronta

4 Comments

You can post comments in this post.


  • OK – Sei bravissimo a spiegare…

    Ho un altro problema da porti, mi tiene sveglio oltre il dovuto…
    Ho un elenco di nomi ai quali, in altra colonna è premesso un codice di aggregazione.
    Es. in una scuola, seguono la pallavolo Antonio, Gaia e Giovanni, gli altri hanno altri interessi…
    Per strani motivi cerco prima Gaia e, una volta verificato (con il codice di aggregazione) che segue pallavolo ho necessità di avere l’elenco di tutti gli altri amanti dello stesso sport della scuola.
    (naturalmente non posso alterare il database perché è creato da altra procedura…)
    Il “cerca.vert” è funzionale alla ricerca del nominativo e l’altra funzione “confronta” mi permette il giochino del codice premesso al nome…, ma per ottenere il secondo amante della pallavolo (e poi il terzo, il quarto …)

    Chiedo scusa e ringrazio anticipatamente

    Se non puoi rispondermi è uguale… continuerò a cercare.

    Roero

    Roero Nesti 1 anno ago Reply


    • Ciao Roero. Grazie!

      Punterei sulle forme matriciali o sulle pivot. Però, come ti scrivevo in privato, se vuoi girarmi un file provo a darti una soluzione pratica applicata ai tuoi dati reali.

      samuele 1 anno ago Reply


      • VOGLIO CREARE UN FOGLIO PER PREVENTIVI CHE MI PERMETTA DI FARE ALCUNE VARIAIZONI IN AUTOMATICO TRAMITE MENU A TENDINA MAGARI:

        1° IN BASE AL NOME MI DEVE RESTITUIRE I VARI DATI DEL CLIENTE (INDIRIZZO NUMERO, CODICE FISCALE, PARTITA IVA… ECC.). QUESTA PARTE L’HO GIA’ RISOLTA FACENDO CONVALIDA DATI SU UNA TABELLA CHE POSSIAMO DEFINIRE “FOGLIO ANAGRAFICA” IN CUI VORREI INSERIRE ANCHE IL “LISTINO DI RIFERIMENTO”.

        L’ALTRA VARIABILE MI SERVE A FILTRARE I LISTINI DIVERSI (IMMAGINA 2 PRODOTTI SIMILI, A PREZZI DIVERSI A SECONDA DEL NOME FOGLIO… CHE AL PUNTO 1 SI CREA GIA’ FILTRANDO IL NOME…MA EQUIVALE A TABELLE DIVERSE SU CUI NON SO FARE UN CERCA.VERT CHE SI ADEGUI… COME FACCIO AD AUTOMATIZZARLO? DOVE TI SPEDISCO IL FILE?)

        GIOVANNI 4 mesi ago Reply


        • Ciao Giovanni, spedisci pure a samuele@amicoexcel.it

          Se è una cosa che si risolve in tempi ragionevoli, come mi pare di capire, lo faccio e te lo invio. Se richiede codice VBA ti faccio sapere tempi e costi prima di metterci mano.

          samuele 4 mesi ago Reply


Inserisci un commento