Fórmula genérica
=INDEX(data,MATCH(lookup,ids,0),2)
Resumo
Para juntar ou mesclar tabelas que possuem um id comum, você pode usar as funções INDEX e MATCH. No exemplo mostrado, a fórmula em E5 é:
=INDEX(data,MATCH($C5,ids,0),2)
onde "dados" é o intervalo nomeado H5: J8 e "ids" é o intervalo nomeado H5: H8.
Explicação
Essa fórmula extrai o nome e o estado do cliente da tabela de clientes para a tabela de pedidos. A função MATCH é usada para localizar o cliente certo e a função INDEX é usada para recuperar os dados.
Recuperando o nome do cliente
Trabalhando de dentro para fora, a função MATCH é usada para obter um número de linha como este:
MATCH($C5,ids,0)
- O valor de pesquisa vem o id do cliente em C5, que é uma referência mista, com a coluna bloqueada, para que a fórmula possa ser facilmente copiada.
- A matriz de pesquisa são os ids de intervalo nomeados (H5: H8), a primeira coluna na tabela do cliente.
- O tipo de correspondência é definido como zero para forçar uma correspondência exata.
A função MATCH retorna 2 neste caso, que vai para INDEX como o número da linha:
=INDEX(data,2,2)
Com o número da coluna codificado como 2 (os nomes dos clientes estão na coluna 2) e a matriz definida para o intervalo nomeado "dados" (H5: J8) INDEX retorna: Amy Chang.
Recuperando o estado do cliente
A fórmula para recuperar o estado do cliente é quase idêntica. A única diferença é que o número da coluna é codificado como 3, já que as informações do estado aparecem na 3ª coluna:
=INDEX(data,MATCH($C5,ids,0),2) // get name =INDEX(data,MATCH($C5,ids,0),3) // get state
Correspondência bidirecional dinâmica
Adicionando outra função MATCH à fórmula, você pode configurar uma correspondência dinâmica bidirecional. Por exemplo, com o intervalo nomeado "cabeçalhos" para H4: J4, você pode usar uma fórmula como esta:
=INDEX(data,MATCH($C5,ids,0),MATCH(E$4,headers,0))
Aqui, uma segunda função MATCH foi adicionada para obter o número correto da coluna. MATCH usa o cabeçalho da coluna atual na primeira tabela para localizar o número da coluna correto na segunda tabela e retorna automaticamente esse número para INDEX.
