È facile cercare il valore con una chiave univoca in una tabella. Possiamo semplicemente usare la funzione VLOOKUP. Ma quando non hai quella colonna univoca nei tuoi dati e devi cercare in più colonne per abbinare un valore, CERCA.VERT non aiuta.
Quindi, per cercare un valore in una tabella con più criteri utilizzeremo la formula INDEX-MATCH-INDEX.
Formula generica per la ricerca di più criteri
= INDEX(lookup_range,MATCH(1, INDEX((criteria1 =range1)*(criteria2=range2)*(criteriaN=rangeN),0,1),0))
ricerca_intervallo: È l'intervallo da cui si desidera recuperare il valore.
Criteri1, Criteri2, Criterio N: Questi sono i criteri che vuoi abbinare in range1, range2 e Range N. Puoi avere fino a 270 criteri - coppie di range.
Gamma1, gamma2, gammaN : Questi sono gli intervalli in cui corrisponderai ai tuoi rispettivi criteri.
Come funzionerà? Vediamo…
INDEX e MATCH con più criteri Esempio
Qui ho una tabella di dati. Voglio estrarre il nome del cliente utilizzando Data di prenotazione, Costruttore e Area. Quindi qui ho tre criteri e un intervallo di ricerca.
Scrivi questa formula nella cella I4 premi invio.
=INDICE(E2:E16,CONFRONTA(1, INDICE((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0))
Come funziona:
Sappiamo già come funzionano le funzioni INDICE e CONFRONTA in EXCEL, quindi non lo spiegherò qui. Parleremo del trucco che abbiamo usato qui.
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16): La parte principale è questa. Ogni parte di questa istruzione restituisce un array di true false.
Quando i valori booleani vengono moltiplicati, restituiscono un array di 0 e 1. La moltiplicazione funziona come operatore AND. Quindi quando tutti i valori sono veri solo allora restituisce 1 altrimenti 0
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16) Questo ritornerà del tutto
{FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;VERO;VERO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO}* {FALSO;FALSO;FALSO;VERO;VERO;VERO;VERO;VERO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;VERO}* {FALSO;FALSO;FALSO;VERO;FALSO;FALSO;FALSO;VERO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO}
Che si tradurrà in
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
INDICE((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): la funzione INDICE restituirà lo stesso array ({0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}) alla funzione CONFRONTA come matrice di ricerca.
INCONTRO(1,INDICE((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): la funzione CONFRONTA cercherà 1 nell'array {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}. E restituirà il numero di indice del primo 1 trovato nell'array. Che è 8 qui.
INDICE(E2:E16,INCONTRO(1,INDICE((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)): Infine, INDEX restituirà il valore da un determinato intervallo (E2:E16) trovato indice (8).
Semplice????. Spiacenti, non potrei renderlo più semplice.
Soluzione array
Se puoi premere CTRL + MAIUSC + INVIO di conseguenza, puoi eliminare la funzione INDICE interna. Basta scrivere questa formula e premere CTRL + MAIUSC + INVIO.
=INDICE(E2:E16,CONFRONTA(1,(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0))
Formula di matrice generica per la ricerca di criteri multipli
=INDEX(lookup_range,MATCH(1,(criteri1 =range1)*(criteri2=range2)*(criteriN=rangeN),0))
La formula funziona come la spiegazione precedente.
Ho fatto del mio meglio per spiegarlo nel modo più semplice possibile. Ma se non sono stato abbastanza chiaro, fammelo sapere nella sezione commenti qui sotto. A proposito, non è necessario sapere come funziona il motore per guidare un'auto. Hai solo bisogno di sapere come guidarlo. E tu lo sai molto bene.
Come cercare i primi 5 valori con valori duplicati utilizzando INDEX-MATCH in Excel
Come CERCA.VERT più valori in Excel
Come eseguire un VLOOKUP con Dynamic Col Index in Excel
Come utilizzare CERCA.VERT da due o più tabelle di ricerca in Excel
Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività
Come utilizzare la funzione CERCA.VERT in Excel
Come utilizzare la funzione CONTA.SE in Excel
Come utilizzare la funzione SOMMA.SE in Excel