Applies ToExcel para Microsoft 365 Excel para a Web Excel 2024 Excel 2021 Excel 2019 Excel 2016

Dica: Tente usar as novas funções XLOOKUP e XMATCH , versões aprimoradas das funções descritas neste artigo. Essas novas funções funcionam em qualquer direção e retornam correspondências exatas por padrão, tornando-as mais fáceis e convenientes de usar do que seus antecessores.

Suponha que você tenha uma lista de números de localização do escritório e precise saber quais funcionários estão em cada escritório. A planilha é enorme, então você pode pensar que é uma tarefa desafiadora. Na verdade, é muito fácil fazer com uma função de pesquisa.

As funções VLOOKUP e HLOOKUP , juntamente com INDEX e MATCH, são algumas das funções mais úteis no Excel.

Observação: O recurso Assistente de Pesquisa não está mais disponível no Excel.

Aqui está um exemplo de como usar o VLOOKUP.

=PROCV(B2,C2:E7,3,VERDADEIRO)

Neste exemplo, B2 é o primeiro argumento: um elemento de dados que a função precisa funcionar. Para VLOOKUP, este primeiro argumento é o valor que você deseja encontrar. Esse argumento pode ser uma referência de célula ou um valor fixo, como "smith" ou 21.000. O segundo argumento é o intervalo de células, C2-:E7, no qual pesquisar o valor que você deseja encontrar. O terceiro argumento é a coluna nesse intervalo de células que contém o valor que você busca.

O quarto argumento é opcional. Insira TRUE ou FALSE. Se você inserir VERDADEIRO ou deixar o argumento em branco, a função retornará uma correspondência aproximada do valor especificado no primeiro argumento. Se você inserir FALSO, a função retornará uma correspondência exata do valor fornecido pelo primeiro argumento. Em outras palavras, deixar o quarto argumento em branco ou inserir TRUE oferece mais flexibilidade.

Este exemplo mostra como a função funciona. Quando você insere um valor na célula B2 (o primeiro argumento), o VLOOKUP pesquisa as células no intervalo C2:E7 (2º argumento) e retorna a correspondência aproximada mais próxima da terceira coluna no intervalo, coluna E (3º argumento).

Um uso típico da função PROCV

O quarto argumento está vazio, portanto, a função retorna uma correspondência aproximada. Caso contrário, você precisaria inserir um dos valores das colunas C ou D para obter um resultado.

Quando você está confortável com o VLOOKUP, a função HLOOKUP é igualmente fácil de usar. Insira os mesmos argumentos, mas ele pesquisa em linhas em vez de colunas.

Usando INDEX e MATCH em vez de VLOOKUP

Há certas limitações com o uso de VLOOKUP— a função VLOOKUP só pode pesquisar um valor da esquerda para a direita. Isso significa que a coluna que contém o valor que você pesquisa deve estar sempre localizada à esquerda da coluna que contém o valor retornado. Agora, se sua planilha não for criada dessa forma, não use VLOOKUP. Em vez disso, use a combinação de funções INDEX e MATCH.

Esse exemplo mostra uma pequena lista onde o valor que se deseja pesquisar, Chicago, não está na última coluna à esquerda. Portanto, não podemos usar o PROCV. Em vez disso, usaremos a função CORRESP para localizar Chicago no intervalo B1:B11. O termo está localizado na linha 4. Em seguida, o ÍNDICE usa esse valor como o argumento de pesquisa e localiza a população de Chicago na 4° coluna (coluna D). A fórmula usada é mostrada na célula A14.

Usando ÍNDICE e CORRESP para pesquisar um valor

Para obter mais exemplos de como usar INDEX e MATCH em vez de VLOOKUP, consulte o artigo https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ por Bill Jelen, MVP da Microsoft.

Experimentar

Se você quiser experimentar funções de pesquisa antes de experimentá-las com seus próprios dados, veja alguns dados de exemplo.

Exemplo VLOOKUP no trabalho

Copie os dados a seguir em uma planilha em branco.

Dica: Antes de colar os dados no Excel, defina as larguras de coluna para as colunas A a C para 250 pixels e clique em Quebrar Texto Automaticamente (guia Home, grupo Alinhamento).

Densidade

Viscosidade

Temperatura

0,457

3,55

500

0,525

3,25

400

0,606

2,93

300

0,675

2,75

250

0,746

2,57

200

0,835

2,38

150

0,946

2,17

100

1,09

1,95

50

1,29

1,71

0

Fórmula

Descrição

Resultado

=PROCV(1,A2:C10,2)

Usando uma correspondência aproximada, procura o valor 1 na coluna A, localiza o maior valor menor ou igual a 1 na coluna A, que é 0,946 e retorna o valor da coluna B na mesma linha.

2,17

=PROCV(1,A2:C10,3,VERDADEIRO)

Usando uma correspondência aproximada, procura o valor 1 na coluna A, localiza o maior valor menor ou igual a 1 na coluna A, que é 0,946 e retorna o valor da coluna C na mesma linha.

100

=PROCV(0,7,A2:C10,3,FALSO)

Usando uma correspondência exata, procura o valor 0,7 na coluna A. Como não há nenhuma correspondência exata na coluna A, é retornado um erro.

#N/D

=PROCV(0,1,A2:C10,2,VERDADEIRO)

Usando uma correspondência aproximada, procura o valor 0,1 na coluna A. Como 0,1 é menor que o menor valor na coluna A, é retornado um erro.

#N/D

=PROCV(2,A2:C10,2,VERDADEIRO)

Usando uma correspondência aproximada, procura o valor 1 na coluna A, localiza o maior valor menor ou igual a 2 na coluna A, que é 1,29 e retorna o valor da coluna B na mesma linha.

