INDICE e CONFRONTA su più colonne

Per cercare un valore confrontandolo su più colonne, una formula di matrice basata sulle funzioni INDICE e CONFRONTA, che incorpora le funzioni MMULT, TRASPOSTA e COLONNA, ti sarà di grande aiuto.

Come cercare un valore confrontandolo su più colonne?

Per compilare la classe corrispondente di ciascuno studente, come mostrato nella tabella precedente — dove le informazioni sono distribuite su più colonne — puoi innanzitutto utilizzare la combinazione delle funzioni MMULT, TRASPOSTA e COLONNA per generare una matrice. In seguito, la funzione CONFRONTA individuerà la posizione del valore cercato, che verrà passata alla funzione INDICE per recuperare il valore desiderato.

Sintassi generica

=INDEX()return_range,(MATCH(1,MMULT(--())))lookup_array=lookup_value),TRANSPOSE(COLUMN()lookup_array)^0)),0)))

√ Nota: questa è una formula di matrice che richiede di essere inserita con Ctrl+Maiusc+Invio.

Per trovare la classe di Jimmy, copiare o digitare la formula seguente nella cella H5 e premere Ctrl+Maiusc+Invioper ottenere il risultato:

=INDICE()$B$5:$B$7,(CONFRONTA(1,MMULT(--())))$C$5:$E$7=G5),TRASPOSTA(COLONNA()$C$5:$E$7)^0)),0)))

√ Nota: I simboli del dollaro ($) sopra indicano riferimenti assoluti, il che significa che gli intervalli di nome e classe nella formula non cambieranno quando si sposta o copia la formula in altre celle. Si noti che non si devono aggiungere simboli del dollaro al riferimento di cella che rappresenta il valore da cercare, poiché lo si desidera relativo quando lo si copia in altre celle. Dopo aver inserito la formula, trascinare il quadratino di riempimento verso il basso per applicare la formula alle celle sottostanti.

Spiegazione della formula

=INDEX()$B$5:$B$7,(MATCH(1,))MMULT()--($C$5:$E$7=G5),TRANSPOSE()COLUMN($C$5:$E$7)^0)),0)))

• --($C$5:$E$7=G5): Questo segmento verifica se ciascun valore nell'intervallo $C$5:$E$7 è uguale al valore nella cella G5, generando una matrice di VERO e FALSO come questa:
  {VERO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO}.
  Il doppio segno meno converte quindi i valori VERO e FALSO in 1 e 0, producendo una matrice come questa:
  {1,0,0;0,0,0;0,0,0}.
• COLONNA($C$5:$E$7): La funzione COLONNA restituisce i numeri di colonna per l'intervallo $C$5:$E$7 in una matrice come questa: {3,4,5}.
• TRASPOSTA()COLONNA($C$5:$E$7)^0)=TRASPOSTA(){3,4,5}^0):Dopo aver elevato a potenza 0, tutti i numeri nella matrice {3,4,5} vengono convertiti in 1: {1,1,1}. La funzione TRASPOSTA trasforma quindi la matrice di colonna in una matrice di riga come questa:{1;1;1}.
• MMULT()--($C$5:$E$7=G5),TRASPOSTA()COLONNA($C$5:$E$7)^0))=MMULT(){1,0,0;0,0,0;0,0,0},{1;1;1}):La funzione MMULT restituisce il prodotto matriciale delle due matrici, ovvero:{1;0;0}.
• CONFRONTA(1,)MMULT()--($C$5:$E$7=G5),TRASPOSTA()COLONNA($C$5:$E$7)^0)),0)=CONFRONTA(1,){1;0;0},0): Il tipo_corrispondenza 0 impone alla funzione CONFRONTA di restituire la posizione della prima corrispondenza di 1 nella matrice {1;0;0}, che è 1.
• INDICE()$B$5:$B$7,(CONFRONTA(1,))MMULT()--($C$5:$E$7=G5),TRASPOSTA()COLONNA($C$5:$E$7)^0)),0))) = INDICE($B$5:$B$7La funzione INDICE restituisce il 1° valore nell'intervallo delle classi $B$5:$B$7, che è A.

Per cercare facilmente un valore abbinandolo su più colonne, è possibile utilizzare anche il nostro add-in professionale per Excel Kutools per Excel.Consulta qui le istruzioni per portare a termine l’operazione.

Funzioni correlate

Formule correlate