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

Question

Eu tenho uma função Excel que encontra o índice AmountNamedRangedo segundo maior valor, visto que o correspondente DateNamedRangeestá nos ú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 a mesma quantidade. Se existe tal coisa, porém, esta fórmula fornece o índice doprimeirolinha com o mesmo valor do segundo maior valor dos últimos trinta dias. Sei 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 Matchencontra a primeira linha da tabela que corresponde ao valor. Como posso modificar minha fórmula para retornar 6 corretamente?

Answer 1

Você precisa usar a mesma expressão do segundo parâmetro MATCHque você usou para LARGE, então não vai olhar apenas em Valores, 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)