Estes exemplos ensinam como fazer Vlookup em múltiplos critérios, retornar uma instância específica ou todas as correspondências, fazer Vlookup dinâmico em várias planilhas e muito mais.
É a segunda parte da série que o ajudará a aproveitar o poder do Excel VLOOKUP. Os exemplos indicam que você sabe como essa função funciona. Caso contrário, é lógico começar com os usos básicos de VLOOKUP no Excel.
Antes de prosseguir, deixe-me lembrar brevemente a sintaxe:
Agora que todos estão na mesma página, vamos dar uma olhada mais de perto nos exemplos de fórmulas avançadas de VLOOKUP:
- Como verificar vários critérios no Excel
- Vlookup e retorna enésima correspondência
- Vlookup valores múltiplos
- Vlookup baseado em valores de linha e coluna
- Fórmula aninhada de Vlookup
- VLOOKUP e INDIRETO para extrair dados de várias planilhas
Como pesquisar vários critérios
A função VLOOKUP do Excel é realmente útil quando se trata de pesquisar em um banco de dados para um determinado valor. No entanto, falta um recurso importante – sua sintaxe permite apenas um valor de pesquisa. Mas e se você quiser pesquisar com várias condições? Existem algumas soluções diferentes para você escolher.
Fórmula 1. PROCV com base em tw o valores
Suponha que você tenha uma lista de pedidos e deseja encontrar a quantidade com base em 2 critérios, nome do cliente e produto. Um fator complicador é que cada cliente pediu vários produtos, conforme mostrado na tabela abaixo:
Uma fórmula VLOOKUP usual não funcionará neste situação porque ele retorna a primeira correspondência encontrada com base em um único valor de pesquisa que você especifica.
Para superar isso, você pode adicionar uma coluna auxiliar e concatenar os valores de duas colunas de pesquisa (Cliente e Produto) lá. É importante que a coluna auxiliar seja a coluna mais à esquerda na matriz da tabela porque é onde a VLOOKUP do Excel sempre pesquisa o valor de pesquisa.
Portanto, adicione uma coluna à esquerda da sua tabela e copie a fórmula abaixo nessa coluna. Isso preencherá a coluna auxiliar com os valores das colunas B e C (o caractere de espaço é concatenado entre eles para melhor legibilidade):
=B2&" "&C2
Em seguida, use uma fórmula VLOOKUP padrão e coloque os dois critérios no argumento lookup_value, separados por um espaço:
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
Ou insira os critérios em células separadas (G1 e G2 em nosso caso) e concatene essas células:
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
Como nós queremos retornar um valor da coluna D, que é o quarto na matriz da tabela, usamos 4 para col_index_num. O argumento range_lookup é definido como FALSE para Vlookup uma correspondência exata. A captura de tela abaixo mostra o resultado:
Caso sua tabela de pesquisa esteja em outra planilha, inclua o nome da planilha em sua fórmula VLOOKUP. Por exemplo:
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
Como alternativa, crie um intervalo nomeado para a tabela de pesquisa (digamos, Pedidos) para tornar a fórmula mais fácil- para ler:
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
Para obter mais informações, consulte Como visualizar a partir de outra planilha no Excel.
Fórmula 2. VLOOKUP do Excel com várias condições
Em teoria, você pode usar a abordagem acima para Vlookup com mais de dois critérios. H no entanto, há algumas ressalvas. Em primeiro lugar, um valor de pesquisa é limitado a 255 caracteres e, em segundo lugar, o design da planilha pode não permitir a adição de uma coluna auxiliar.
Felizmente, o Microsoft Excel geralmente oferece mais de uma maneira de fazer a mesma coisa. Para Vlookup vários critérios, você pode usar uma combinação INDEX MATCH ou a função XLOOKUP recentemente introduzida no Office 365.
Por exemplo, para pesquisar com base em 3 valores diferentes (Data, Nome do cliente e Produto), use uma das seguintes fórmulas:
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
Onde:
Para obter a explicação detalhada das fórmulas, consulte:
- XLOOKUP com vários critérios
- Fórmula INDEX MATCH com vários critérios
Como usar VLOOKUP para obter a 2ª, 3ª ou enésima correspondência
Como você já sabe, VLOOKUP do Excel pode buscar apenas um valor correspondente, mais precisamente, ele retorna a primeira correspondência encontrada. Mas e se houver várias correspondências em sua matriz de pesquisa e você quiser obter a segunda ou a terceira instância? A tarefa parece bastante complicada, mas a solução existe!
Fórmula 1. Vlookup enésima instância
Suponha que você tenha nomes de clientes em uma coluna, os produtos que eles compraram em outra e você estão procurando o segundo ou terceiro produto comprado por um determinado cliente.
A maneira mais simples é adicionar uma coluna auxiliar à esquerda da tabela, como fizemos no primeiro exemplo. Mas, desta vez, iremos preenchê-lo com nomes de clientes e números de ocorrência como “John Doe1”, “John Doe2”, etc.
Para obter a ocorrência, use a função CONT.SE com uma referência de intervalo misto (o a primeira referência é absoluta e a segunda é relativa como $ B $ 2: B2). Visto que a referência relativa muda com base na posição da célula em que a fórmula é copiada, na linha 3 ela se tornará $ B $ 2: B3, na linha 4 – $ B $ 2: B4 e assim por diante.
Concatenada com o nome do cliente (B2), a fórmula assume este formato:
=B2&COUNTIF($B$2:B2, B2)
A fórmula acima vai para A2, e em seguida, você o copia para quantas células forem necessárias.
Depois disso, insira o nome do destino e o número da ocorrência em células separadas (F1 e F2) e use a fórmula abaixo para visualizar uma ocorrência específica:
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
Fórmula 2. Vlookup 2ª ocorrência
Se você estiver procurando pela 2ª instância do valor de pesquisa, poderá fazer sem a coluna auxiliar. Em vez disso, crie a matriz da tabela dinamicamente usando a função INDIRETO junto com MATCH:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
Onde:
- E1 é o valor de pesquisa
- A2: A11 é o intervalo de pesquisa
- B11 é a última célula (canto inferior direito) da tabela de pesquisa
Observe que a fórmula acima foi escrita para um caso específico onde as células de dados na tabela de pesquisa começam na linha 2. Se sua tabela estiver em algum lugar no meio da página, use esta fórmula universal, em que A1 é a célula superior esquerda da tabela de pesquisa que contém um cabeçalho de coluna:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
Como esta fórmula funciona
Aqui está a parte principal da fórmula que cria um intervalo vlookup dinâmico:
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
A função MATCH configurada para correspondência exata (0 no último argumento) compara o nome do destino (E1) com a lista de nomes (A2: A11) e retorna a posição da primeira correspondência encontrada, que é 3 eu não seu caso. Este número será usado como a coordenada de linha inicial para o intervalo vlookup, então adicionamos 2 a ele (+1 para excluir a primeira instância e +1 para excluir a linha 1 com os cabeçalhos das colunas). Como alternativa, você pode usar 1 + ROW (A1) para calcular o ajuste necessário automaticamente com base na posição da linha do cabeçalho (A1 em nosso caso).
Como resultado, obtemos a seguinte string de texto, que INDIRETO converte em uma referência de intervalo:
INDIRECT("A"&5&":B11") -> A5:B11
Este intervalo vai para o argumento table_array de VLOOKUP forçando-o a iniciar a pesquisa na linha 5, deixando de fora a primeira instância do valor de pesquisa:
VLOOKUP(E1, A5:B11, 2, FALSE)
How to Vlookup e retornar vários valores no Excel
A função VLOOKUP do Excel foi projetada para retornar apenas uma correspondência. Existe uma maneira de Vlookup múltiplas instâncias? Sim, existe, embora não seja fácil. Isso requer o uso combinado de várias funções, como INDEX, SMALL e ROW, é uma fórmula de matriz.
Por exemplo, o seguinte pode encontrar todas as ocorrências do valor de pesquisa F2 no intervalo de pesquisa B2: B16 e retornar várias correspondências da coluna C:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Existem 2 maneiras de inserir a fórmula em sua planilha:
- Digite a fórmula na primeira célula, pressione Ctrl + Shift + Enter e arraste-a para baixo para mais algumas células.
- Selecione várias células adjacentes em uma única coluna (F1: F11 em a captura de tela abaixo), digite a fórmula e pressione Ctrl + Shift + Enter para completá-la.
De qualquer forma, o número de células em que você insere a fórmula deve ser igual ou maior que o número máximo de correspondências possíveis.
Para obter a explicação detalhada da lógica da fórmula e mais exemplos, consulte Como PROCURAR vários valores no Excel .
Como Vlookup em linhas e colunas (lookup bidirecional)
Pesquisa bidirecional (também conhecida como pesquisa de matriz ou pesquisa bidimensional) é uma palavra chique para pesquisar um valor na interseção de uma determinada linha e coluna.Existem algumas maneiras diferentes de fazer pesquisa bidimensional no Excel, mas como o foco deste tutorial está na função VLOOKUP, vamos usá-la naturalmente.
Para este exemplo, vamos pegar o abaixo a tabela com vendas mensais e elaborar uma fórmula VLOOKUP para recuperar o número de vendas de um item específico em um determinado mês.
Com nomes de itens em A2: A9, nomes de meses em B1: F1, o item de destino em I1 e o mês de destino em I2, a fórmula é a seguinte:
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
Como esta fórmula funciona
O núcleo da fórmula é a função VLOOKUP padrão que procura uma correspondência exata para o valor de pesquisa em I1. Mas, como não sabemos em que coluna exatamente estão as vendas para um mês específico, não podemos fornecer o número da coluna diretamente para o argumento col_index_num. Para encontrar essa coluna, usamos a seguinte função MATCH:
MATCH(I2, A1:F1, 0)
Traduzida para o inglês, a fórmula diz: procure o valor I2 em A1: F1 e retorna sua posição relativa na matriz. Ao fornecer 0 ao terceiro argumento, você instrui MATCH para encontrar o valor exatamente igual ao valor de pesquisa (é como usar FALSE para o argumento range_lookup de VLOOKUP).
Visto que Mar está na quarta coluna na matriz de pesquisa, a função MATCH retorna 4, que vai diretamente para o argumento col_index_num de VLOOKUP:
VLOOKUP(I1, A2:F9, 4, FALSE)
Preste atenção que embora os nomes dos meses comecem na coluna B, usamos A1: I1 para o array de pesquisa. Isso é feito para que o número retornado por MATCH corresponda à posição da coluna em table_array de VLOOKUP.
Para aprender mais maneiras de realizar pesquisa de matriz no Excel, consulte INDEX MATCH MATCH e outras fórmulas para pesquisa bidimensional.
Como fazer vários Vlookup no Excel (Vlookup aninhado)
Às vezes pode acontecer que sua tabela principal e a tabela de pesquisa não tenham uma única coluna em comum, o que o impede de fazer um Vlookup entre duas tabelas. No entanto, existe outra tabela, que não contém as informações que você está procurando, mas tem uma coluna comum com a tabela principal e outra coluna comum com a tabela de pesquisa.
Na imagem abaixo ilustra a situação:
O objetivo é copiar os preços para a tabela principal com base nos IDs dos itens. O problema é que a tabela que contém os preços não tem os IDs do item, o que significa que teremos que fazer duas Vlookups em uma fórmula.
Por uma questão de conveniência, vamos criar alguns intervalos nomeados primeiro :
- A tabela de pesquisa 1 é chamada de Produtos (D3: E3).
- A tabela de pesquisa 2 é chamada de Preços (G3: H3).
As tabelas podem estar na mesma planilha ou em planilhas diferentes.
E agora, vamos realizar o chamado Vlookup duplo, também conhecido como Vlookup aninhado.
Primeiro, faça um VLOOKUP fórmula para encontrar o nome do produto na tabela de pesquisa 1 (produtos nomeados) com base no id do item (A3):
=VLOOKUP(A3, Products, 2, FALSE)
Em seguida, coloque a fórmula acima no argumento lookup_value de outra função VLOOKUP para extrair preços da tabela Lookup 2 (chamada Preços) com base no nome do produto retornado pelo VLOOKUP aninhado:
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
A captura de tela abaixo mostra nossa fórmula aninhada do Vlookup em ação:
How to Vlookup Multiple planilhas dinamicamente
Às vezes, você pode ter dados no mesmo formato divididos em várias planilhas. E seu objetivo é extrair dados de uma planilha específica, dependendo do valor-chave em uma determinada célula.
Isso pode ser mais fácil de entender com um exemplo. Digamos que você tenha alguns relatórios regionais de vendas no mesmo formato e queira obter os números de vendas de um produto específico em certas regiões:
Como no exemplo anterior, começamos definindo alguns nomes:
- Intervalo A2: B5 na planilha CA é denominado CA_Sales.
- Intervalo A2 : B5 na planilha FL é denominado FL_Sales.
- O intervalo A2: B5 na planilha KS é denominado KS_Sales.
Como você pode ver, todos os intervalos nomeados têm um comum parte (Vendas) e peças únicas (CA, FL, KS). Certifique-se de nomear seus intervalos de maneira semelhante, pois é essencial para a fórmula que iremos construir.
Fórmula 1. VLOOKUP INDIRETO para extrair dados dinamicamente de planilhas diferentes
Se sua tarefa é recuperar dados de planilhas múltiplas, uma fórmula VLOOKUP INDIRETO é a melhor solução – compacta e fácil de entender.
Para este exemplo, organizamos a tabela de resumo desta forma:
- Insira os produtos de interesse em A2 e A3. Esses são os nossos valores de pesquisa.
- Insira as partes exclusivas dos intervalos nomeados em B1, C1 e D1.
E agora, concatenamos a célula que contém a parte única (B1) com a parte comum (“_Vendas”) e alimentamos a string resultante para INDIRETO:
INDIRECT(B$1&"_Sales")
A função INDIRETO transforma a string em um nome que o Excel pode entender e você a coloca no argumento table_array de VLOOKUP:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
A fórmula acima vai para B2 e, em seguida, você a copia para baixo e à direita.
Preste atenção nisso, no valor de pesquisa ($ A2), bloqueamos a coordenada da coluna com referência de célula absoluta para que a coluna permaneça fixa quando a fórmula é copiada para a direita. Na referência B $ 1, bloqueamos a linha porque queremos a coluna coordene para alterar e forneça uma parte do nome apropriada para INDIRETO dependendo da coluna em que a fórmula é copiada:
Se sua tabela principal estiver organizada diferentemente, os valores de pesquisa em uma linha e partes exclusivas dos nomes de intervalo em uma coluna mn, então você deve bloquear a coordenada da linha no valor de pesquisa (B $ 1) e a coordenada da coluna nas partes do nome ($ A2):
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
Fórmula 2. VLOOKUP e IFs aninhados para pesquisar várias planilhas
Na situação em que você acabou de duas ou três planilhas de pesquisa, você pode usar uma fórmula VLOOKUP bastante simples com funções IF aninhadas para selecionar a planilha correta com base no valor-chave em uma célula específica:
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
Onde $ A2 é o valor de pesquisa (nome do item) e B $ 1 é o valor-chave (estado):
Nesse caso, você não precisa necessariamente definir nomes e pode usar referências externas para se referir a outra planilha ou pasta de trabalho.
Para obter mais exemplos de fórmulas, consulte Como fazer VLOOKUP em várias planilhas no Excel .
É assim que usar VLOOKUP no Excel. Agradeço a leitura e espero vê-lo em nosso blog na próxima semana!
Livro de exercícios práticos para download
Exemplos de fórmulas de VLOOKUP avançadas (arquivo .xlsx)
- Tutorial de VLOOKUP do Excel para iniciantes
- Como procurar várias correspondências no Excel
- Como usar VLOOKUP & funções SUM ou SUMIF em Excel
- INDEX & Funções MATCH no Excel – uma alternativa melhor para VLOOKUP
- 4 maneiras de fazer um Vlookup com distinção entre maiúsculas e minúsculas no Excel
- VLOOKUP do Excel não funciona – erros N / A, NAME e VALUE