Un CERCA.VERT con più condizioni (usando INDICE e CONFRONTA)

Aiutami a crescere, condividi questo articolo...
  • 5
  •  
  •  
  • 1
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
    6
    Shares

Vediamo come simulare un CERCA.VERT con più condizioni usando le funzioni INDICE e CONFRONTA di Excel

cerca.vert usando più condizioni
Aiutami a crescere, condividi questo articolo...
  • 5
  •  
  •  
  • 1
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
    6
    Shares

cerca.vert usando più condizioniQuante volte avete la necessità di cercare un dato con CERCA.VERT con più condizioni? Scommetto che vi accade spesso, lo dico perché è una delle cose che mi vengono chieste più spesso.

Vi mostrerò in questo articolo come ottenere questo risultato usando INDICE e CONFRONTA perché, come sappiamo, CERCA.VERT non permette di definire più parametri per le ricerche.

La base dati di esempio

Prima di tutto definiamo la nostra base dati che sarà la seguente:

A B C D
1 PRODOTTO COLORE / MODELLO TAGLIA CODICE
2 Maglietta Blu S MBS
3 Maglietta Rosso S MRS
4 Maglietta Verde S MVS
5 Maglietta Blu M MBM
6 Maglietta Rosso M MRM
7 Maglietta Verde M MVM
8 Maglietta Blu L MBL
9 Maglietta Rosso L MRL
10 Maglietta Verde L MVL
11 Scarpa Mocassino 38 SM38
12 Scarpa Mocassino 39 SM39
13 Scarpa Mocassino 40 SM40

Come potete vedere abbiamo una lista di prodotti con 2 varianti per ognuno di essi, il colore (o modello), la taglia ed infine il codice. Poniamo adesso di voler ricercare il codice di un prodotto inserendo le caratteristiche usando una maschera simile a questa.

F G
1 Prodotto Maglietta
2 Colore Verde
3 Taglia L
4
5 Codice [qui andrà la formula]

Con il classico CERCA.VERT questo non sarebbe possibile, infatti usando questa funzione possiamo passare un solo parametro relativo ad una sola colonna, la prima dell’intervallo selezionato.

L’alternativa a CERCA.VERT

Possiamo a questo punto pensare ad un approccio diverso, in Excel esiste una funzione chiamata INDICE (qui un approfondimento) che permette di selezionare una cella all’interno di un intervallo passando il numero di riga ed il numero di colonna.

Posizionatevi nella cella G5, corrispondente al valore del “Codice” ed inserite la sequente formula

=INDICE(A1:D13;2;4)

Usando questa formula otterrete il valore della seconda riga e della quarta colonna del nostro intervallo iniziale, quindi il codice MBS.

Ora dobbiamo modificare la nostra formula perché il numero di riga 2 diventi dinamico e rispecchi la riga del prodotto che cerchiamo. Per questo possiamo usare la funzione CONFRONTA (qui c’è un articolo dedicato).

Cerchiamo quindi il primo parametro, il prodotto, usando questa formula

=INDICE(A1:D13;CONFRONTA(G1;A1:A13;0);4)

Il risultato non è cambiato, infatti la nostra formula trova alla prima riga dell’elenco il prodotto cercato e si ferma. Dobbiamo quindi modificarla ulteriormente.

La soluzione finale

ATTENZIONE: da ora in poi questa formula diventa ora MATRICIALE (leggi qui per la definizione) e quindi va confermata con CTRL+MAIUSC+INVIO e non solo con INVIO.

Vediamo ora come aggiungere un secondo parametro modificando questa volta la parte relativa alla funziona CONFRONTA che diventerà ora il cuore della nostra ricerca.

Prima però una piccola premessa: come ho già detto in passato più volte i valori VERO e FALSO sono interpretati da Excel anche come 1 e 0. Perché vi dico questo? Perché ora inizieremo a confrontare i valori di ogni riga del nostro elenco con il valore che stiamo cercando, e otterremo dei VERO se il valore corrisponde e dei FALSO se il valore NON corrisponde, poi li moltiplicheremo tra di loro. Andando avanti vedrete che tutto diventerà più chiaro.

Aggiungiamo quindi il secondo parametro, cerchiamo il colore o modello basandoci sul valore contenuto in G2.

=INDICE(A1:D13;CONFRONTA(1;(A1:A13=G1)*(B1:B13=G2);0);4)

