Já fiz dois posts ensinando como encontrar a última e a enésima ocorrência de um valor no Excel, agora trago uma variação desse última para conseguir obter o penúltimo valor.
A abordagem é bem semelhante ao de encontrar a n-ésima ocorrencia, a diferença fica no uso da função LARGE (MAIOR em português) ao invés da SMALL (MENOR). Seguindo o mesmo exemplo, suponha que você tenha uma tabela com os dados:
Neste caso, se eu procurar por “Caneta”, gostaria de obter o valor “R$ 4,00”. A função é a seguinte:
=INDEX(B2:B10;LARGE(IF("Caneta"=A2:A10;ROW(A2:A10)-ROW(A2)+1);2))
Aplique com CONTROL+SHIFT+ENTER.
O que determina qual ocorrência será retornada é o segundo parâmetro da função LARGE, nesse caso o número 2 para indicar que queremos o penúltimo. Para pegar a última ocorrência bastaria trocar ele para 1, a antepenúltima para 3 e assim por diante.