Mesclar tabelas com INDEX e MATCH
Digamos que você tenha duas ou mais tabelas com uma coluna comum, no entanto, os dados nas colunas comuns não estão listados na mesma ordem. Neste caso, para mesclar, combinar ou unir as tabelas combinando os dados, você pode usar a ajuda doe ÍNDICE e JOGO funções.
Como mesclar tabelas com INDEX e MATCH?
Juntar-se tabela 1 e tabela 2 para combinar todas as informações do nova mesa como mostrado na captura de tela acima, você deve primeiro copiar os dados da tabela 1 ou da tabela 2 para a nova tabela (aqui copiei os dados da tabela 1, veja a captura de tela abaixo). Pegue a primeira carteira de estudante 23201 na nova tabela como exemplo, as funções INDEX e MATCH ajudarão você a recuperar suas informações de pontuação e classificação desta maneira: MATCH retorna a posição da linha do ID do aluno que corresponde 23201 in tabela 2. As informações da linha serão alimentadas ao INDEX para recuperar o valor na interseção dessa linha e a coluna especificada (coluna de pontuação ou classificação).
Sintaxe genérica
=INDEX(return_table,MATCH(lookup_value,lookup_array,0),col_num)
√ Nota: Uma vez que preenchemos as informações de tabela 1, agora só precisamos recuperar os dados correspondentes de tabela 2.
- tabela_retorno: A tabela da qual retornar as pontuações dos alunos. Aqui se refere a tabela 2.
- valor_pesquisa: O valor que você usou para corresponder às informações no tabela_retorno. Aqui se refere ao valor do ID do aluno no nova mesa.
- lookup_array: O intervalo de células com os valores para comparar com o lookup_value. Aqui se refere à coluna ID do aluno na tabela_retorno.
- número_coluna: O número da coluna que indica de qual coluna do tabela_retorno para retornar as informações correspondentes.
- 0: A tipo de partida 0 força MATCH a realizar uma correspondência exata.
Para recuperar os dados correspondentes da tabela 2 para combinar todas as informações na nova tabela, copie ou insira as fórmulas abaixo nas células F16 e G16 e pressione Entrar para obter os resultados:
Célula F16 (Pontuação)
= INDEX ($F$5:$H$11,PARTIDA(C16,$ F $ 5: $ F $ 11, 0),2)
Célula G16 (Classificação)
= INDEX ($F$5:$H$11,PARTIDA(C16,$ F $ 5: $ F $ 11, 0),3)
√ Nota: Os cifrões ($) acima indicam referências absolutas, o que significa que o tabela_retorno e lookup_array na fórmula não mudará quando você mover ou copiar as fórmulas para outras células. No entanto, não há cifrões adicionados ao lookup_value já que você quer que seja dinâmico. Depois de inserir a fórmula, arraste a alça de preenchimento para baixo para aplicar a fórmula às células abaixo.
Explicação da fórmula
Aqui, usamos a fórmula abaixo como exemplo:
=INDEX($F$5:$H$11,MATCH(C16,$F$5:$F$11,0),2)
- PARTIDA(C16,$F$5:$F$11,0): A tipo_correspondência 0 força a função MATCH a realizar uma correspondência exata. A função então retorna a posição do valor de correspondência de 23201 (o valor em C16) na matriz de pesquisa $ F $ 5: $ F $ 11. Então, a função irá retornar 3 já que seu valor de correspondência está no 3ª posição no intervalo.
- ÍNDICE($F$5:$H$11,PARTIDA(C16,$F$5:$F$11,0),2) = ÍNDICE ($F$5:$H$11,3,2): A função INDEX retorna o valor na interseção do 3rd linha e 2ª coluna da tabela de retorno $F$5:$H$11, o que é 91.
Funções relacionadas
A função INDEX do Excel retorna o valor exibido com base em uma determinada posição de um intervalo ou matriz.
A função MATCH do Excel procura um valor específico em um intervalo de células e retorna a posição relativa do valor.
Fórmulas Relacionadas
Correspondência exata com INDEX e MATCH
Se você precisa descobrir as informações listadas no Excel sobre um produto específico, filme ou pessoa, etc., você deve fazer um bom uso da combinação das funções INDEX e MATCH.
Correspondência aproximada com INDEX e MATCH
Há momentos em que precisamos encontrar correspondências aproximadas no Excel para avaliar o desempenho dos funcionários, dar notas aos alunos, calcular a postagem com base no peso, etc. Neste tutorial, falaremos sobre como usar as funções INDEX e MATCH para recuperar o resultados que precisamos.
Pesquisar o valor de correspondência mais próximo com vários critérios
Em alguns casos, pode ser necessário pesquisar o valor de correspondência mais próximo ou aproximado com base em mais de um critério. Com a combinação das funções INDEX, MATCH e IF, você pode fazer isso rapidamente no Excel.
As melhores ferramentas de produtividade para escritório
Kutools para Excel - ajuda você a se destacar na multidão
Kutools para Excel possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...
Guia Office - Habilite leitura e edição com guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques do mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz guias eficientes para o Office (inclui Excel), assim como Chrome, Edge e Firefox.