Formula generica
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Sommario
Per cercare un valore confrontando più colonne, puoi utilizzare una formula di matrice basata su MMULT, TRANSPOSE, COLUMN e INDEX. Nell'esempio mostrato, la formula in H4 è:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
dove "nomi" è l'intervallo denominato C4: E7 e "gruppi" è l'intervallo denominato B4: B7. La formula restituisce il gruppo a cui appartiene ogni nome.
Nota: si tratta di una formula di matrice e deve essere immessa con il tasto di scorrimento del controllo invio.
Spiegazione
Lavorando dall'interno verso l'esterno, i criteri logici utilizzati in questa formula sono:
--(names=G4)
dove nomi è l'intervallo denominato C4: E7. Questo genera un risultato VERO / FALSO per ogni valore nei dati e il doppio negativo costringe i valori VERO FALSO a 1 e 0 per produrre un array come questo:
(0,0,0;1,0,0;0,0,0;0,0,0)
Questa matrice è di 4 righe per 3 colonne, corrispondenti alla struttura dei "nomi".
Viene creato un secondo array con questa espressione:
TRANSPOSE(COLUMN(names)^0))
La funzione COLUMN viene utilizzata per creare un array numerico con 3 colonne e 1 riga e TRANSPOSE converte questo array in 1 colonna e 3 righe. Elevarsi alla potenza di zero converte semplicemente tutti i numeri nella matrice in 1. La funzione MMULT viene quindi utilizzata per eseguire la moltiplicazione di matrici:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
e il risultato va nella funzione MATCH come array, con 1 come valore di ricerca:
MATCH(1,(0;1;0;0),0)
La funzione CONFRONTA restituisce la posizione della prima corrispondenza, che corrisponde alla riga della prima riga corrispondente che soddisfa i criteri forniti. Viene inserito in INDICE come numero di riga, con l'intervallo denominato "gruppi" come matrice:
=INDEX(groups,2)
Infine, INDEX restituisce "Bear", il gruppo a cui appartiene Adam.
Il valore letterale contiene per i criteri
Per verificare la presenza di valori di testo specifici invece di una corrispondenza esatta, è possibile utilizzare insieme le funzioni ISNUMBER e SEARCH. Ad esempio, per abbinare celle che contengono "mela" puoi utilizzare:
=ISNUMBER(SEARCH("apple",data))
Questa formula è spiegata qui.
