CORRISPONDENZA INDICE Excel: ricerche di base e avanzate

In Excel, il recupero accurato di dati specifici è spesso una necessità frequente. Sebbene le funzioni INDICE e CONFRONTA abbiano ciascuna i propri punti di forza, combinandole si sblocca un potente set di strumenti per la ricerca dei dati. Insieme, facilitano una gamma di funzionalità di ricerca, dalle ricerche orizzontali e verticali di base a funzionalità più avanzate come le ricerche bidirezionali, con distinzione tra maiuscole e minuscole e multicriterio. Offrendo funzionalità migliorate rispetto a CERCA.VERT, l'associazione di INDICE e MATCH consente una gamma più ampia di opzioni di ricerca dei dati. In questo tutorial, approfondiamo le possibilità che possono ottenere insieme.

• funzione INDICE
• Funzione MATCH
• Combina INDICE e CONFRONTA

• Ricerca bidirezionale
• Ricerca a sinistra
• Ricerca con distinzione tra maiuscole e minuscole
• Corrispondenza più vicina
• Ricerca con più criteri
• Ricerca su più colonne
• Cerca il primo valore non vuoto
• Ricerca del primo valore numerico
• Ricerca per associazioni MAX o MIN
• Suggerimento: personalizza i tuoi messaggi di errore #N/D

Come usare INDEX e MATCH in Excel

Prima di utilizzare le funzioni INDEX e MATCH, assicuriamoci di sapere come possono INDEX e MATCH aiutarci a cercare prima i valori.

Come utilizzare la funzione INDICE in Excel

Il INDICE la funzione in Excel restituisce il valore in una determinata posizione in un intervallo specifico. La sintassi della funzione INDICE è la seguente:

• schieramento (obbligatorio) si riferisce all'intervallo da cui si desidera restituire il valore.
• numero_riga (richiesto, a meno che numero_colonna è presente) si riferisce al numero di riga dell'array.
• numero_colonna (facoltativo, ma obbligatorio se numero_riga viene omesso) si riferisce al numero di colonna dell'array.

Ad esempio, per sapere il punteggio di Jeff, l' 6studente nell'elenco, puoi utilizzare la funzione INDICE in questo modo:

=INDEX(C2:C11,6)

√ Nota: la gamma C2: C11 è dove sono elencati i punteggi, mentre il numero 6 trova il punteggio dell'esame del 6esimo studente.

Qui facciamo un piccolo test. Per la formula =INDICE(A1:C1,2), che valore restituirà? --- Sì, tornerà Data di nascita, l' 2secondo valore nella riga specificata.

Ora dovremmo sapere che la funzione INDICE può funzionare perfettamente con intervalli orizzontali o verticali. Ma cosa succede se ne abbiamo bisogno per restituire un valore in un intervallo più ampio con più righe e colonne? Bene, in questo caso, dovremmo applicare sia un numero di riga che un numero di colonna. Ad esempio, per scoprirlo Il punteggio di Jeff all'interno dell'intervallo della tabella invece che in una singola colonna, possiamo individuare il suo punteggio con a numero di riga 6 e numero di colonna 3 nel cellule attraverso A2 a C11 come questo:

=INDEX(A2:C11,6,3)

Tuttavia, per un database molto grande con più righe e colonne, non è certamente conveniente per noi applicare la formula con un numero di riga e di colonna esatto. Ed è qui che dovremmo combinare l'uso della funzione MATCH.

Come utilizzare la funzione CONFRONTA in Excel

La funzione CONFRONTA in Excel restituisce un valore numerico, la posizione di un elemento specifico nell'intervallo specificato. La sintassi della funzione CONFRONTA è la seguente:

