Tenho duas tabelas do Excel, tabela A e tabela B. A tabela A contém as colunas ID do cliente, CEP, Nome do cliente e várias outras que possuem os mesmos nomes de colunas das colunas da tabela B. Desejo criar uma fórmula, de preferência usando referência estruturada para que a ordem das colunas na tabela B seja irrelevante, procure o valor na tabela B que corresponde ao ID do cliente da linha em que estou na tabela A e ao valor da coluna em que estou.
Por exemplo, se minha fórmula estiver na terceira coluna da tabela A e em uma linha com ID do cliente "123", quero que ela verifique seu próprio nome de coluna (Nome do cliente) e pesquise o valor para Nome do cliente onde o ID do cliente = "123" na tabela B.
A fórmula a seguir funciona bem para a coluna Nome do cliente:
=INDEX(TableB[Customer Name], MATCH([@[Customer Number]], TableB[Customer Number], 0))
mas quero ser capaz de criar uma única fórmula que substitua dinamicamente a parte [Nome do cliente] pelo nome da coluna em que estou, para que possa copiá-la em todas as colunas. Tentei criar a referência usando #Headers e indireto, mas recebo um erro Ref:
=INDIRECT("INDEX(TableB["&[#Headers]&"], MATCH([@[Customer Number]], TableB[Customer Number], 0))")
Responder1
INDEX MATCHé a abordagem correta, você só precisa ter cuidado ao estruturá-la.
Tabela A à esquerda. Tabela B à direita. Usaremos [Número do cliente] para procurar [Código postal].
Esta é a fórmula para escrever D2:
=INDEX(TableB,MATCH([@[Customer Number]],TableB[Customer Number],0),MATCH(D$1,TableB[#Headers],0))
INDEX, como você sabe, retorna o valor de uma célula na interseção de uma linha e uma coluna. MATCHretorna a posição relativa de um valor em uma matriz.
Portanto, para as duas entradas de INDEX, primeiro encontramos o número da linha na tabela de origem que corresponde ao valor de pesquisa que estamos usando (Número do Cliente) (esta é a primeira metade tradicional de um INDEX MATCH), alimentando MATCHuma matriz vertical para procurar e, em seguida, encontramos o número da coluna que corresponde ao nome da coluna em que estamos, alimentando MATCHuma matriz horizontal que compreende a linha de cabeçalho da coluna de origem.
Você notará que se adicionar um campo à tabela de origem e alterar o cabeçalho na tabela de fórmulas, você obterá os novos resultados sem alterar a fórmula.
..e que esta fórmula seja copiada tanto para frente quanto para baixo.
As duas chaves aqui são:
- sabendo que MATCH contará tanto para frente quanto para baixo.
- alterando manualmente a referência estruturada que o Excel fornece para a referência de célula real no estilo R1C1, de modo que você possa tornar o índice da coluna dessa referência de célula dinâmico em vez de fixo (ou seja, C$1 em oposição a
TableA[[#Headers],[Post Code]], que codifica o valor para o campo em que você está e, como tal, não será copiado, embora funcione se você alterar manualmente o nome da coluna que contém a fórmula de pesquisa e precisar apenas de um campo de pesquisa na tabela de destino) .
ObservaçãoSei que esta pergunta já tem mais de três anos, mas é uma boa pergunta e uma grande demonstração da versatilidade da INDEX MATCHtécnica.