Come invertire cerca.vert in excel con indice-match?

Per comprendere come funzionano INDICE e CONFRONTA come formula di ricerca inversa, devi scegliere una cosa semplice: Match indica all’indice la posizione (numero di cella) di un valore in una colonna o riga, quindi l’indice restituisce questo valore. valore utilizzando questa posizione (numero di cella) .

Pensatela in questo modo, la funzione MATCH è un agente sotto copertura che trova il criminale e la funzione INDEX è un poliziotto che successivamente arresta quel criminale.

Ma vediamo nel dettaglio come possiamo combinare queste due funzioni. Di seguito è riportata la sintassi di INDEX come sai.

INDICE(matrice, numero_riga, [numero_colonna])

Nella funzione INDICE, l’argomento row_num indica da quale riga deve restituire il valore. Diciamo che se inserisci 4, restituirà il valore della quarta riga.

Per creare la formula di ricerca inversa, dobbiamo sostituire MATCH con argomento_riga di INDICE.

Quando utilizziamo MATCH, cerca il valore nella colonna di ricerca e restituisce il numero di cella di quel valore. Quindi, INDEX utilizza questo numero per determinare la posizione della cella dalla colonna del valore.

Alla fine restituisce il valore di quella cella e ottieni il valore che stai cercando. Ma ora lavoriamo con un esempio reale. Di seguito abbiamo un elenco delle città e i nomi dei dipendenti che vi lavorano.

Qui dobbiamo cercare il nome del dipendente che lavora a Mumbai. Ora, se guardi i dati, nella colonna in cui hai le città, questa è la nostra colonna di ricerca, e nella colonna in cui hai i nomi dei dipendenti, quella è la colonna del tuo valore. E la formula sarà:

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

Suddividiamo questa formula in due parti per capirla.

Parte 1 : Nella prima parte, abbiamo utilizzato la funzione di corrispondenza per cercare il valore “Mumbai” e ha restituito il “5” che è la posizione della cella in cui è presente il valore “Mumbai” nella colonna delle città.

Parte 2 : nella seconda parte, abbiamo utilizzato INDICE e abbiamo fatto riferimento alla colonna del nome del dipendente per trovare il valore. Qui la funzione indice sa che desideri il valore della quinta cella della colonna. Quindi, ha restituito “Siya” nel risultato.

Altri esempi di INDICE e corrispondenza

Abbiamo davanti a noi alcuni dei problemi più comuni che abbiamo risolto utilizzando la formula INDEX MATCH. Accedi ai file di esempio: assicurati di scaricare questi file di esempio da qui per seguirli insieme a ciascun esempio.

1. Ricerca di base con INDICE – MATCH

Una ricerca normale è uno dei compiti più importanti che devi svolgere con le formule di ricerca e INDEX MATCH è perfetto per questo. Qui abbiamo una tabella dati con ID e nome del dipendente. Ogni ID è univoco ed è necessario cercare il nome del dipendente con il suo ID.

Supponiamo che tu voglia cercare il nome EMP-132. Per questo, la formula sarà:

 =INDEX(name_column,MATCH(emp-id,emp-id_column,0))

ecco come funziona questa formula

Innanzitutto , MATCH corrisponde all’id emp nella colonna emp id e restituisce il numero di cella dell’id che stai cercando. Qui il numero di riga è 6.

Successivamente INDICE restituisce il nome del dipendente dalla colonna del nome utilizzando lo stesso numero di cella.

2. Guarda a sinistra

CERCA.VERT non può andare a sinistra durante la ricerca di un valore. Come ho già detto, in INDEX e MATCH puoi cercare in qualsiasi direzione. Nella tabella dati seguente è presente la colonna del numero di fattura dopo la colonna dell’importo.

Pertanto, se desideri cercare l’importo di una determinata fattura, ciò non è possibile con CERCA.VERT. In CERCA.VERT quando selezioni una tabella, la prima colonna in quella tabella sarà la colonna di ricerca.

Ma qui in questa tabella dobbiamo utilizzare l’ultima colonna della tabella come colonna di ricerca. Quindi pollice in giù per CERCA.VERT qui. Chiamiamo INDEX e MATCH per il salvataggio e la formula sarà:

 =INDEX(G2:G14,MATCH(L6,J2:J14,0),0)

… ecco come funziona questa formula

• Prima di tutto hai fatto riferimento alla colonna dell’importo nella funzione indice. Questa è la colonna da cui dobbiamo ottenere il valore.
• In secondo luogo , nell’argomento numero_riga della funzione indice, hai utilizzato la funzione di corrispondenza e hai specificato il numero della fattura, fatto riferimento alla colonna della fattura e hai utilizzato zero per la corrispondenza esatta.
• In terzo luogo , la funzione di corrispondenza restituisce il numero di cella della fattura dall’intervallo.

