Recuperare l'n-esima corrispondenza con INDEX

Per trovare l'n-esima corrispondenza di un valore in un intervallo e recuperare i relativi dati, puoi utilizzare una formula basata sulle funzioni INDEX, ROW, SMALL e IF.

Come recuperare informazioni associate all'n-esima corrispondenza di un valore con INDEX?

Per ottenere le informazioni relative alla 3ª corrispondenza di Usain Bolt nella tabella mostrata sopra, puoi combinare le funzioni ROW e IF per creare un array contenente i numeri di riga di tutti i valori corrispondenti a Usain Bolt. In seguito, utilizza la funzione SMALL per estrarre il numero di riga dell’n-esima corrispondenza desiderata. Infine, passa questo risultato alla funzione INDEX per recuperare le informazioni corrispondenti sulla stessa riga.

Sintassi generica

=INDEX()return_range,SMALL(IF())lookup_array=lookup_value,ROW()lookup_array-ROW(INDEX())lookup_array,1,1))+1),n))

√ Nota: questa è una formula matriciale che richiede di essere inserita con Ctrl+Maiusc+Invio.

Per ottenere le informazioni relative alla 3ª corrispondenza di Usain Bolt, copiare o inserire le formule seguenti nelle celle G8 e G9, quindi premere Ctrl+Maiusc+Invioper ottenere i risultati:

Anno (cella G8)
=INDEX()B6:B12,SMALL(IF())C6:C12=G5,ROW()C6:C12-ROW(INDEX())C6:C12,1,1))+1),G6))
Tempo (cella G9)
=INDEX()D6:D12,SMALL(IF())C6:C12=G5,ROW()C6:C12-ROW(INDEX())C6:C12,1,1))+1),G6))

Spiegazione della formula

Qui utilizziamo la seguente formula come esempio:

=INDEX(B6:B12,)SMALL()IF(C6:C12=G5,)ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6))

• ROW(C6:C12):La funzione ROW restituisce il numero di riga di ciascuna cella nell'intervallo C6:C12 in un array come questo: {6;7;8;9;10;11;12}.
• ROW(INDEX(C6:C12,1,1)):Le funzioni INDEX e ROW restituiscono il numero della prima riga dell’intervallo di ricerca C6:C12 in questo foglio di lavoro Excel, che è 6.
• IF(C6:C12=G5,)ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1)=IF(C6:C12=G5,){6;7;8;9;10;11;12}-6+1):La funzione IF verifica se ciascun valore nell’intervallo C6:C12 corrisponde al valore nella cella G5, ovvero Usain Bolt. In caso affermativo, restituisce il risultato di “numero di riga corrispondente – 6 + 1”; in caso contrario, restituisce FALSE. I risultati formano quindi un array come questo: {FALSE;2;3;4;FALSE;FALSE;FALSE}.
• SMALL()IF(C6:C12=G5,)ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6)=SMALL(){FALSE;2;3;4;FALSE;FALSE;FALSE},G6):Il frammento restituisce il 3°valore più piccolo (quello indicato nella cella)G6) dall’array, che è 4.
• INDEX(B6:B12,)SMALL()IF(C6:C12=G5,)ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6)) = INDEX(B6:B12,4)La funzione INDEX restituisce il 4° valore nell’intervallo degli anni B6:B12, che è Beijing 2008.

Funzioni correlate

Formule correlate