Índice de correspondência de várias linhas e colunas de critérios
Todos nós usamos VLOOKUP no dia a dia para buscar os dados e também estamos cientes do fato de que VLOOKUP pode buscar os dados da esquerda para a direita, portanto, o valor de pesquisa deve estar sempre no lado esquerdo das colunas de resultado. No entanto, temos várias alternativas que podem ser usadas como alternativa à função VLOOKUP no Excel. Com tecnologia avançada, podemos usar a fórmula INDEX + MATCH para combinar vários critérios para linhas e colunas. Portanto, este artigo especial o levará em detalhes sobre essa técnica.
Como usar a fórmula INDEX + MATCH para combinar vários critérios?
Aqui, explicamos como usar a fórmula índice + correspondência para combinar vários critérios para linhas e colunas com exemplos.
Você pode baixar este modelo de índice de correspondência de múltiplos critérios aqui - modelo de índice de correspondência de vários critériosExemplo # 1 - Fórmula ÍNDICE + CORRESPONDÊNCIA
Nem a maioria dos usuários do Excel busca funções além de VLOOKUP, os motivos podem ser tantos. De qualquer forma, vamos fazer uma introdução simples a esta fórmula antes de irmos para o nível avançado.
Por exemplo, observe a estrutura de dados abaixo no Excel.
Temos nomes de “Representantes de Vendas” e seus respectivos valores de vendas. Por outro lado, temos uma lista suspensa de “Representante de venda” na célula D2.
Com base na seleção que fazemos na lista suspensa, o valor das vendas deve aparecer na célula E2.
O problema é que não podemos aplicar a fórmula VLOOKUP porque o valor de pesquisa “Representante de vendas” está à direita da coluna de resultado “Vendas”, portanto, nesses casos, podemos usar a fórmula de valor de pesquisa de combinação ÍNDICE + CORRESPONDÊNCIA.
INDEX procura o valor do número da linha mencionado no intervalo A2: A11 e, nesse intervalo, precisamos fornecer de qual linha precisamos que o valor de venda venha. Este valor de linha é baseado no nome “Representante de Vendas” selecionado na lista suspensa do Excel, então a função CORRESP procura o número da linha “Representante de Vendas” no intervalo B2: B11 e retorna o número da linha do valor correspondido .
Exemplo # 2 - Vários critérios na fórmula INDEX + MATCH
Agora temos uma estrutura de dados como a abaixo.
Temos valores de vendas mensais de “Sales Rep”. A partir dessa tabela, precisamos de resultados dinâmicos como na célula A15, criei uma lista suspensa “Representante de vendas” e na célula B14, criei uma lista suspensa “Mês”.
Com base na seleção feita nessas duas células, nossa fórmula deve buscar os dados da tabela acima.
Por exemplo, se eu escolher “Rep 8” e “Abr”, então será necessário mostrar o valor de venda de “Rep 8” para o mês de “Abr”.
Portanto, nesses casos, precisamos combinar linhas e colunas. Siga as etapas abaixo para aplicar a fórmula de acordo com as linhas e colunas.
Etapa 1: Abra a função INDEX na célula B15.
Etapa 2: O primeiro argumento da função INDEX é “Array”, ou seja, de qual intervalo de células precisamos o resultado. Portanto, neste caso, precisamos dos valores de vendas, portanto, escolha o intervalo de células de B2 a G11.
Passo 3: Próximo argumento da função INDEX de qual linha do intervalo selecionado precisamos do resultado. Nesse caso, precisamos chegar ao número da linha “Representante de Vendas” com base na seleção feita na célula suspensa A15 da célula. Portanto, para buscar dinamicamente o número da linha com base na função MATCH aberta de seleção.
Etapa 4: O VALOR DE BUSCA da função CORRESPONDÊNCIA é “Representante de vendas”, portanto, escolha a célula A15 como referência.
Etapa 5: a matriz de pesquisa será o intervalo de nomes de "Representante de vendas" na tabela principal. Portanto, escolha o intervalo de A2 a A11.
Etapa 6: o tipo de correspondência da função MATCH será exato, portanto, insira zero como o valor do argumento.
Etapa 7: O próximo argumento da função INDEX é “Número da coluna”, ou seja, do intervalo de células selecionado de qual coluna precisamos do resultado. Isso depende do mês que selecionamos na lista suspensa da célula B14. Portanto, para obter o número da coluna, abra automaticamente outra função MATCH.
Etapa 8: Este valor de pesquisa de tempo será o nome do mês, então selecione a célula B14 como referência.
Etapa 9: a matriz de pesquisa será o intervalo de células do mês na tabela principal, ou seja, de B1 a G1.
Etapa 10: o último argumento é o tipo de correspondência, escolha “Correspondência exata” como critério. Feche os dois colchetes e pressione a tecla Enter para obter o resultado.
Como podemos ver acima, escolhemos “Rep 6” e “Abr” como o mês e nossa fórmula retornou o valor de vendas do mês de “Abr” para “Rep 6”.
Nota: a célula amarela é a referência para você.
Coisas para lembrar
- Uma combinação de INDEX + MATCH pode ser mais poderosa do que a fórmula VLOOKUP.
- INDEX & MATCH podem corresponder aos cabeçalhos de linhas e colunas e retornar o resultado da tabela do meio.
- MATCH pode retornar o número da linha e o número da coluna dos cabeçalhos da tabela de ambas as linhas e colunas.
