Como encontrar um índice particular de um valor que aparece várias vezes em uma tabela

Question

Eu tenho uma função do Excel que encontra o índice em AmountNamedRange da segunda maior quantia, dado que sua% correspondenteDateNamedRange está dentro dos últimos trinta dias.

=ArrayFormula(Match(Large(If(DateNamedRange > Today() - 30, AmountNamedRange, 0), 2), AmountNamedRange, 0))

Isso funciona bem, desde que não haja outras linhas na tabela com o mesmo valor. Se existe tal coisa, no entanto, esta fórmula fornece o índice da primeira linha com o mesmo valor que o segundo maior valor nos últimos trinta dias. Eu percebo que minha explicação é confusa, então aqui está um exemplo (suponha que hoje seja 31/01/2017):

1    Date          Amount
2    12/2/2016     27
3    12/6/2016     66
4    12/15/2016    99
5    1/8/2017      45
6    1/20/2017     27
7    1/22/2017     18

Nesse caso, o segundo maior valor nos últimos 30 dias é 27 (índice 6), mas minha fórmula retornará o índice 2 porque Match localizará a primeira linha na tabela que corresponde ao valor. Como posso modificar minha fórmula para retornar corretamente 6?

    

Answer 1

Você precisa usar a mesma expressão que o segundo parâmetro de MATCH que você usou para LARGE , por isso não basta procurar em Montantes, mas apenas os valores dos últimos 30 dias:

=MATCH(LARGE(IF(DateNamedRange > TODAY() - 30, AmountNamedRange, 0), 2), IF(DateNamedRange > TODAY() - 30, AmountNamedRange, 0), 0)
(ainda é uma fórmula de matriz)