• valore di ricerca (obbligatorio) si riferisce al valore da abbinare nel file ricerca_array.
• ricerca_array (obbligatorio) si riferisce all'intervallo di celle in cui si desidera che MATCH cerchi.
• tipo_corrispondenza (facoltativo): 1, 0 or -1.
  • 1 (predefinito), CONFRONTA troverà il valore più grande minore o uguale a valore di ricerca. I valori in ricerca_array devono essere inseriti in ordine crescente.
  • 0, MATCH troverà il primo valore che è esattamente uguale a valore di ricerca. I valori in ricerca_array può essere in qualsiasi ordine. (Nei casi in cui il tipo di corrispondenza è impostato su 0, è possibile utilizzare caratteri jolly.)
  • -1, MATCH troverà il valore più piccolo maggiore o uguale a valore di ricerca. I valori in ricerca_array devono essere posti in ordine decrescente.

Ad esempio, per sapere la posizione di Vera nella lista dei nomi, puoi usare la formula MATCH in questo modo:

=MATCH("Vera",A2:A11,0)

√ Nota: Il risultato “4” indica che il nome “Vera” è nella 4a posizione dell'elenco.

Ora che conosciamo gli usi di base delle funzioni INDICE e CONFRONTA in Excel, rimbocchiamoci le maniche e prepariamoci a combinare le due funzioni.

Come combinare INDICE e CONFRONTA in Excel

Si prega di vedere l'esempio seguente per capire come possiamo combinare le funzioni INDICE e CONFRONTA:

Trovare Il punteggio di Evelyn, con la consapevolezza che i punteggi dell'esame sono nella 3terza colonna, possiamo utilizzare la funzione MATCH per determinare automaticamente la posizione della riga senza la necessità di contarlo manualmente. Successivamente, possiamo utilizzare la funzione INDICE per recuperare il file valore all'intersezione della riga identificata e della 3a colonna:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Bene, poiché la formula può sembrare un po' complicata, esaminiamone ogni parte.

Il INDICE la formula contiene tre argomenti:

• numero_riga: CORRISPONDENZA("Evelyn",A2:A11,0) fornisce a INDEX la posizione nella riga del valore "Evelyn" nell'intervallo A2: A11, Che ha 5.
• numero_colonna: 3 specifica il 3terza colonna per INDICE per individuare il punteggio all'interno dell'array.
• schieramento: A2: C11 indica a INDEX di restituire il valore corrispondente all'intersezione della riga e della colonna specificate, all'interno dell'intervallo compreso da Da A2 a C11. Alla fine, otteniamo il risultato 90.

Nella formula sopra, abbiamo utilizzato un valore hardcoded, "Evelyn". Tuttavia, in pratica, i valori codificati non sono pratici, poiché richiederebbero modifiche ogni volta che cerchiamo dati diversi, come il punteggio di un altro studente. In tali scenari, possiamo utilizzare i riferimenti di cella per creare formule dinamiche. Ad esempio, in questo caso, lo farò cambia "Evelyn" in F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Esempi di formule INDICE e MATCH

In questa parte parleremo di diverse circostanze per utilizzare le funzioni INDICE e CONFRONTA per soddisfare esigenze diverse.

INDEX e MATCH per applicare una ricerca bidirezionale

Nell'esempio precedente, conoscevamo il numero di colonna e utilizzavamo una formula CONFRONTA per trovare il numero di riga. Ma cosa succede se non siamo sicuri anche del numero di colonna?

In questi casi, possiamo eseguire una ricerca bidirezionale, nota anche come ricerca nella matrice, utilizzando due funzioni MATCH: una per trovare il numero di riga e l'altra per determinare il numero di colonna. Ad esempio, sapere Il punteggio di Evelyn, dovremmo usare la formula:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX e MATCH per applicare una ricerca a sinistra

Consideriamo ora uno scenario in cui è necessario determinare la classe di Evelyn. Potresti aver notato che la colonna della classe è posizionata a sinistra della colonna del nome, una situazione che supera le capacità di un'altra potente funzione di ricerca di Excel, CERCA.VERT.

In effetti, la capacità di eseguire ricerche sul lato sinistro è uno degli aspetti in cui la combinazione di INDICE e MATCH supera CERCA.VERT.

