Obter a enésima correspondência com ÍNDICE

Para encontrar a enésima correspondência de um valor num intervalo e obter os respetivos dados associados, pode utilizar uma fórmula baseada nas funções ÍNDICE, LIN, MENOR e SE.

Como obter a informação associada à enésima ocorrência de um valor com ÍNDICE?

Para obter a informação relativa à 3.ª correspondência de Usain Bolt na tabela apresentada acima, pode combinar as funções LIN e SE para criar um array com os números de linha de todas as ocorrências de Usain Bolt. Depois, utilize a função MENOR para identificar o número da linha da enésima correspondência pretendida. Por fim, insira esse resultado na função ÍNDICE para obter a informação correspondente nessa mesma linha.

Sintaxe genérica

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

√ Nota: Esta é uma fórmula de matriz que requer que a introduza com Ctrl+Shift+Enter.

Para obter a informação relativa à 3.ª correspondência de Usain Bolt, copie ou introduza as fórmulas abaixo nas células G8 e G9 e prima Ctrl+Shift+Enterpara obter os resultados:

Year (Cell G8)
=ÍNDICE()B6:B12,MENOR(SE())C6:C12=G5,LIN()C6:C12-LIN(ÍNDICE())C6:C12,1,1))+1),G6))
Time (Cell G9)
=ÍNDICE()D6:D12,MENOR(SE())C6:C12=G5,LIN()C6:C12-LIN(ÍNDICE())C6:C12,1,1))+1),G6))

Explicação da fórmula

Utilizamos a fórmula abaixo como exemplo:

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

• ROW(C6:C12): A função LIN retorna o número de linha de cada célula no intervalo C6:C12 num array com este formato: {6;7;8;9;10;11;12}.
• ROW(INDEX(C6:C12,1,1)):As funções ÍNDICE e LIN devolvem o número da primeira linha do intervalo de pesquisa C6:C12 nesta folha de cálculo do Excel, que é 6.
• SE(C6:C12=G5,)ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1)=SE(C6:C12=G5,){6;7;8;9;10;11;12}-6+1):A função SE verifica se cada valor no intervalo C6:C12 corresponde ao valor na célula G5, Usain Bolt. Se sim, a função devolve o resultado de “número da respetiva linha – 6 + 1”; caso contrário, devolve FALSO. Assim, os resultados aparecem num array com este formato: {FALSO;2;3;4;FALSO;FALSO;FALSO}.
• MENOR()SE(C6:C12=G5,)LIN(C6:C12)-LIN(ÍNDICE(C6:C12,1,1))+1),G6)=MENOR(){FALSO;2;3;4;FALSO;FALSO;FALSO},G6):O excerto devolve o 3.º(valor na célula)G6) menor valor do array, que é 4.
• ÍNDICE(B6:B12,)MENOR()SE(C6:C12=G5,)LIN(C6:C12)-LIN(ÍNDICE(C6:C12,1,1))+1),G6)) = ÍNDICE(B6:B12,4)A função ÍNDICE devolve o 4.º valor no intervalo dos anos B6:B12, que é Pequim 2008.

Funções relacionadas

Fórmulas relacionadas