INDEX e MATCH con più array
Supponiamo che tu abbia diverse tabelle con le stesse didascalie come mostrato di seguito, per cercare i valori che corrispondono ai criteri di assegnazione di queste tabelle potrebbe essere un lavoro difficile per te. In questo tutorial, parleremo di come cercare un valore su più array, intervalli o gruppi abbinando criteri specifici con il INDICE, PARTITA ed SCEGLIERE funzioni.
Come cercare un valore su più array?
Per conoscere il leader di diversi gruppi che appartengono a diversi dipartimenti, puoi prima utilizzare la funzione SCEGLI per scegliere come target la tabella da cui restituire il nome del leader. La funzione MATCH scoprirà quindi la posizione del leader nella tabella a cui appartiene. Infine, la funzione INDICE recupererà il leader in base alle informazioni sulla posizione più la colonna specifica in cui sono elencati i nomi dei leader.
Sintassi generica
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- numero_array: Il numero SCEGLI utilizzato per indicare un array dall'elenco matrice1, matrice2,… per restituire il risultato da.
- matrice1, matrice2,…: Gli array da cui restituire il risultato. Qui si fa riferimento alle tre tabelle.
- valore di ricerca: Il valore utilizzato dalla formula di combinazione per trovare la posizione del relativo leader corrispondente. Qui si riferisce al gruppo dato.
- ricerca_array: L'intervallo di celle in cui valore di ricerca È elencato. Qui si riferisce alla gamma del gruppo. Nota: puoi utilizzare l'intervallo di gruppi di qualsiasi reparto poiché sono tutti uguali e abbiamo solo bisogno di ottenere il numero di posizione.
- numero_colonna: La colonna che indichi da cui vuoi recuperare i dati.
Per conoscere il capogruppo del Girone D che appartiene al Dipartimento A, per favore copia o inserisci la formula sotto nella cella G5 e premi entrare per ottenere il risultato:
=INDICE(SCEGLI(1,$ B $ 5: $ C $ 8,$ B $ 11: $ C $ 14,$ B $ 17: $ C $ 20),INCONTRO(F5,$ B $ 5: $ B $ 8, 0),2)
√ Nota: i simboli del dollaro ($) sopra indicano riferimenti assoluti, il che significa che il nome e gli intervalli di classe nella formula non cambieranno quando sposti o copi la formula in altre celle. Dopo aver inserito la formula, trascina verso il basso il quadratino di riempimento per applicare la formula alle celle sottostanti, quindi modifica il numero_array di conseguenza.
Spiegazione della formula
=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): La funzione SCEGLI restituisce il 1st array dai tre array elencati nella formula. Quindi tornerà $ B $ 5: $ C $ 8, cioè il intervallo di dati del Dipartimento A.
- CONFRONTA(F5,$B$5:$B$8,0): Il match_type 0 forza la funzione CONFRONTA a restituire la posizione della prima corrispondenza di Gruppo D, il valore nella cella F5, nell'array $ B $ 5: $ B $ 8, Che ha 4.
- INDICE(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),CONFRONTA(F5,$B$5:$B$8,0),2) = INDICE($ B $ 5: $ C $ 8,4,2): La funzione INDICE recupera il valore all'intersezione di 4la fila e 2nd colonna dell'intervallo $ B $ 5: $ C $ 8, Che ha Emily.
Per evitare di cambiare numero_array nella formula ogni volta che la copi, puoi usare la colonna helper, la colonna D. La formula sarebbe così:
=INDICE(SCEGLI(D5,$ B $ 5: $ C $ 8,$ B $ 11: $ C $ 14,$ B $ 17: $ C $ 20),INCONTRO(F5,$ B $ 5: $ B $ 8, 0),2)
√ Nota: i numeri 1, 2, 3 nella colonna helper indicare il array1, array2, array3 all'interno della funzione SCEGLI.
Funzioni correlate
La funzione INDICE di Excel restituisce il valore visualizzato in base a una determinata posizione da un intervallo o da una matrice.
La funzione CONFRONTA di Excel cerca un valore specifico in un intervallo di celle e restituisce la posizione relativa del valore.
La funzione SCEGLI restituisce un valore dall'elenco di argomenti del valore in base al numero di indice specificato. Ad esempio, SCEGLI (3, "Mela", "Pesca", "Arancio") restituisce Arancione, il numero di indice è 3 e Arancione è il terzo valore dopo il numero di indice nella funzione.
Formule correlate
Valori di ricerca da un altro foglio di lavoro o cartella di lavoro
Se sai come utilizzare la funzione CERCA.VERT per cercare valori in un foglio di lavoro, i valori di vlookup da un altro foglio di lavoro o cartella di lavoro non saranno un problema per te.
Vlookup con nome foglio dinamico
In molti casi, potrebbe essere necessario raccogliere dati su più fogli di lavoro per il riepilogo. Con la combinazione della funzione CERCA.VERT e della funzione INDIRETTO, puoi creare una formula per cercare valori specifici su fogli di lavoro con il nome del foglio dinamico.
Ricerca a criteri multipli con INDEX e MATCH
Quando si ha a che fare con un grande database in un foglio di calcolo Excel con diverse colonne e didascalie di riga, è sempre difficile trovare qualcosa che soddisfi più criteri. In questo caso, puoi utilizzare una formula di matrice con le funzioni INDICE e CONFRONTA.
I migliori strumenti per la produttività in ufficio
Kutools per Excel: ti aiuta a distinguerti dalla folla
Kutools per Excel vanta oltre 300 funzionalità, Garantirti che ciò di cui hai bisogno sia a portata di clic...
Scheda Office: abilita la lettura e la modifica a schede in Microsoft Office (incluso Excel)
- Un secondo per passare da una dozzina di documenti aperti all'altra!
- Riduci ogni giorno centinaia di clic del mouse, dì addio alla mano del mouse.
- Aumenta la produttività del 50% durante la visualizzazione e la modifica di più documenti.
- Porta schede efficienti in Office (incluso Excel), proprio come Chrome, Edge e Firefox.