Trovare La classe di Evelyn, utilizzare la seguente formula per cerca Evelyn in B2:B11 e recuperare il valore corrispondente da A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX e MATCH per applicare una ricerca con distinzione tra maiuscole e minuscole

La funzione MATCH è intrinsecamente insensibile alle maiuscole e alle minuscole. Tuttavia, quando si desidera che la formula distingua tra caratteri maiuscoli e minuscoli, è possibile migliorarla incorporando la funzione ESATTO funzione. Combinando la funzione CONFRONTA con ESATTO in una formula INDICE, puoi quindi eseguire in modo efficace una ricerca con distinzione tra maiuscole e minuscole, come mostrato di seguito:

• schieramento si riferisce all'intervallo da cui si desidera restituire il valore.
• valore di ricerca si riferisce al valore da abbinare, considerando il caso dei caratteri, nel ricerca_array.
• ricerca_array si riferisce all'intervallo di celle con cui vuoi confrontare MATCH valore di ricerca.

Ad esempio, per sapere Il punteggio dell'esame di JIMMY, usa la seguente formula:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Nota: questa è una formula di matrice che richiede di inserire con Ctrl + Shift + entrare, ad eccezione di Excel 365, Excel 2021 e versioni successive.

INDEX e MATCH per trovare la corrispondenza più vicina

In Excel potresti riscontrare situazioni in cui è necessario trovare la corrispondenza più vicina o più vicina a un valore specifico all'interno di un set di dati. In tali scenari, utilizzare una combinazione di funzioni INDICE e CONFRONTA, insieme alle funzioni ABS e MIN, può essere incredibilmente utile.

• schieramento si riferisce all'intervallo da cui si desidera restituire il valore.
• ricerca_array si riferisce all'intervallo di valori in cui si desidera trovare la corrispondenza più vicina valore di ricerca.
• valore di ricerca si riferisce al valore per trovare la corrispondenza più vicina.

Ad esempio, per scoprirlo il cui punteggio è più vicino a 85, utilizzare la seguente formula per cercare il punteggio più vicino a 85 in C2:C11 e recuperare il valore corrispondente da A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Nota: questa è una formula di matrice che richiede di inserire con Ctrl + Shift + entrare, ad eccezione di Excel 365, Excel 2021 e versioni successive.

INDEX e MATCH per applicare una ricerca con più criteri

Per trovare un valore che soddisfi più condizioni, richiedendo la ricerca in due o più colonne, utilizza la seguente formula. La formula ti consente di eseguire una ricerca multicriterio specificando varie condizioni su diverse colonne, aiutandoti a trovare il valore desiderato che soddisfa tutti i criteri specificati.

√ Nota: questa è una formula di matrice che richiede di inserire con Ctrl + Shift + entrare. Verrà quindi visualizzato un paio di parentesi graffe nella barra della formula.

• schieramento si riferisce all'intervallo da cui si desidera restituire il valore.
• (ricerca_valore=ricerca_array) rappresenta una condizione unica. Questa condizione controlla se un particolare valore di ricerca corrisponde ai valori in ricerca_array.

Ad esempio, per trovare il punteggio di Coco di Classe A, la cui data di nascita è il 7/2/2008, puoi utilizzare la seguente formula:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX e MATCH per applicare una ricerca su più colonne

Immagina uno scenario in cui hai a che fare con più colonne di dati. La prima colonna funge da chiave per classificare i dati nelle altre colonne. Per determinare la categoria o la classificazione di una voce specifica, dovrai effettuare una ricerca tra le colonne di dati e associarla alla relativa chiave nella colonna di riferimento.

