Fórmula genérica
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Resumo
Para encontrar o valor máximo em um determinado mês, você pode usar a função MAXIFS ou uma das outras alternativas abaixo. No exemplo mostrado, a fórmula em G5 é:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Onde vendas (D5: D15), datas (B5: B15) e valores (C5: C15) são intervalos nomeados.
Explicação
A função MAXIFS pode encontrar o valor máximo em um intervalo com base em um ou mais critérios. No exemplo mostrado, estamos usando MAXIFS para encontrar o valor máximo de vendas com base em um determinado mês por meio de datas entre o primeiro dia do mês e o último dia do mês. O primeiro critério verifica se as datas são maiores ou iguais ao primeiro dia do mês:
dates,">="&G4,dates // first of month
Observação: estamos assumindo que a data no G4 é uma data do "primeiro dia do mês".
O segundo critério verifica se as datas são menores ou iguais ao último dia do mês, calculado com a função EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Quando ambos os critérios retornam TRUE, a data está no mês determinado e MAXIFS retorna o valor máximo de datas que atendem aos critérios.
Fórmulas alternativas
Se a sua versão do Excel não inclui a função MAXIFS, existem outras opções. Uma opção é uma fórmula de matriz simples com base nas funções MAX e IF:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Nota: Esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Outra opção é usar a função AGGREGATE desta forma:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Ambas as opções acima usam um método mais simples de datas de teste com base na função TEXTO que usa um formato de número para extrair o mês e o ano de uma data. A função TEXT não pode ser usada desta forma dentro da função MAXIFS, porque MAXIFS requer um intervalo. Essas abordagens alternativas são explicadas com mais detalhes aqui.
O cliente máximo
Para recuperar o cliente associado ao valor máximo em um determinado mês, a fórmula em G6 é:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
A ideia aqui é que já sabemos o valor máximo em um mês (G5) e podemos usar esse valor como uma "chave" para pesquisar o cliente. Note que devemos repetir a lógica usada para isolar valores em um determinado mês, a fim de evitar uma falsa correspondência em um mês diferente.
Como as alternativas mencionadas acima, esta fórmula também usa um teste simplificado para datas com base na função TEXTO. Leia mais sobre INDEX e MATCH com vários critérios aqui.
Para uma fórmula tudo-em-um, substitua G5 por uma das opções de fórmula explicadas acima para determinar o valor máximo em um determinado mês.
