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).
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.
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 |
