fonte: https://www.funcaoexcel.com.br/indice-corresp-com-multiplos-criterios/
copiei o artigo inteiro do site funcaoexcel., pois achei muito bom e não quero perder esses dados. Obrigado
só uma observação, no google sheets coloque assim ={formula}
ÍNDICE + CORRESP com múltiplos critérios
ÍNDICE + CORRESP com múltiplos critérios
Se você já está familiarizado com a união das funções ÍNDICE + CORRESP não terá nenhum problema para entender o que será explicado aqui.
Saiba porque ÍNDICE + CORRESP é superior que PROCV, clique aqui
ÍNDICE + CORRESP
Se está lendo isso você já deve saber que a união dessas duas funções pode facilmente encontrar qualquer informação em uma matriz de dados, e que a função CORRESP fica responsável por indicar a linha e/ou coluna onde o dado procurado está, e esta linha/coluna é encontrada com base em um critério apenas. Mas o que fazer quando temos mais de um critério?
Exemplo prático
Com base nos dados abaixo, queremos encontrar o animal contido na coluna B com base em sua morada e em seu alimento, contidos nas colunas C e D respectivamente.
O que teremos de fazer é simplesmente dizer a função CORRESP que nos dará em qual linha da lista de animais está o dado procurado, que deve procurar não só na lista de moradas mas também na lista de alimentos e assim indicar em qual linha as duas condições são atendidas ao mesmo tempo. Para isso teremos de utilizar uma função matricial.
Note que outra saída possível para o problema seria o uso de uma coluna auxiliar que concatenasse o nome da morada com o nome do alimento, assim faríamos a busca nesta coluna auxiliar com o uso de ÍNDICE + CORRESP normalmente, sem uso de função matricial. Porém, como a ideia aqui é não criar esta coluna auxiliar, então devemos utilizar a função matricial.
Para saber mais sobre funções matriciais clique aqui.
Resolvendo o caso
A função que utilizaremos é:
{=SEERRO(ÍNDICE($B$3:$B$7;CORRESP(F3&G3;$C$3:$C$7&$D$3:$D$7;0));"")}
Escreva sem as chaves {} e aperte CTRL + SHIFT + ENTER para finalizar a fórmula, ao invés de apenas ENTER. As chaves serão postas automaticamente, o que significa que estamos utilizando uma função matricial.
Conforme a imagem:
Onde:
Célula F3 = Dado procurado em moradia.
Célula G3 = Dado procurado em alimento.
Intervalo C3:C7 = Intervalo onde estão listadas as moradias.
Intervalo D3:D7 = Intervalo onde estão listados os alimentos.
Veja que apenas concatenamos (utilizando &) o dado procurado e as área procuradas, simples assim. Desta forma a função CORRESP irá encontrar somente a linha onde as duas condições são atendidas ao mesmo tempo. Caso isso não aconteça, a função SEERRO entra em ação e ao invés de deixar que nossa função matricial retorne um erro, ela irá retornar vazio, ou seja, aspas duplas “”.
E eis o resultado da nossa fórmula.
Como só possuíamos um animal com moradia = floresta e alimento = frutas, a fórmula retornou este animal, o macaco.
Neste exemplo tínhamos apenas 2 critérios, caso você precise de mais, basta repetir o processo inserindo & entre os critérios de busca e entra as áreas de busca. Se por exemplo, além de moradia e alimento tivéssemos também o critério de porte do animal, então ficaria, moradia&alimento&porte.
IMPORTANTE – PARA QUE TUDO FUNCIONE
Como dito, o que foi ensinado aqui se trata de uma fórmula matricial e é necessário informar ao Excel isso, pois ele não identifica isso automaticamente. Ao terminar de escrever sua fórmula ao invés de apertar apenas tecla ENTER para concluir, aperte CTRL + SHIFT + ENTER. Só assim a fórmula irá funcionar. Caso precise, arraste (não copie, arraste) a fórmula para baixo para buscar demais dados. Se você fizer diferente disso, não irá funcionar, você notará que na barra de fórmulas a sua fórmula aparecerá entre chaves {Sua_fórmula} indicando que é uma fórmula matricial.
Para saber um pouco mais de fórmulas matriciais clique aqui