In questo articolo impareremo come utilizzare la funzione INDICE e CONFRONTA per cercare il valore in Excel.
Funzione INDEX & MATCH per sostituire la funzione vlookup
Di solito usiamo la funzione VLOOKUP per trovare un valore conoscendo l'indice della riga e il numero della colonna. Per la funzione CERCA.VERT dobbiamo avere il numero di colonna e i criteri di corrispondenza della riga e la cosa più importante è il valore da scoprire che si trova a destra del valore corrispondente. Ma i dati non sempre lo supportano. Molte volte dobbiamo semplicemente cercare i valori senza conoscere l'indice di riga o di colonna, avendo solo i valori da abbinare. Ad esempio: trovare lo stipendio di un dipendente con ID dipendente. In questo cercheremo l'ID dipendente per l'indice di riga e lo stipendio per l'indice di colonna. Impariamo come eseguire questa ricerca utilizzando INDEX & MATCH.
Come risolvere il problema?
Per capire prima la formula, dobbiamo rivedere un po' le seguenti funzioni
- Funzione INDICE
- Funzione CONFRONTA
Ora creeremo una formula usando le funzioni sopra. La funzione Match restituirà l'indice del valore di ricerca1 nel campo dell'intestazione della riga. E un'altra funzione CONFRONTA restituirà l'indice del valore di ricerca 2 dal campo di intestazione della colonna. I numeri di indice verranno ora inseriti nella funzione INDEX per ottenere i valori sotto il valore di ricerca dai dati della tabella.
Formula generica:
| = INDEX ( data , MATCH ( lookup_value1, row_headers, 0 , MATCH ( lookup_value2, column_headers, 0 ) ) ) |
dati : array di valori all'interno della tabella senza intestazioni
lookup_value1 : valore da cercare in row_header.
intestazioni_riga : matrice dell'indice di riga da cercare.
lookup_value1 : valore da cercare in column_header.
intestazioni_colonne : colonna Matrice indice da cercare.
Esempio:
Le affermazioni di cui sopra possono essere complicate da capire. Quindi capiamo questo usando la formula in un esempio
Qui abbiamo un elenco dei punteggi ottenuti dagli studenti con la loro lista delle materie. Abbiamo bisogno di trovare il punteggio per uno specifico studente (Gary) e soggetto (studi sociali) come mostrato nell'istantanea qui sotto.
Il valore Studente1 deve corrispondere all'array Row_header e il valore Subject2 deve corrispondere all'array Column_header.
Usa la formula nella cella J6:
| = INDICE ( tabella , CONFRONTA ( J5, riga, 0 , CONFRONTA ( J4, colonna, 0 ) ) ) |
Spiegazione:
- La funzione CONFRONTA corrisponde al valore Studente nella cella J4 con l'array di intestazione di riga e restituisce la sua posizione 3 come numero.
- La funzione CONFRONTA corrisponde al valore Oggetto nella cella J5 con l'array di intestazione di colonna e restituisce la sua posizione 4 come numero.
- La funzione INDEX prende il numero di indice di riga e colonna e cerca nei dati della tabella e restituisce il valore corrispondente.
- L'argomento del tipo MATCH è fisso su 0. Poiché la formula estrarrà la corrispondenza esatta.
Qui i valori della formula sono dati come riferimenti di cella e row_header, table e column_header dati come intervalli denominati.
Come puoi vedere nell'istantanea sopra, abbiamo ottenuto il punteggio ottenuto dallo studente Gary in Soggetto Scienze Sociali come 36 . E dimostra che la formula funziona bene e per i dubbi vedere le note sottostanti per capire.
Ora useremo la corrispondenza approssimativa con le intestazioni di riga e le intestazioni di colonna come numeri. La corrispondenza approssimativa prende solo i valori numerici in quanto non è possibile applicarla ai valori di testo
Qui abbiamo un prezzo di valori in base all'altezza e alla larghezza del prodotto. Dobbiamo trovare il prezzo per una specifica altezza (34) e larghezza (21) come mostrato nell'istantanea qui sotto.
Il valore Altezza1 deve corrispondere all'array Row_header e il valore Larghezza2 deve corrispondere all'array Column_header.
Usa la formula nella cella K6:
| = INDICE ( dati , CONFRONTA ( K4, Altezza, 1 , CONFRONTA ( K5, Larghezza, 1 ) ) ) |
Spiegazione:
- La funzione CONFRONTA corrisponde al valore Altezza nella cella K4 con l'array di intestazione di riga e restituisce la sua posizione 3 come numero.
- La funzione CONFRONTA corrisponde al valore Larghezza nella cella K5 con l'array di intestazione di colonna e restituisce la sua posizione 2 come numero.
- La funzione INDEX prende il numero di indice di riga e colonna e cerca nei dati della tabella e restituisce il valore corrispondente.
- L'argomento del tipo CONFRONTA è fisso su 1. Poiché la formula estrarrà la corrispondenza approssimativa.
Qui i valori della formula sono dati come riferimenti di cella e row_header, data e column_header dati come intervalli denominati come menzionato nell'istantanea sopra.
Puoi anche eseguire la ricerca di corrispondenze esatte utilizzando la funzione INDICE e CONFRONTA in Excel. Ulteriori informazioni su Come eseguire la ricerca con distinzione tra maiuscole e minuscole utilizzando la funzione INDICE E CONFRONTA in Excel. Puoi anche cercare le corrispondenze parziali utilizzando i caratteri jolly in Excel.
Come puoi vedere nell'istantanea sopra, abbiamo ottenuto il prezzo ottenuto dall'altezza (34) & Larghezza (21) come 53.10. E dimostra che la formula funziona bene e per i dubbi vedere le note sottostanti per una maggiore comprensione.
Appunti:
- La funzione restituisce l'errore #NA se l'argomento dell'array di ricerca per la funzione CONFRONTA è un array 2D che è il campo di intestazione dei dati…
- La funzione corrisponde al valore esatto poiché l'argomento del tipo di corrispondenza per la funzione CONFRONTA è 0.
- I valori di ricerca possono essere forniti come riferimento di cella o direttamente utilizzando il simbolo di virgolette ( ") nella formula come argomenti.
Spero che questo articolo su Come utilizzare Usa la funzione INDICE e CONFRONTA per il valore di ricerca in Excel sia esplicativo. Trova altri articoli sul calcolo dei valori e le relative formule di Excel qui. Se ti è piaciuto il nostro blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook. Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare, completare o innovare il nostro lavoro e renderlo migliore per te. Scrivici al sito di posta elettronica.
Usa INDEX e MATCH per cercare il valore : La formula INDEX-MATCH viene utilizzata per cercare in modo dinamico e preciso un valore in una determinata tabella. Questa è un'alternativa alla funzione CERCA.VERT e supera le carenze della funzione CERCA.VERT.
Usa CERCA.VERT da due o più tabelle di ricerca : Per cercare da più tabelle possiamo adottare un approccio IFERROR. Per cercare da più tabelle, prende l'errore come un'opzione per la tabella successiva. Un altro metodo può essere un approccio If.
Come eseguire la ricerca con distinzione tra maiuscole e minuscole in Excel : La funzione VLOOKUP di excel non fa distinzione tra maiuscole e minuscole e restituirà il primo valore corrispondente dall'elenco. INDEX-MATCH non fa eccezione, ma può essere modificato per fare distinzione tra maiuscole e minuscole. Vediamo come…
Cerca il testo che appare frequentemente con criteri in Excel : La ricerca appare più frequentemente nel testo in un intervallo che usiamo la funzione INDICE-MATCH con MODALITÀ. Ecco il metodo.
Articoli popolari:
Come usare la funzione SE in Excel : l'istruzione IF in Excel controlla la condizione e restituisce un valore specifico se la condizione è VERA o restituisce un altro valore specifico se FALSO.
Come utilizzare la funzione CERCA.VERT in Excel : questa è una delle funzioni più utilizzate e popolari di excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come utilizzare la funzione SOMMA.SE in Excel : Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.
Come utilizzare la funzione CONTA.SE in Excel : Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.