Con CERCA.VERT otteniamo sempre la prima corrispondenza. Lo stesso accade con la funzione INDEX MATCH. Quindi, come facciamo CERCA.VERT seconda corrispondenza o terza o ennesima?

In questo articolo, impareremo come ottenere l’ennesima occorrenza di un valore nell’intervallo.

0055

Formula generica

{=SMALL(IF(range=value,ROW(range)-ROW(first_cell_in_range)+1),n)}

Nota: questo è un link: / excel-array-formas-arrays-in-excel-formula [array formula] . È necessario inserirlo con CTRL + MAIUSC + INVIO . Intervallo: l’intervallo in cui si desidera cercare l’ennesima posizione del valore *.

Valore: il valore di cui stai cercando l’ennesima posizione nella gamma. First_cell_in_range: la prima cella dell’intervallo. Se l’intervallo è A2: A10, la prima cella dell’intervallo è A2. *

n: il numero di valori occorsi.

Vediamo un esempio per chiarire le cose.

===

Esempio: trova la seconda corrispondenza in Excel

Quindi qui ho questo elenco di nomi nell’intervallo Excel A2: A10. Ho chiamato questo intervallo come nomi. Ora voglio ottenere la posizione della seconda occorrenza di “Rony” nei nomi.

0056

Nell’immagine sopra, possiamo vedere che è al settimo posto nell’intervallo A2: A10 (nomi). Ora dobbiamo ottenere la sua posizione utilizzando una formula Excel.

Applicare la formula generica sopra in C2 per cercare la seconda occorrenza di Rony nell’elenco.

{=SMALL(IF(names=“Rony” ,ROW(names)-ROW(A2)+1),2)}

Inseriscilo con CTRL + MAIUSC + INVIO ..

0057

E abbiamo una risposta. Mostra 7, che è corretto. Se si modifica il valore di n in 3, verrà restituito 8. Se si modifica il valore di n maggiore dell’occorrenza del valore nell’intervallo, restituirà l’errore #NUM.

===

Come funziona?

Bene, è abbastanza facile. Vediamo ogni parte una per una.

link: / tips-if-condition-in-excel [IF] (names = “Rony”, link: / lookup-and-reference-excel-row-function [ROW] (names) -`link: / funzione-riga-ricerca-riferimento-Excel [ROW] `(A2) + 1 *)

:

In IF, names = “Rony” restituisce un array di TRUE e FALSE. TRUE ogni volta che una cella nei nomi di intervallo (A2: A10) corrisponde a “Rony”. \ {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}.

Next link: / lookup-and-reference-excel-row-function [ROW] (names) –link: / lookup-and-reference-excel-row-function [ROW] `(A2) +1: link: / lookup-and-reference-excel-row-function [ROW] `(nomi *):

qui la funzione RIGA restituisce il numero di riga di ogni cella nei nomi.

\ {2; 3; 4; 5; 6; 7; 8; 9; 10}.

link: / lookup-and-reference-excel-row-function [ROW] (names) -`link: / lookup-and-reference-excel-row-function [ROW] `(A2) * Quindi sottraiamo la riga numero di A2 da ogni valore nella matrice data. Questo ci dà un array di numeri di serie che iniziano da 0.

\ {0; 1; 2; 3; 4; 5; 6; 7; 8}.

link: / lookup-and-reference-excel-row-function [ROW] (names) -`link: / lookup-and-reference-excel-row-function [ROW] `(A2) +1: Per ottenere numeri di serie a partire da 1, aggiungiamo 1 a ciascun valore in questo array. Questo ci dà il numero di serie che inizia da 1.

\ {1; 2; 3; 4; 5; 6; 7; 8; 9}.

Ora abbiamo IF (\ {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}, \ {1; 2; 3; 4; 5; 6; 7; 8; 9}).

Questo risolve in \ {1; FALSE; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE}.

Ora abbiamo la formula risolta in link: / statistics-formas-excel-small-function [SMALL] (\ {1; FALSE; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE}, 2).

Ora SMALL restituisce il secondo valore più piccolo nell’intervallo, che è 7.

Come lo usiamo?

La domanda arriva: qual è il vantaggio di ottenere un indice grezzo dell’ennesima partita? Sarebbe più utile se potessi recuperare le informazioni correlate dall’ennesimo valore. Bene, anche quello può essere fatto. Se vogliamo ottenere il valore dal valore dell’ennesima corrispondenza della cella adiacente nei nomi degli intervalli (A2: A10).

{=INDEX(B2:B10, SMALL(IF(names=“Rony” ,ROW(names)-ROW(A2)+1),2))}

Quindi sì ragazzi, è così che puoi ottenere l’ennesima partita in un intervallo. Spero di essere stato abbastanza esplicativo. Se hai dubbi su questo articolo o su qualsiasi altro argomento relativo a Excel / VBA, scrivi nella sezione commenti qui sotto.

Articoli correlati:

link: / information-formas-how-to-get-sequential-row-number-in-excel [How To Get Sequential Row Number in Excel]

link: / lookup-formulas-vlookup-top-5-values-with-duplicate-values-using-index-match-in-excel [Vlookup Top 5 Values ​​with Duplicate Values ​​Using INDEX-MATCH in Excel]

link: / lookup-formas-vlookup-multiple-values ​​[VLOOKUP Multiple Values]

link: / lookup-formas-use-index-and-match-to-lookup-value [Usa INDEX e MATCH per cercare il valore]

link: / lookup-formulas-lookup-valore-con-criteri-multipli [Valore di ricerca con criteri multipli]

Articoli popolari:

link: / formule-e-funzioni-introduzione-di-vlookup-funzione [La funzione CERCA.VERT in Excel]

link: / tips-countif-in-microsoft-excel [CONTA.SE in Excel 2016]

link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SOMMA.SE in Excel]