Una volta confermata con CTRL+MAIUSC+INVIO vedrete che la formula verrà rappresentata tra parentesi graffe

{=INDICE(A1:D13;CONFRONTA(1;(A1:A13=G1)*(B1:B13=G2);0);4)}

Ed anche il risultato sarà cambiato, infatti il codice restituito sarà ora MVS, corrispondente al primo prodotto “Maglietta” di colore “Verde”. Ma come funziona questa formula? Per ogni riga vengono confrontati i valori delle colonne con i nostri parametri di ricerca, il risultato di ogni confronto verrà moltiplicato per il risultato del confronto successivo.

Quindi nella seconda riga avremo

(A2="Maglietta")*(B2="Verde") 

che darà come risultato

VERO*FALSO

che corrisponde a

1*0

che da come risultato 0, questo si ripete per ogni riga ottenendo una matrice così composta (partendo dalla riga 1 che contiene i titoli:

0
0
0
1
0
0
1
0
0
1
0
0
0

CONFRONTA cercherà all’interno di essa il primo 1 che corrisponde alla 4 riga e passerà questa informazione a INDICE che diventerà, quindi

=INDICE(A1;D13;4;4)

restituendoci quindi il valore della 4 riga e della 4 colonna del nostro intervallo iniziale, ovvero MVS.

Seguendo questa logica possiamo ora aggiungere il terzo (e quarto, quinto… centesimo) parametro alla formula semplicemente modificando i parametri di CONFRONTA

=INDICE(A1:D13;CONFRONTA(1;(A1:A13=G1)*(B1:B13=G2)*(C1:C13=G3);0);4)

confermare con CTRL+MAIUSC+INVIO ed ecco che avremo ottenuto il risultato sperato.


MESSAGGIO DALL'AUTORE
Hai trovato questo blog utile o interessante? Lascia una tua opinione sul guestbook per farmi sapere cosa ne pensi e come possa migliorarlo.

Conclusioni

Superato lo scoglio iniziale per capirne il funzionamento diventa estremamente semplice utilizzare questa formula.

Scarica qui il file di esempio

Spero di essere stato abbastanza chiaro ed esaustivo nella spiegazione (forse anche troppo), ma se avete dei dubbi non esitate a scriverlo nei commenti, sono sempre a vostra disposizione.

 

Autore: gianfranco

Appassionato di informatica, fotografia e di tecnologia in generale. Esperto nell’uso di Excel e con tanta voglia di condividere le sue conoscenza con il mondo, sempre pronto a dare una mano (ma non più di due volte, altrimenti rimango senza).

11 pensieri riguardo “Un CERCA.VERT con più condizioni (usando INDICE e CONFRONTA)”

  1. Ben fatto! Io avrei fatto lo stesso! Forse però usando l’Indice e Confronta e non il Cerca Verticale, anche se immagino che molta più gente si trovi a suo agio con il cerca verticale

  2. Non riesco a risolvere questo caso:
    ho una tabella cosi composta:
    giorno 15 giorno 16 giorno 17 giorno 18…………………
    1 2 1 1
    in base al giorno la formula deve puntare sul giorno della tabella e sommare da quella data fino alla fine del mese.
    Esempio oggi giorno 16, la formula deve sommare da giorno 16 al giorno 30 (2+1+1…….)

    1. Ciao Rino,
      innanzitutto grazie per aver visitato il mio sito 🙂

      Ecco la tua soluzione, poniamo che tu abbia i nomi dei giorni (giorno 1, giorno 2, etc) nella prima riga, quindi da A1 fino ad AE1 ed i numeri in A2:AE2 come nell’immagine sottostante e che tu inserisca il nome del giorno nella cella A3, in A4

      dovresti mettere la seguente formula nella cella che vuoi:

      =SOMMA(SCARTO(A2;0;CONFRONTA(A3;A1:AE1;0)-1;1;31-CONFRONTA(A3;A1:AE1;0)+1))

      Per semplicità ho dato per assodato che i giorni siano sempre 31, basterà lasciare vuoti quelli che non vuoi usare (es. per febbraio. aprile, giugno, etc)

      Se hai problemi contattami pure 🙂

      A presto

  3. Ciao, io ho un problema simile, ho provato acercare una soluzione on line ma non ho trovato molto o forse non sono stato capace di interpretarlo…. ho 2 tabelle, devo trovare una corrispondenza utilizzando contemporaneamente 2 criteri, faccio un esempio:

    tabella a

    2 2 0 15
    2 3 0 11
    2 4 0 12

    tabella b

    2 2 0 ( quarto valore della prima riga tabella a )
    2 3 0 ( quarto valore della prima riga tabella a )
    2 4 0 ( quarto valore della prima riga tabella a )

    so usare il cerca vert con un valore ma creare una formula tipo cerca valore 1a e 1b e restituiscimi il valor di 1 d mi viene complicato

    Mi aiutate per favore?
    Grazie mille a tutti

  4. Bell’articolo!
    Ho un problema che credo sia risolvibile usando indice e confronta ma non riesco a capire esattamente come
    Ho una tabella con due colonne (Gruppi e Valori). La prima Colonna è strutturata come segue.

    Gruppo1
    Sub1
    Sub2
    Sub3
    Gruppo 2
    Sub1
    Sub2
    Sub3

    I nomi dei sottogruppi sono fissi , quelli dei gruppi cambiano.
    Presumiamo di avere la stessa Colonna 8stessa struttura ma non ordine) in un altro foglio, e voglio combinare I valori. Come faccio a farlo assicurandomi che excel insewrisca il valore corrispondente a due valori (Gruppo e Sub).

    Ho trovato la soluzione creando una seconda Colonna con il gruppo corrispondente per riga, ma volevo sapere se ci fosse una soluzione alternative, senza dover creare/modificare dati.

  5. Paolo ha detto:

    RIPARTIZIONE CAP 00188
    Z. TOPONIMO CIVICO TIPO
    34 ADRIANOPOLI VIA
    28 ARA DELLE ROSE VIA
    30 ARCISATE PIAZZA
    27 ARCORE VIA
    31 ARETUSI GIOVANNI VIA
    57 ARTA TERME VIA
    57 BACCANO VIA
    25 BAGNATICA VIA
    31 BAGOLINO 6 VIA
    31 BAGOLINO 14 VIA
    31 BAGOLINO 32 VIA
    34 BAGOLINO VIA

    INDICE(A3:A13;CONFRONTA(B18;B3:B13;0))
    TOPONIMO CIVICO TIPO ZONA
    arcisate 30
    Buongiorno ho creato questo file che contiene le vide di un cap e la loro zona di appartenenza e vorrei creare una scheda di ricerca dove scrivendo il toponimo il civio e il tipo mi dia la zona di appartenenza, come già vedi ho impostato la formula dell’indice confronto che mi va a dare la zona in base al toponimo, ma per più correttezza vorrei mettere gli altri due confronti (civico e tipo) perché ci sono casi in cui essendo civico e tipo può cambiare zona.
    Come posso fare per inserire gli altri due confronti?ho provato con la formula presente nell’articolo ma mi da errore, in più mi servirebbe che la formula funzioni anche se la scio la casella del civico e del tipo in bianco in quanto solo in alcuni casi mi serve confrontarli tutti e tre insieme.
    Ultima cosa io per ora avrò 7 schede diverse (ossia 7 cap diversi) devo unirli in un’unica scheda oppure posso tenerle separata e dalla scheda di ricerca si può impostare la formula che cerca in tutte le schede?
    Se mi lasci il tuo indirizzo email ti invio il file.
    Ti ringrazio per l’aiuto.

  6. Buongiorno,
    Innanzitutto complimenti per la spiegazione. L’idea è utilissima ed è spiegata benissimo.

    Volevo chiederti se fosse possibile tradurre questa formula excel in codice vba in modo da inserirla in una logica più ampia?

    Ancora complimenti,
    saluti.

  7. Buongiorno,
    innanzitutto grazie perché questa formula mi sta risolvendo moltri di problemi,
    ma, per il mio caso sarebbe perfetta se riuscisse a tirare fuori più risultati che corrispondono agli stessi criteri.
    Ad esempio se ci fossero 2 o 3 codici diversi che corrispondono a: maglietta – verde – S – come posso fare a visualizzare anche il secondo e il terzo codice?
    Grazie

  8. Buongiorno,
    è possibile con questa funzione ottenere più risultati che corrispondono agli stessi criteri.
    Ad esempio se ci fossero 2 o 3 codici diversi che corrispondono a: maglietta – verde – S – come posso fare a visualizzare anche il secondo e il terzo codice?
    Grazie!!!

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *