Ricerca con criteri multipli con INDEX e MATCH
Quando si lavora con un database di grandi dimensioni in un foglio di calcolo Excel con diverse colonne e intestazioni di riga, è sempre complicato trovare qualcosa che soddisfi più criteri. In questo caso, puoi utilizzare una formula matriciale con le funzioni INDEX e MATCH.
Come eseguire una ricerca con criteri multipli?
Per scoprire il prodotto che è bianco, di dimensioni medie e con un prezzo di $18 come mostrato nell'immagine sopra, puoi sfruttare la logica booleana per generare un array di 1 e 0 per mostrare le righe che soddisfano i criteri. La funzione MATCH troverà quindi la posizione della prima riga che soddisfa tutti i criteri. Successivamente, INDEX troverà l'ID del prodotto corrispondente sulla stessa riga.
Sintassi generica
=INDEX(intervallo_ritorno,MATCH(1,(valore_criterio1=intervallo_criterio1*valore_criterio2=intervallo_criterio2*(…),0))
√ Nota: Questa è una formula matriciale che richiede di essere inserita con Ctrl + Shift + Enter.
- intervallo_ritorno: L'intervallo da cui vuoi che la formula combinata restituisca l'ID del prodotto. Qui si riferisce all'intervallo degli ID dei prodotti.
- valore_criterio: Il criterio utilizzato per individuare la posizione dell'ID del prodotto. Qui si riferisce ai valori nelle celle h3, H5 e H6.
- intervallo_criterio: Gli intervalli corrispondenti in cui sono elencati i valori_criterio. Qui si riferisce agli intervalli di colore, dimensione e prezzo.
- tipo_corrispondenza 0: Forza MATCH a trovare il primo valore che corrisponde esattamente al valore_ricerca.
Per trovare il prodotto che è bianco, di dimensioni medie e con un prezzo di $18, copia o inserisci la formula seguente nella cella H8 e premi Ctrl + Shift + Enter per ottenere il risultato:
=INDEX(B5:B10,MATCH(1,("Bianco"=C5:C10)*("Medio"=D5:D10)*(18=E5:E10),0))
Oppure, usa un riferimento di cella per rendere la formula dinamica:
=INDEX(B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
Spiegazione della formula
=INDEX(B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
- (h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10): La formula confronta il colore nella cella h3 con tutti i colori nell'intervallo C5:C10; confronta la dimensione in H5 con tutte le dimensioni in D5:D10; confronta il prezzo in H6 con tutti i prezzi in E5:E10. Il risultato iniziale è simile a questo:
{VERO;FALSO;VERO;FALSO;VERO;FALSO}*{FALSO;FALSO;VERO;VERO;VERO;FALSO}*{FALSO;FALSO;FALSO;VERO;VERO;FALSO}.
La moltiplicazione trasformerà i VERO e FALSO in 1 e 0:
{1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
Dopo la moltiplicazione, avremo un singolo array come questo:
{0;0;0;0;1;0}. - MATCH(1,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0),0) = MATCH(1,MATCH(1,{0;0;0;0;1;0},0),0): Il tipo_corrispondenza 0 chiede alla funzione MATCH di trovare la corrispondenza esatta. La funzione restituirà quindi la posizione di 1 nell'array {0;0;0;0;1;0}, che è 5.
- INDEX(B5:B10B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = INDEX(B5:B10B5:B10,5): La funzione INDEX restituisce il quinto valore nell'intervallo degli ID dei prodotti B5:B10, che è 30005.
Funzioni correlate
La funzione INDEX di Excel restituisce il valore visualizzato in base a una data posizione da un intervallo o un array.
La funzione MATCH di Excel cerca un valore specifico in un intervallo di celle e restituisce la posizione relativa del valore.
Formule correlate
Cerca il valore corrispondente più vicino con criteri multipli
In alcuni casi, potrebbe essere necessario cercare il valore corrispondente più vicino o approssimativo basandosi su più di un criterio. Con la combinazione delle funzioni INDEX, MATCH e IF, puoi farlo rapidamente in Excel.
Corrispondenza approssimativa con INDEX e MATCH
Ci sono momenti in cui dobbiamo trovare corrispondenze approssimative in Excel per valutare le prestazioni dei dipendenti, classificare i voti degli studenti, calcolare il costo di spedizione in base al peso, ecc. In questo tutorial, parleremo di come usare le funzioni INDEX e MATCH per recuperare i risultati di cui abbiamo bisogno.
Cerca valori da un altro foglio di lavoro o cartella di lavoro
Se sai come usare la funzione CERCA.VERT per cercare valori in un foglio di lavoro, cercare valori da un altro foglio di lavoro o cartella di lavoro non sarà un problema per te. Il tutorial ti mostrerà come cercare valori da un altro foglio di lavoro in Excel.
Gli strumenti per la produttività di Office migliori
Kutools per Excel - Ti aiuta a distinguerti dalla massa
Kutools per Excel vanta oltre 300 funzionalità, garantendo che ciò di cui hai bisogno sia a portata di clic...
Office Tab - Abilita la lettura e la modifica a schede in Microsoft Office (incluso Excel)
- Un secondo per passare tra decine di documenti aperti!
- Riduce centinaia di clic del mouse ogni giorno, dì addio alla mano del mouse.
- Aumenta la tua produttività del 50% quando visualizzi e modifichi più documenti.
- Porta Schede Efficienti a Office (incluso Excel), proprio come Chrome, Edge e Firefox.