E alla fine, INDEX utilizza questo numero per restituire l’importo posizionando la cella dalla colonna dell’importo.

3. Ricerca fuzzy

Proprio come CERCA.VERT, puoi anche utilizzare INDICE/MATCH per una ricerca approssimativa.

La ricerca fuzzy può essere utile quando il valore che stai cercando non è elencato e desideri ottenere la corrispondenza più vicina. Nella tabella seguente è disponibile un elenco dei voti in base ai voti.

E, se vuoi ottenere un punteggio di 79, puoi utilizzare la formula seguente.

 =INDEX(B2:B6,MATCH(D3,A2:A6,1))

… ecco come funziona questa formula

In questa formula, abbiamo utilizzato 1 nella funzione match per match_type che gli consente di eseguire una ricerca approssimativa. Restituisce il primo valore minore o uguale al valore di ricerca.

Per 79, il primo valore più basso è 75, mentre per 75 il voto è B. Ecco perché ottieni una B nel risultato.

4. RICERCA orizzontale

Come sai, HLOOKUP è per la ricerca orizzontale, ma puoi anche utilizzare INDEX e MATCH per questo. Qui nella tabella dati qui sotto hai una tabella orizzontale per le vendite mensili e vuoi ottenere il valore delle vendite per “Maggio”.

E la formula sarà:

 =INDEX(amount,0,MATCH(lookup_month,months,0))

… ecco come funziona questa formula

Nella formula precedente, invece di utilizzare CONFRONTA nell’argomento row_num dell’indice, lo abbiamo utilizzato in column_num. Inoltre, la corrispondenza restituisce il numero di colonna di maggio.

E poi INDICE restituisce il valore della colonna dei risultati in base al numero di posizione.

5. Ricerca bidirezionale

In una ricerca bidirezionale, dobbiamo ottenere un valore da una tabella. Basta guardare la tabella qui sotto dove hai gli importi delle vendite per zona e per prodotto.

Ora, se vuoi ottenere l’importo delle vendite di un prodotto per una particolare area, hai bisogno di una ricerca bidirezionale e per questo devi utilizzare una combinazione di INDEX MATCH MATCH. Sì, qui devi usare MATCH due volte.

In una normale combinazione di INDICE e CONFRONTA, si utilizza CONFRONTA per il numero di riga, ma in una ricerca bidirezionale è necessario utilizzarlo anche per il numero di colonna. Le formule saranno:

 =INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))

… ecco come funziona questa formula

Nella formula precedente, per ottenere l’importo delle vendite dalla tabella, hai utilizzato la funzione indice e quindi la funzione di corrispondenza per gli argomenti row_num e column_num. La funzione di corrispondenza che è l’argomento column_num restituisce 5 come valore Product-D nella quinta riga dell’intervallo a cui fai riferimento.

Inoltre, la funzione di corrispondenza trovata nell’argomento row_num restituisce 2 perché il valore della zona nord si trova nella seconda colonna dell’intervallo a cui fai riferimento.

Ora, con questi valori, la funzione indice ha restituito il valore che si trova nella seconda colonna e nella quinta riga: 1456.

6. Distinzione tra maiuscole e minuscole

Se riscontri un problema quando hai due stessi valori in un elenco o colonna ma in lettere maiuscole e minuscole, puoi eseguire una ricerca con distinzione tra maiuscole e minuscole per trovare il valore corretto. Diamo un’occhiata all’elenco degli studenti qui sotto dove hai il nome e nella seconda colonna hai segnato.

E, all’inizio, ci sono nomi che sono gli stessi ma in casi testuali diversi. Ad esempio, John Parker e JOHN Mathew. Supponiamo che tu voglia cercare le marche di “JOHN” e non “John”, puoi creare una ricerca con corrispondenza esatta con INDICE e MATCH. E la formula sarà:

 =INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))

… ecco come funziona questa formula

Qui in questa formula hai utilizzato la funzione ESATTA nella funzione di corrispondenza. Poiché la funzione di corrispondenza non è in grado di cercare un valore con distinzione tra maiuscole e minuscole e EXACT è una funzione perfetta per questo.

Può confrontare due valori e restituire VERO se sono esattamente uguali (compreso il caso), ma è necessario inserire questa formula in forma tabellare perché è necessario confrontare l’intera colonna con un singolo valore in ESATTO. Quando lo inserisci, restituirà un array come questo.

 =INDEX(Sheet1!$B$2:$B$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))

Da lì è necessario utilizzare la funzione match per ottenere la posizione TRUE dall’array.

A questo punto hai il numero di cella (numero di riga) del valore che devi trovare. Inoltre, puoi utilizzare INDEX per questo valore di colonna.

