Formula generica
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Sommario
Per cercare valori con INDICE e CONFRONTA, utilizzando più criteri, è possibile utilizzare una formula di matrice. Nell'esempio mostrato, la formula in H8 è:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO, tranne in Excel 365.
Spiegazione
Questa è una formula più avanzata. Per le basi, vedi Come usare INDICE e CONFRONTA.
Normalmente, una formula INDICE CORRISPONDENZA è configurata con CONFRONTA impostata per esaminare un intervallo di una colonna e fornire una corrispondenza in base a determinati criteri. Senza concatenare i valori in una colonna helper o nella formula stessa, non è possibile fornire più di un criterio.
Questa formula aggira questa limitazione utilizzando la logica booleana per creare un array di uno e zero per rappresentare le righe che corrispondono a tutti e 3 i criteri, quindi utilizzando CONFRONTA per abbinare il primo 1 trovato. L'array temporaneo di uno e zero viene generato con questo frammento:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Qui confrontiamo l'articolo in H5 con tutti gli articoli, la dimensione in H6 con tutte le taglie e il colore in H7 con tutti i colori. Il risultato iniziale sono tre array di risultati VERO / FALSO come questo:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Suggerimento: usa F9 per vedere questi risultati. Basta selezionare un'espressione nella barra della formula e premere F9.
L'operazione matematica (moltiplicazione) trasforma i valori VERO FALSO in 1 e 0:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Dopo la moltiplicazione, abbiamo un singolo array come questo:
(0;0;1;0;0;0;0)
che viene inserito nella funzione MATCH come array di ricerca, con un valore di ricerca di 1:
MATCH(1,(0;0;1;0;0;0;0))
A questo punto, la formula è una formula standard MATCH INDICE. La funzione CONFRONTA restituisce 3 a INDICE:
=INDEX(E5:E11,3)
e INDICE restituisce un risultato finale di $ 17,00.
Visualizzazione di array
Gli array spiegati sopra possono essere difficili da visualizzare. L'immagine sotto mostra l'idea di base. Le colonne B, C e D corrispondono ai dati nell'esempio. La colonna F viene creata moltiplicando le tre colonne insieme. È l'array consegnato a MATCH.
Versione non array
È possibile aggiungere un altro INDICE a questa formula, evitando la necessità di inserire come formula di matrice con control + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
La funzione INDICE può gestire gli array in modo nativo, quindi il secondo INDICE viene aggiunto solo per "catturare" l'array creato con l'operazione logica booleana e restituire nuovamente lo stesso array a MATCH. A tale scopo, INDICE è configurato con zero righe e una colonna. Il trucco della riga zero fa sì che INDICE restituisca la colonna 1 dall'array (che è comunque già una colonna).
Perché vorresti la versione non array? A volte, le persone dimenticano di inserire una formula di matrice con CTRL + MAIUSC + INVIO e la formula restituisce un risultato errato. Quindi, una formula non array è più "a prova di proiettile". Tuttavia, il compromesso è una formula più complessa.
Nota: in Excel 365, non è necessario immettere formule di matrice in un modo speciale.
