INDICE e CORRISPONDENZA su più colonne

Per cercare un valore confrontando su più colonne, una formula matriciale basata sulle INDICE e CORRISPONDENZA che incorpora MMULT, TRASPOSTA, e COLONNA ti sarà utile.

Come cercare un valore confrontando su più colonne?

Per inserire la classe corrispondente di ogni studente come mostrato nella tabella sopra, dove le informazioni sono elencate su più colonne, puoi prima utilizzare il trucco delle funzioni MMULT, TRASPOSTA e COLONNA per produrre un array matrice. Poi la funzione CORRISPONDENZA ti darà la posizione del tuo valore di ricerca, che verrà passato a INDICE per recuperare il valore che stai cercando nell'array.

Sintassi Generica

=INDICE(intervallo_restituito,(CORRISPONDENZA(1,MMULT(--(intervallo_ricerca=valore_ricerca),TRASPOSTA(COLONNA(intervallo_ricerca)^0)),0)))

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

Per trovare la classe di Jimmy, copia o inserisci la formula sottostante nella cella H5, e premi Ctrl + Maiusc + Invio per ottenere il risultato:

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

√ Nota: I segni del dollaro ($) sopra indicano riferimenti assoluti, il che significa che gli intervalli di nomi e classi nella formula non cambieranno quando sposterai o copierai la formula in altre celle. Tieni presente che non dovresti aggiungere segni di dollaro al riferimento di cella che rappresenta il valore di ricerca, poiché vuoi che sia relativo quando lo copi in altre celle. Dopo aver inserito la formula, trascina la maniglia di riempimento verso il basso per applicare la formula alle celle sottostanti.

Spiegazione della formula

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

• --($C$5:$E$7=G5): Questo segmento controlla ogni valore nell'intervallo $C$5:$E$7 se sono uguali al valore nella cella G5, e genera un array VERO e FALSO come questo:
  {TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
  Il doppio negativo convertirà quindi i VERO e FALSO in 1 e 0 per produrre un array come questo:
  {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 un array come questo: {3,4,5}.
• TRASPOSTA(COLONNA($C$5:$E$7)^0) = TRASPOSTA({3,4,5}^0): Dopo aver elevato alla potenza 0, tutti i numeri nell'array {3,4,5} verranno convertiti in 1: {1,1,1}. La funzione TRASPOSTA converte quindi l'array di colonne in un array di righe come questo: {1;1;1}.
• MMULT(MMULT(--($C$5:$E$7=G5),,TRASPOSTA(COLONNA($C$5:$E$7)^0))) = MMULT(MMULT({1,0,0;0,0,0;0,0,0},,{1;1;1})): La funzione MMULT restituisce il prodotto matriciale dei due array come questo: {1;0;0}.
• CORRISPONDENZA(1,MMULT(--($C$5:$E$7=G5),TRASPOSTA(COLONNA($C$5:$E$7)^0)),0) = CORRISPONDENZA(1,{1;0;0},0): Il tipo_corrispondenza 0 forza la funzione CORRISPONDENZA a restituire la posizione della prima corrispondenza di 1 nell'array {1;0;0}, che è 1.
• INDICE($B$5:$B$7$B$5:$B$7,,,(CORRISPONDENZA(1,MMULT(--($C$5:$E$7=G5),,,TRASPOSTA(COLONNA($C$5:$E$7)^0)),0))) = INDICE($B$5:$B$7$B$5:$B$7,,,1): La funzione INDICE restituisce il primo valore nell'intervallo di classe $B$5:$B$7, che è A.

Per cercare facilmente un valore confrontando su più colonne, puoi anche utilizzare il nostro add-in Excel professionale Kutools per Excel. Vedi le istruzioni qui per portare a termine la missione.

Funzioni correlate

Formule correlate