Fórmula genérica
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Resumo
Para pesquisar um valor combinando várias colunas, você pode usar uma fórmula de matriz baseada em MMULT, TRANSPOSE, COLUMN e INDEX. No exemplo mostrado, a fórmula em H4 é:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
onde "nomes" é o intervalo nomeado C4: E7 e "grupos" é o intervalo nomeado B4: B7. A fórmula retorna o grupo ao qual cada nome pertence.
Observação: esta é uma fórmula de matriz e deve ser inserida com a tecla Ctrl Shift Enter.
Explicação
Trabalhando de dentro para fora, os critérios lógicos usados nesta fórmula são:
--(names=G4)
onde nomes é o intervalo nomeado C4: E7. Isso gera um resultado VERDADEIRO / FALSO para cada valor nos dados, e o duplo negativo força os valores VERDADEIRO / FALSO para 1 e 0 para produzir uma matriz como esta:
(0,0,0;1,0,0;0,0,0;0,0,0)
Este array tem 4 linhas por 3 colunas, combinando com a estrutura de "nomes".
Uma segunda matriz é criada com esta expressão:
TRANSPOSE(COLUMN(names)^0))
A função COLUMN é usada para criar um array numérico com 3 colunas e 1 linha, e TRANSPOSE converte este array em 1 coluna e 3 linhas. Elevar à potência de zero simplesmente converte todos os números na matriz em 1. A função MMULT é então usada para realizar a multiplicação da matriz:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
e o resultado vai para a função MATCH como a matriz, com 1 como o valor de pesquisa:
MATCH(1,(0;1;0;0),0)
A função MATCH retorna a posição da primeira correspondência, que corresponde à linha da primeira linha correspondente que atende aos critérios fornecidos. Isso é alimentado em INDEX como o número da linha, com o intervalo nomeado "grupos" como a matriz:
=INDEX(groups,2)
Finalmente, INDEX retorna "Bear", o grupo ao qual Adam pertence.
Literal contém para critérios
Para verificar se há valores de texto específicos em vez de uma correspondência exata, você pode usar as funções ISNUMBER e SEARCH juntas. Por exemplo, para combinar células que contêm "apple", você pode usar:
=ISNUMBER(SEARCH("apple",data))
Esta fórmula é explicada aqui.