Punto importante: se ottieni più di un VERO con EXACT, la corrispondenza restituirà semplicemente il numero per il primo VERO

7. Con caratteri jolly con INDEX MATCH

I caratteri jolly sono molto utili. È possibile eseguire una ricerca parziale utilizzando i caratteri jolly . E la parte migliore è che, come tutte le altre ricerche con formula, puoi anche utilizzare caratteri jolly con indice e corrispondenza.

Dai un’occhiata all’elenco dei nomi qui sotto dove trovi i nomi e i cognomi dei dipendenti e la loro età.

Da questo elenco è necessario ricavare l’età di un particolare dipendente (Sondra). Ma il fatto è che conosci solo il nome.

E, se usi un asterisco, puoi cercare l’età di Sondra utilizzando il nome. Per questo, la formula sarà:

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

… ecco come funziona questa formula

Un asterisco è un carattere jolly che può sostituire n numero di caratteri. Quindi quando lo hai usato dopo il nome, ha sostituito il cognome.

8. Valore più basso

Supponiamo che tu abbia un elenco di studenti con i loro punteggi come di seguito. E ora da questa lista vuoi cercare il nome dello studente che ha il punteggio più basso.

Per questo, puoi utilizzare la funzione MIN con indice e corrispondenza e la formula sarà:

 =INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))

Hai i nomi degli studenti nella colonna A e i loro punteggi nella colonna B.

Quindi quando inserisci questa formula in una cella e premi Invio, verrà restituito il nome dello studente con il punteggio più basso, ovvero Librada Bastian.

Spiegazione

In questa formula abbiamo tre parti diverse.

Nella prima parte, la funzione MIN restituisce il punteggio più basso.

Successivamente, nella seconda parte, la funzione di corrispondenza restituisce la cella con il punteggio più basso.

Alla fine, la funzione indice restituisce il valore della colonna del nome dello studente utilizzando la stessa posizione della cella restituita dalla corrispondenza.

Suggerimento: Allo stesso modo puoi anche ottenere il nome dello studente che ottiene il punteggio più alto.

9. Miglior ennesimo punteggio

Ora pensa in questo modo, hai un elenco di studenti con i loro punteggi degli esami e da questo elenco vuoi ottenere il nome dello studente che ha ottenuto il 2° punteggio più alto.

Il fatto è che non sai qual è il secondo punteggio più alto.

Normalmente quando cerchi un valore con le formule di ricerca hai la certezza del valore che stai cercando. Ma qui non sai qual è il secondo punteggio più alto.

Quindi per questo puoi combinare una funzione di grandi dimensioni con un indice e abbinarlo. La funzione grande ti aiuterà a determinare il secondo valore più alto nell’intervallo.

E la formula sarà:

 =INDEX(student_names,MATCH(LARGE(score,2),score,0))

… ecco come funziona questa formula

In questa formula hai utilizzato la funzione large all’interno della funzione match per l’argomento lookup_value. E nella grande funzione hai menzionato l’intervallo di punteggio e 2 per ottenere il secondo valore più alto.

Dopo che la funzione large restituisce il secondo valore più grande, la funzione di corrispondenza utilizza quel valore e restituisce il numero di cella corrispondente.

E alla fine, la funzione indice utilizza questo numero di cella e restituisce il nome dello studente.

10. Criteri multipli

Normalmente la combinazione di indice e corrispondenza ha lo scopo di cercare un singolo valore. Ed è per questo che usi un solo intervallo nella funzione di corrispondenza.

Ma a volte, quando ti trovi di fronte ai dati nel mondo reale, devi utilizzare più criteri per trovare un valore.

Considera l’esempio seguente. Qui hai un elenco di prodotti con diversi dettagli come nome del prodotto, categoria e taglia.

E da questi dati vuoi ottenere il prezzo di un particolare prodotto utilizzando tutti i criteri.

La formula sarà quindi:

 =INDEX(D2:D11,MATCH(1,(A14=A2:A11)*(B14=B2:B11)*(C14=C2:C11),0))

Nota: questa è una formula di matrice, quindi devi inserirla usando ctrl + maiusc + invio.

… ecco come funziona questa formula

In questa formula hai tre diversi array per far corrispondere tre diversi valori e questi array restituiscono TRUE e FALSE dove i valori corrispondono.

Dopodiché, quando li moltiplichi tra loro, ottieni un array o qualcosa del genere.

 =INDEX(D2:D11,MATCH(1,{0;0;0;0;0;0;1;0;0;0},0))

E la funzione match restituisce la posizione 1 per l’array.

Alla fine, INDICE restituisce il prezzo della colonna prezzo utilizzando il numero restituito dalla corrispondenza.

