Fórmula genérica
=INDEX(rng1,MODE(IF(rng2=criteria,MATCH(rng1,rng1,0))))
Resumo
Para localizar o texto que ocorre com mais frequência em um intervalo, com base nos critérios fornecidos, você pode usar uma fórmula de matriz baseada em várias funções do Excel INDEX, MATCH, MODE e IF. No exemplo mostrado, a fórmula em G5 é:
=INDEX(supplier,MODE(IF(client=F5,MATCH(supplier,supplier,0))))
onde "fornecedor" é o intervalo nomeado C5: C15 e "cliente" é o intervalo nomeado B5: B15.
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Explicação
Trabalhando de dentro para fora, usamos a função MATCH para combinar o intervalo de texto com ele mesmo, dando a MATCH o mesmo intervalo para valor de pesquisa e array de pesquisa, com zero para o tipo de correspondência:
MATCH(supplier,supplier,0)
Como o valor de pesquisa é uma matriz com 10 valores, MATCH retorna uma matriz de 10 resultados:
(1;1;3;3;5;1;7;3;1;5;5)
Cada item nesta matriz representa a primeira posição em que o nome de um fornecedor aparece nos dados. Esta matriz é alimentada na função IF, que é usada para filtrar resultados apenas para o Cliente A:
IF(client=F5,(1;1;3;3;5;1;7;3;1;5;5))
IF retorna a matriz filtrada para a função MODE:
(1;FALSE;3;FALSE;5;1;FALSE;FALSE;1;5;FALSE)
Observe que apenas as posições associadas ao Cliente A permanecem na matriz. MODE ignora valores FALSE e retorna o número que ocorre com mais frequência para a função INDEX como o número da linha:
=INDEX(supplier,1)
Finalmente, com o intervalo denominado "fornecedor" como a matriz, INDEX retorna "Marrom", o fornecedor que ocorre com mais frequência para o Cliente A.
