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

Aiutami a crescere, condividi questo articolo...
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

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...
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

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).


MESSAGGIO DALL'AUTORE
Aiutami a far conoscere questo blog ad altre persone perché possa aiutare anche loro, condividi questo articolo su Facebook, Twitter, Google+ o Linkedin, non ti costa nulla e mi aiuterà a diffonderlo dandomi l'ispirazione per scrivere altri articoli.

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.

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).

7 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.

Lascia un commento

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