Suggerimento: se non desideri utilizzare una formula di matrice, puoi utilizzare la condizione SUMPRODUCT .

11. Primo valore numerico di un intervallo

Supponiamo che tu abbia un elenco in cui sono presenti sia valori di testo che valori numerici e ora da questo elenco desideri ottenere il primo valore numerico.

Per fare ciò, puoi combinare la funzione ISNUMBER con indice/corrispondenza. ISNUMBER può aiutarti a identificare quale valore è un numero e quale è testo.

La formula sarà:

 =INDEX(list,MATCH(TRUE,ISNUMBER(list),0))

È necessario inserire questa formula sotto forma di tabella (utilizzando Ctrl + Maiusc + Invio).

… ecco come funziona questa formula

In questa formula ISNUMBER restituisce un array uguale alla lunghezza dell’elenco e in questo array hai TRUE per i valori che sono numeri e FALSE per il resto dei valori.

Successivamente hai utilizzato TRUE nella funzione di corrispondenza come valore di ricerca. Quindi restituisce il numero di posizione del primo TRUE nell’array.

In definitiva, l’utilizzo di questo indice del numero di posizione restituisce il primo valore numerico.

12. Ottieni il primo valore non vuoto

Pensiamo in questo modo, hai un elenco di valori in cui alcune delle prime celle sono vuote e vuoi ottenere il primo valore non vuoto.

E puoi usare questa formula per ottenere questo primo valore non vuoto.

 =INDEX(A2:A11,MATCH(FALSE,ISBLANK(A2:A11),0))

… ecco come funziona questa formula

Dobbiamo dividere questa formula in tre parti diverse per comprenderla saggiamente.

Innanzitutto , hai utilizzato la funzione ISBLANK nella funzione di corrispondenza per ottenere un array in cui hai TRUE per le celle vuote e FALSE per le celle non vuote.

In secondo luogo , MATCH restituisce il numero di posizione del primo TRUE nell’array restituito da ISBLANK.

Quindi a questo punto hai il numero di cella del primo valore non vuoto.

In terzo luogo , la funzione indice restituisce semplicemente il primo valore non vuoto nell’elenco.

13. Testo più frequente

Supponiamo ora che, dato un elenco di valori di testo, sia necessario contare il testo più frequente.

Nell’elenco qui sotto hai dei nomi.

Ma ci sono alcuni nomi che compaiono più di una volta.

Quindi, ora devi ottenere il nome che ha la maggiore occorrenza nell’elenco. Puoi utilizzare la formula seguente che è una combinazione di MODALITÀ, INDICE e CORRISPONDENZA.

 =INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))

Quando inserisci questa formula verrà restituito “Tamesha” che è il nome più comune.

… ecco come funziona questa formula

Per prima cosa , MATCH confronterà l’intero intervallo di nomi con se stesso. E, così facendo, restituirà un array in cui ogni testo rappresenterà la sua prima posizione.

Prendiamo come esempio il nome “Tamesha”, che è il nostro nome più comune nell’elenco. Ora , se guardi l’elenco, questo è successo prima sull’ottava cella e poi sulla dodicesima cella.

Ma, se guardi la tabella, per tutte le posizioni in cui abbiamo “Tamesha”, restituisce 8 che è la sua prima posizione.

Successivamente, dall’array restituito da MATCH, la funzione mode restituirà il numero più frequente che è il numero di cella della prima occorrenza di “ Tamesha ”.

E alla fine, INDEX restituirà il testo utilizzando questo numero di cella.

14. Creare un collegamento ipertestuale

Supponiamo ora che oltre a cercare un valore, desideri anche creare un collegamento ipertestuale per quel valore. In questo modo puoi navigare rapidamente fino alla cella in cui si trova la colonna di ricerca.

Ad esempio, nella tabella seguente è necessario ottenere l’età di una persona. Inoltre, se crei un collegamento ipertestuale per questo valore, puoi facilmente passare alla cella in cui si trova questo valore.

E per questo dobbiamo usare HYPERLINK + Cell con INDEX e MATCH e la formula sarà:

Ecco come funziona questa formula

Separiamo questa formula in più parti per capirla meglio.

• Prima di tutto, hai utilizzato indice e corrispondenza nella funzione cella. Inoltre, quando utilizzi entrambe le funzioni nella funzione di cella, ottieni un riferimento di cella del valore corrispondente anziché del valore corrispondente.
• In secondo luogo , hai concatenato “#” con il riferimento di cella.
• In terzo luogo , hai utilizzato nuovamente l’indice e la corrispondenza per ottenere il valore corrispondente da utilizzare come testo del collegamento. In questo modo hai il valore corrispondente e il collegamento alla cella in cui si trova quel valore.