Fórmula genérica
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Resumo
Para pesquisar valores com INDEX e MATCH, usando vários critérios, você pode usar uma fórmula de matriz. No exemplo mostrado, a fórmula em H8 é:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter, exceto no Excel 365.
Explicação
Esta é uma fórmula mais avançada. Para informações básicas, consulte Como usar INDEX e MATCH.
Normalmente, uma fórmula INDEX MATCH é configurada com MATCH definido para examinar um intervalo de uma coluna e fornecer uma correspondência com base em determinados critérios. Sem concatenar valores em uma coluna auxiliar ou na própria fórmula, não há como fornecer mais de um critério.
Essa fórmula contorna essa limitação usando a lógica booleana para criar uma matriz de uns e zeros para representar as linhas que correspondem a todos os 3 critérios e, em seguida, usando MATCH para corresponder ao primeiro 1 encontrado. A matriz temporária de uns e zeros é gerada com este snippet:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Aqui, comparamos o item em H5 com todos os itens, o tamanho em H6 com todos os tamanhos e a cor em H7 com todas as cores. O resultado inicial são três matrizes de resultados TRUE / FALSE como este:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Dica: use F9 para ver esses resultados. Basta selecionar uma expressão na barra de fórmulas e pressionar F9.
A operação matemática (multiplicação) transforma os valores TRUE FALSE em 1s e 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Após a multiplicação, temos uma única matriz como esta:
(0;0;1;0;0;0;0)
que é alimentado na função MATCH como a matriz de pesquisa, com um valor de pesquisa de 1:
MATCH(1,(0;0;1;0;0;0;0))
Neste ponto, a fórmula é uma fórmula INDEX MATCH padrão. A função MATCH retorna 3 para INDEX:
=INDEX(E5:E11,3)
e INDEX retorna um resultado final de $ 17,00.
Visualização de matriz
As matrizes explicadas acima podem ser difíceis de visualizar. A imagem abaixo mostra a ideia básica. As colunas B, C e D correspondem aos dados do exemplo. A coluna F é criada pela multiplicação das três colunas. É o array entregue ao MATCH.
Versão sem matriz
É possível adicionar outro INDEX a esta fórmula, evitando a necessidade de inserir como uma fórmula de matriz com control + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
A função INDEX pode manipular arrays nativamente, então o segundo INDEX é adicionado apenas para "capturar" o array criado com a operação de lógica booleana e retornar o mesmo array novamente para MATCH. Para fazer isso, INDEX é configurado com zero linhas e uma coluna. O truque da linha zero faz com que INDEX retorne a coluna 1 do array (que já é uma coluna de qualquer maneira).
Por que você quer a versão sem array? Às vezes, as pessoas esquecem de inserir uma fórmula de matriz com control + shift + enter, e a fórmula retorna um resultado incorreto. Portanto, uma fórmula sem matriz é mais "à prova de balas". No entanto, a compensação é uma fórmula mais complexa.
Observação: no Excel 365, não é necessário inserir fórmulas de matriz de maneira especial.
