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 as combinações exatas por padrão, tornando-as mais fáceis e convenientes de usar do que seus predecessores.
Suponha que você tenha uma lista de números de local do escritório e precisa saber quais funcionários estão em cada escritório. A planilha é enorme, portanto, você pode pensar que é uma tarefa desafiadora. Na verdade, é muito fácil fazer com uma função de análise.
As funções VLOOKUPe HLOOKUP, juntamente com INDEX e MATCH,são algumas das funções mais úteis no Excel.
Observação: O recurso Assistente de Procurar não está mais disponível no Excel.
Veja 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 para 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ê procura.
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. Ao inserir um valor na célula B2 (o primeiro argumento), o VLOOKUP pesquisa as células no intervalo C2:E7 (argumento 2nd) e retorna a combinação aproximada mais próxima da terceira coluna do intervalo, a coluna E (argumento 3rd).
O quarto argumento está vazio, portanto, a função retorna uma combinação 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.
Há determinadas limitações com o uso do VLOOKUP— a função VLOOKUP só pode procurar um valor da esquerda para a direita. Isso significa que a coluna que contém o valor que você procurar sempre deve estar localizada à esquerda da coluna que contém o valor de retorno. Agora, se sua planilha não for criada dessa forma, não use o VLOOKUP. 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 //www.mrexcel.com/excel-tips/excel-vlookup-index-match/ por Bill Jelen, MVP da Microsoft.
Se você quiser experimentar funções de pesquisa antes de experimentá-las com seus próprios dados, aqui estão alguns dados de exemplo.
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 |
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("Machados"; A1:C4; 2; VERDADEIRO) | Pesquisa "Machados" 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": "Machados", 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 |
Este último exemplo emprega as funções INDEX e MATCH em conjunto para retornar o número da fatura mais antigo e sua 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 |