Ad esempio, nella tabella seguente, come possiamo abbinare lo studente Shawn alla sua classe corrispondente utilizzando INDEX e MATCH? Bene, puoi ottenerlo con una formula, ma la formula è piuttosto estesa e può essere difficile da comprendere, per non parlare di ricordare e digitare.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Ecco dove Kutools for Excel's Indicizzare e abbinare su più colonne la funzionalità torna utile. Semplifica il processo, rendendo facile e veloce l'abbinamento di voci specifiche con le categorie corrispondenti. Per sbloccare questo potente strumento e abbinare facilmente Shawn alla sua classe, è semplice scaricare e installare il componente aggiuntivo Kutools per Excel, quindi procedere come segue:

1. Seleziona la cella di destinazione in cui desideri visualizzare la classe corrispondente.
2. Sulla Kutools scheda, fare clic formula Helper > Ricerca e riferimento > Indicizzare e abbinare su più colonne.
3. 
3. Nella finestra di dialogo pop-up, procedere come segue:
   1. Fare clic sul primo pulsante accanto a Ricerca_col per selezionare la colonna contenente le informazioni chiave che desideri restituire, ovvero i nomi delle classi. (Puoi selezionare solo una singola colonna qui.)
   2. Fare clic sul 2° pulsante accanto a Tabella_rng per selezionare le celle in modo che corrispondano ai valori nel selezionato Ricerca_col, cioè i nomi degli studenti.
   3. Fare clic sul 3° pulsante accanto a Valore di ricerca per selezionare la cella contenente il nome dello studente che desideri abbinare alla sua classe, in questo caso Shawn.
   4. Clicchi OK.
   5. 

Risultato

Kutools ha generato automaticamente la formula e vedrai immediatamente il nome della classe di Shawn visualizzato nella cella di destinazione.

INDEX e MATCH per cercare il primo valore non vuoto

Per recuperare il primo valore non vuoto, ignorando gli errori, da una colonna o da una riga, è possibile utilizzare una formula basata sulle funzioni INDICE e CONFRONTA. Tuttavia, se non vuoi ignorare gli errori del tuo intervallo, aggiungi la funzione ISBLANK.

• Ottieni il primo valore non vuoto in una colonna o riga ignorando gli errori:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Ottieni il primo valore non vuoto in una colonna o riga inclusi gli errori:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX e MATCH per cercare il primo valore numerico

Per recuperare il primo valore numerico da una colonna o da una riga, utilizzare la formula basata sulle funzioni INDICE, CONFRONTA e VAL.NUMERO.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX e MATCH per cercare associazioni MAX o MIN

Se è necessario recuperare un valore associato al valore massimo o minimo all'interno di un intervallo, è possibile utilizzare la funzione MAX o MIN insieme alle funzioni INDICE e CONFRONTA.

• INDEX e MATCH per recuperare un valore associato al valore massimo:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX e MATCH per recuperare un valore associato al valore minimo:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• Ci sono due argomenti nelle formule di cui sopra:
• • schieramento si riferisce all'intervallo da cui si desidera restituire le informazioni correlate.
  • ricerca_array rappresenta l'insieme di valori da esaminare o ricercare secondo criteri specifici, ovvero valori massimi o minimi.

Ad esempio, se vuoi determinare chi ha il punteggio più alto, utilizzare la seguente formula:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Suggerimento: personalizza i tuoi messaggi di errore #N/D

Quando lavori con le funzioni INDICE e CONFRONTA di Excel, potresti riscontrare l'errore #N/D quando non viene trovato alcun risultato corrispondente. Ad esempio, nella tabella seguente, quando si tenta di trovare il punteggio di una studentessa di nome Samantha, viene visualizzato l'errore #N/D poiché lei non è presente nel set di dati.

Per rendere i tuoi fogli di calcolo più facili da usare, puoi personalizzare questo messaggio di errore racchiudendo la formula CORRISPONDENZA INDICE nella funzione IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Sopra sono riportati tutti i contenuti rilevanti relativi alle funzioni INDICE e CONFRONTA in Excel. Spero che troverai utile il tutorial. Se desideri esplorare ulteriori suggerimenti e trucchi su Excel, clicca qui per accedere alla nostra vasta raccolta di oltre migliaia di tutorial.