1,71

Exemplo HLOOKUP

Copie todas as células nesta tabela e cole-as na célula A1 em uma planilha em branco no Excel.

Dica: Antes de colar os dados no Excel, defina as larguras de coluna para as colunas A a C para 250 pixels e clique em Quebrar Texto Automaticamente (guia Home, grupo Alinhamento).

Eixos

Rolamentos

Parafusos

4

4

9

5

7

10

6

8

11

Fórmula

Descrição

Resultado

=PROCH("Eixos"; A1:C4; 2; VERDADEIRO)

Pesquisa "Eixos" na linha 1 e retorna o valor que está na linha 2 da mesma coluna (coluna A).

4

=PROCH("Rolamentos"; A1:C4; 3; FALSO)

Pesquisa "Rolamentos" na linha 1 e retorna o valor que está na linha 3 da mesma coluna (coluna B).

7

=PROCH("B"; A1:C4; 3; VERDADEIRO)

Pesquisa "Rolamentos" na linha 1 e retorna o valor que está na linha 3 da mesma coluna. Como uma correspondência exata para "B" não foi encontrada, é usado o maior valor na linha 1 que é menor que "B": "Eixos", na coluna A.

5

=PROCH("Parafusos"; A1:C4; 4)

Pesquisa "Parafusos" na linha 1 e retorna o valor que está na linha 4 da mesma coluna (coluna C).

11

=PROCH(3; {1;2;3;"a";"b";"c";"d";"e";"f"}; 2; VERDADEIRO)

Procura o número 3 na constante de matriz de três linhas e retorna o valor da linha 2 na mesma coluna (nesse caso, a terceira). Há três linhas de valores na constante de matriz, cada uma separada por um ponto-e-vírgula (;). Como "c" foi localizado na linha 2 e na mesma coluna de 3, "c" é retornado.

c

Exemplos INDEX e MATCH

Este último exemplo emprega as funções INDEX e MATCH juntas para retornar o número de fatura mais antigo e a data correspondente para cada uma das cinco cidades. Como a data é retornada como um número, nós usamos a função TEXTO para formatá-la para uma data. A função ÍNDICE usa, na verdade, o resultado da função CORRESP como seu argumento. A combinação das funções ÍNDICE e CORRESP são usadas duas vezes em cada fórmula: primeiro para retornar o número da fatura e depois para retornar a data.

Copie todas as células nesta tabela e cole-as na célula A1 em uma planilha em branco no Excel.

Dica: Antes de colar os dados no Excel, defina as larguras de coluna para as colunas A a D para 250 pixels e clique em Quebrar Texto Automaticamente (guia Home, grupo Alinhamento).

Fatura

Cidade

Data da fatura

Primeira fatura por cidade, com data

3115

Atlanta

07/04/12

="Atlanta = "&INDICE($A$2:$C$33,CORRESP("Atlanta",$B$2:$B$33,0),1)& ", Data da fatura: " & TEXTO(INDICE($A$2:$C$33,CORRESP("Atlanta",$B$2:$B$33,0),3),"d/m/yy")

3137

Atlanta

09/04/12

="Austin = "&INDICE($A$2:$C$33,CORRESP("Austin",$B$2:$B$33,0),1)& ", Data da fatura: " & TEXTO(INDICE($A$2:$C$33,CORRESP("Austin",$B$2:$B$33,0),3),"d/m/yy")

3154

Atlanta

11/04/12

="Dallas = "&INDICE($A$2:$C$33,CORRESP("Dallas",$B$2:$B$33,0),1)& ", Data da fatura: " & TEXTO(INDICE($A$2:$C$33,CORRESP("Dallas",$B$2:$B$33,0),3),"d/m/yy")

3191

Atlanta

21/04/12

="New Orleans = "&INDICE($A$2:$C$33,CORRESP("New Orleans",$B$2:$B$33,0),1)& ", Data da fatura: " & TEXTO(INDICE($A$2:$C$33,CORRESP("New Orleans",$B$2:$B$33,0),3),"d/m/yy")

3293

Atlanta

25/04/12

="Tampa = "&INDICE($A$2:$C$33,CORRESP("Tampa",$B$2:$B$33,0),1)& ", Data da fatura: " & TEXTO(INDICE($A$2:$C$33,CORRESP("Tampa",$B$2:$B$33,0),3),"d/m/yy")

3331

Atlanta

27/04/12

3350

Atlanta

28/04/12

3390

Atlanta

01/05/12

3441

Atlanta

02/05/12

3517

Atlanta

08/05/12

3124

Austin

09/04/12

3155

Austin

11/04/12

3177

Austin

19/04/12

3357

Austin

28/04/12

3492

Austin

06/05/12

3316

Dallas

25/04/12

3346

Dallas

28/04/12

3372

Dallas

01/05/12

3414

Dallas

01/05/12

3451

Dallas

02/05/12

3467

Dallas

02/05/12

3474

Dallas

04/05/12

3490

Dallas

05/05/12

3503

Dallas

08/05/12

3151

New Orleans

09/04/12

3438

New Orleans

02/05/12

3471

New Orleans

04/05/12

3160

Tampa

18/04/12

3328

Tampa

26/04/12

3368

Tampa

29/04/12

3420

Tampa

01/05/12

3501

Tampa

06/05/12

Cartão de Referência Rápida: Pesquisa de atualização VLOOKUP

e funções de referência (referência)

Use o argumento table_array em uma função VLOOKUP

Precisa de mais ajuda?

Quer mais opções

Explore os benefícios da assinatura, procure cursos de treinamento, saiba como proteger seu dispositivo e muito mais.

As comunidades ajudam você a fazer e responder perguntas, fazer comentários e ouvir especialistas com conhecimento avançado.