Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Inscreva-se  \/ 
x

or

Função VLOOKUP com alguns exemplos básicos e avançados no Excel

No Excel, a função VLOOKUP é uma função poderosa para a maioria dos usuários do Excel, que é usada para procurar um valor na extremidade esquerda do intervalo de dados e retornar um valor correspondente na mesma linha de uma coluna que você especificou conforme a captura de tela mostrada abaixo . Este tutorial fala sobre como usar a função VLOOKUP com alguns exemplos básicos e avançados no Excel.

Índice:

1. Introdução da função VLOOKUP - Sintaxe e Argumentos

2. Exemplos básicos de VLOOKUP

3. Exemplos avançados de VLOOKUP

4. Os valores correspondentes de VLOOKUP mantêm a formatação da célula

5. Baixe os arquivos de amostra VLOOKUP


Introdução da função VLOOKUP - Sintaxe e Argumentos

A sintaxe da função PROCV:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Argumentos:

Valor_procurado: O valor que você deseja pesquisar. Deve estar na primeira coluna do intervalo table_array.

Table_array: O intervalo de dados ou tabela onde a coluna de valor de pesquisa e a coluna de valor de resultado estão localizadas

Col_index_num: O número da coluna da qual o valor correspondente será retornado. Ele começa com 1 na coluna mais à esquerda na matriz da tabela.

Pesquisa de alcance: Um valor lógico que determina se esta função VLOOKUP retornará uma correspondência exata ou aproximada.

  • Correspondência aproximada - 1 / VERDADEIRO: Se uma correspondência exata não for encontrada, a fórmula procura a correspondência mais próxima - o maior valor que é menor que o valor de pesquisa. Nesse caso, você deve classificar a coluna de pesquisa em ordem crescente.
    = VLOOKUP (lookup_value, table_array, col_index, TRUE)
    = VLOOKUP (lookup_value, table_array, col_index, 1)
  • Combinação exata - 0 / FALSO: É usado para pesquisar um valor exatamente igual ao valor de pesquisa. Se uma correspondência exata não for encontrada, o valor de erro # N / A será retornado.
    = VLOOKUP (lookup_value, table_array, col_index, FALSE)
    = VLOOKUP (lookup_value, table_array, col_index, 0)

notas:

  • 1. A função Vlookup procura apenas o valor da esquerda para a direita.
  • 2. Se houver vários valores correspondentes com base no valor de pesquisa, somente o primeiro correspondente será retornado usando a função Vlookup.
  • 3. Ele retornará o valor de erro # N / A se o valor de pesquisa não puder ser encontrado.

Exemplos básicos de VLOOKUP

1. Faça uma correspondência exata do Vlookup e uma correspondência aproximada do Vlookup

Faça uma correspondência exata do Vlookup no Excel

Normalmente, se você está procurando uma correspondência exata com a função Vlookup, você só precisa usar FALSE no último argumento.

Por exemplo, para obter as pontuações de matemática correspondentes com base nos números de identificação específicos, faça o seguinte:

1. Aplique a fórmula abaixo em uma célula em branco onde deseja obter o resultado:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

2. Em seguida, arraste a alça de preenchimento para baixo até as células que deseja preencher esta fórmula e você obterá os resultados conforme necessário. Veja a imagem:

notas:

  • 1. Na fórmula acima, F2 é o valor que você deseja que retorne seu valor correspondente, A2: D7 é a matriz da tabela, o número 3 é o número da coluna da qual o valor correspondente é retornado e o FALSE refere-se à correspondência exata.
  • 2. Se o valor do seu critério não for encontrado no intervalo de dados, um valor de erro # N / A será exibido.

Faça uma correspondência aproximada Vlookup no Excel

A correspondência aproximada é útil para pesquisar valores entre intervalos de dados. Se a correspondência exata não for encontrada, o Vlookup aproximado retornará o maior valor que é menor do que o valor de pesquisa.

Por exemplo, se você tiver os seguintes dados de intervalo, os pedidos especificados não estão na coluna Pedidos, como obter seu desconto mais próximo na coluna B?

1. Insira a seguinte fórmula em uma célula onde deseja colocar o resultado:

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

2. Em seguida, arraste a alça de preenchimento até as células para aplicar esta fórmula e você obterá as correspondências aproximadas com base nos valores fornecidos, consulte a captura de tela:

notas:

  • 1. Na fórmula acima, D2 é o valor que você deseja que retorne suas informações relativas, A2: B9 é o intervalo de dados, o número 2 indica o número da coluna que seu valor correspondido é retornado e o VERDADEIRO refere-se à correspondência aproximada.
  • 2. A correspondência aproximada retornará o maior valor menor do que o valor de pesquisa específico.
  • 3. Para usar a função Vlookup para obter um valor de correspondência aproximado, você deve classificar a coluna mais à esquerda do intervalo de dados em ordem crescente, caso contrário, ele retornará um resultado errado.

2. Faça um Vlookup com distinção entre maiúsculas e minúsculas no Excel

Por padrão, a função Vlookup executa uma pesquisa que não diferencia maiúsculas de minúsculas, o que significa que trata os caracteres minúsculos e maiúsculos como idênticos. Às vezes, você pode precisar fazer uma pesquisa com distinção entre maiúsculas e minúsculas no Excel; as funções Índice, Correspondência e Exata ou Pesquisa e Exata podem fazer um favor.

Por exemplo, eu tenho o seguinte intervalo de dados cuja coluna de ID contém uma string de texto com maiúsculas ou minúsculas, agora, quero retornar a pontuação matemática correspondente do número de ID fornecido.

Fórmula 1: usando as funções EXACT, INDEX, MATCH

1. Insira ou copie a fórmula de matriz abaixo em uma célula em branco onde deseja obter o resultado:

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

2. Então aperte Ctrl + Shift + Enter Pressione as teclas simultaneamente para obter o primeiro resultado e, em seguida, selecione a célula da fórmula, arraste a alça de preenchimento para baixo até as células que deseja preencher esta fórmula, então você obterá os resultados corretos de que precisa. Veja a imagem:

notas:

  • 1. Na fórmula acima, A2: A10 é a coluna que contém os valores específicos que você deseja pesquisar, F2 é o valor de pesquisa, C2: C10 é a coluna de onde o resultado será retornado.
  • 2. Se várias correspondências forem encontradas, esta fórmula sempre retornará a primeira correspondência.

Fórmula 2: Usando as funções Lookup e Exact

1. Por favor, aplique a fórmula abaixo em uma célula em branco onde você deseja obter o resultado:

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

2. Em seguida, arraste a alça de preenchimento para baixo até as células para as quais deseja copiar esta fórmula, e você obterá os valores correspondentes com distinção entre maiúsculas e minúsculas, conforme a captura de tela mostrada abaixo:

notas:

  • 1. Na fórmula acima, A2: A10 é a coluna que contém os valores específicos que você deseja pesquisar, F2 é o valor de pesquisa, C2: C10 é a coluna de onde o resultado será retornado.
  • 2. Se várias correspondências forem encontradas, esta fórmula sempre retornará a última correspondência.

3. Valores Vlookup da direita para a esquerda no Excel

A função Vlookup sempre pesquisa um valor na coluna mais à esquerda de um intervalo de dados e retorna o valor correspondente de uma coluna à direita. Se você quiser fazer um Vlookup reverso, o que significa pesquisar um valor específico à direita e retornar seu valor correspondente na coluna da esquerda, conforme mostrado abaixo:

Clique para saber os detalhes passo a passo sobre esta tarefa ...


4. Vlookup o segundo, enésimo ou último valor correspondente no Excel

Normalmente, se vários valores correspondentes forem encontrados ao usar a função Vlookup, apenas o primeiro registro correspondente será retornado. Nesta seção, falarei sobre como obter o segundo, enésimo ou último valor correspondente com a função Vlookup.

Vlookup e retorna o segundo ou enésimo valor correspondente

Suponha que você tenha uma lista de nomes na coluna A, o curso de treinamento que eles adquiriram na coluna B e, agora, está procurando o segundo ou enésimo curso de treinamento comprado por determinado cliente. Veja a imagem:

1. Para obter o segundo ou enésimo valor correspondente com base nos critérios fornecidos, aplique a seguinte fórmula de matriz em uma célula em branco:

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

2. Então aperte Ctrl + Shift + Enter juntas para obter o primeiro resultado e, em seguida, selecione a célula da fórmula, arraste a alça de preenchimento para as células que deseja preencher esta fórmula e todos os segundos valores correspondentes com base nos nomes fornecidos foram exibidos de uma vez, consulte a captura de tela:

Observação:

  • Nesta fórmula, A2: A14 é o intervalo com todos os valores para pesquisa, B2: B14 é o intervalo dos valores correspondentes dos quais você deseja retornar, E2 é o valor de pesquisa e o último número 2 indica o segundo valor correspondente que você deseja obter, se quiser retornar o terceiro valor correspondente, você só precisa alterá-lo para 3 conforme necessário.

Vlookup e retorna o último valor correspondente

Se você deseja visualizar e retornar o último valor correspondente conforme a captura de tela mostrada abaixo, Vlookup e retorna o último valor correspondente tutorial pode ajudá-lo a obter o último valor correspondente em detalhes.


5. Vlookup combinando valores entre dois valores ou datas fornecidos

Às vezes, você pode querer pesquisar valores entre dois valores ou datas e retornar os resultados correspondentes conforme a captura de tela mostrada abaixo, neste caso, você pode usar a função LOOKUP e uma tabela classificada.

Vlookup combinando valores entre dois valores ou datas fornecidos com fórmula

1. Primeiro, sua tabela original deve ser um intervalo de dados classificado. Em seguida, copie ou insira a seguinte fórmula em uma célula em branco:

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

2. Em seguida, arraste a alça de preenchimento para preencher esta fórmula para outras células de que você precisa e agora, você obterá todos os registros correspondentes com base no valor fornecido, consulte a captura de tela:

notas:

  • 1. Na fórmula acima, A2: A6 é o intervalo de valores menores e B2: B6 é o intervalo de números maiores em seu intervalo de dados, o E2 é o valor dado que você deseja obter seu valor correspondente, C2: C6 são os dados da coluna da qual você deseja extrair.
  • 2. Esta fórmula também pode ser usada para extrair valores correspondentes entre duas datas, conforme mostrado abaixo na captura de tela:

Vlookup combinando valores entre dois valores ou datas dados com um recurso útil

Se você sentir dor com a fórmula acima, apresentarei aqui uma ferramenta fácil - Kutools for Excel, Com o seu LOOKUP entre dois valores recurso, você pode retornar o item correspondente com base no valor específico ou data entre dois valores ou datas sem se lembrar de nenhuma fórmula.   Clique para baixar Kutools para Excel agora!


6. Usando curingas para correspondências parciais na função Vlookup

No Excel, os curingas podem ser usados ​​dentro da função Vlookup, que faz uma correspondência parcial em um valor de pesquisa. Por exemplo, você pode usar o Vlookup para retornar o valor correspondente de uma tabela com base em parte de um valor de pesquisa.

Supondo que eu tenha uma gama de dados como a imagem abaixo mostrada, agora, eu quero extrair a pontuação com base no primeiro nome (não no nome completo). Como resolveria essa tarefa no Excel?

1. A função normal do Vlookup não funciona corretamente, você precisa unir o texto ou a referência de célula com um caractere curinga, copie ou insira a seguinte fórmula em uma célula em branco:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

2. Em seguida, arraste a alça de preenchimento para preencher esta fórmula para outras células que você precisa, e todas as pontuações correspondentes foram retornadas conforme a captura de tela abaixo:

notas:

  • 1. Na fórmula acima, E2 & ”*” é o valor de pesquisa, o valor em E2 e o * curinga (“*” indica qualquer caractere ou qualquer caractere), A2: C11 é o intervalo de pesquisa, o número 3 a coluna que contém o valor a ser retornado.
  • 2. Vlookup ao usar curingas, você deve definir o modo de correspondência exata com FALSE ou 0 para o último argumento na função Vlookup.

Dicas:

1. Encontre e retorne os valores correspondentes que terminam com um valor específico, aplique esta fórmula: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2. Para pesquisar e retornar o valor correspondente com base em parte da string de texto, se o texto especificado está na frente, atrás ou no meio da string de texto, você só precisa juntar dois * caracteres em torno da referência de célula ou texto. Faça com esta fórmula: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7. Valores Vlookup de outra planilha

Normalmente, você pode ter que trabalhar com mais de uma planilha, a função Vlookup pode ser usada para pesquisar dados de outra planilha da mesma forma que em uma planilha.

Por exemplo, você tem duas planilhas conforme a captura de tela mostrada abaixo, para pesquisar e retornar os dados correspondentes da planilha especificada, siga estas etapas:

1. Insira ou copie a fórmula abaixo em uma célula em branco onde deseja obter os itens correspondentes:

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

2. Em seguida, arraste a alça de preenchimento até as células às quais deseja aplicar esta fórmula, e você obterá os resultados correspondentes conforme necessário, consulte a captura de tela:

Observação: Na fórmula acima:

  • A2 representa o valor de pesquisa;
  • Ficha de dados é o nome da planilha a partir da qual você deseja pesquisar dados, (se o nome da planilha contiver espaço ou caracteres de pontuação, você deve colocar aspas simples ao redor do nome da planilha, caso contrário, você pode usar diretamente o nome da planilha como = VLOOKUP (A2, Folha de dados! $ A $ 2: $ C $ 15,3,0));
  • A2: C15 é o intervalo de dados na Folha de Dados onde estamos pesquisando dados;
  • o número 3 é o número da coluna que contém os dados correspondentes dos quais você deseja retornar.

8. Valores Vlookup de outra pasta de trabalho

Esta seção falará sobre pesquisa e retornará os valores correspondentes de uma pasta de trabalho diferente usando a função Vlookup.

Por exemplo, a primeira pasta de trabalho contém as listas de produtos e custos, agora você deseja extrair o custo correspondente na segunda pasta de trabalho com base no item do produto, conforme a captura de tela abaixo mostrada.

1. Para recuperar o custo relativo de outra pasta de trabalho, primeiro, abra ambas as pastas de trabalho que deseja usar e, em seguida, aplique a seguinte fórmula em uma célula onde deseja colocar o resultado:

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

2. Em seguida, arraste e copie esta fórmula para outras células de que você precisa, veja a captura de tela:

notas:

  • 1. Na fórmula acima:
    B2 representa o valor de pesquisa;
    [Lista de produtos.xlsx] Folha1 é o nome da pasta de trabalho e da planilha a partir da qual você deseja pesquisar dados, (a referência à pasta de trabalho está entre colchetes e a pasta de trabalho + planilha inteira está entre aspas simples);
    A2: B6 é o intervalo de dados na planilha de outra pasta de trabalho onde estamos pesquisando dados;
    o número 2 é o número da coluna que contém os dados correspondentes dos quais você deseja retornar.
  • 2. Se a pasta de trabalho de pesquisa for fechada, o caminho completo do arquivo para a pasta de trabalho de pesquisa será mostrado na fórmula, como mostrado a seguir:

9. Vlookup e retorna um texto em branco ou específico em vez de 0 ou valor de erro # N / A

Normalmente, quando você aplica a função vlookup para retornar o valor correspondente, se a célula correspondente estiver em branco, ela retornará 0 e, se o valor correspondente não for encontrado, você obterá um erro # N / A de valor conforme a captura de tela abaixo. Em vez de exibir o valor 0 ou # N / A com uma célula em branco ou outro valor de sua preferência, Vlookup para retornar em branco ou valor específico em vez de 0 ou N / A o tutorial pode lhe fazer um favor passo a passo.


Exemplos avançados de VLOOKUP

1. Pesquisa bidirecional com função Vlookup (Vlookup em linha e coluna)

Às vezes, você pode precisar fazer uma pesquisa bidimensional, o que significa Vlookup em linha e coluna ao mesmo tempo. Digamos que você tenha o intervalo de dados a seguir e agora precise obter o valor de um produto específico em um determinado trimestre. Esta seção apresentará algumas fórmulas para lidar com esse trabalho no Excel.

Fórmula 1: Usando as funções VLOOKUP e MATCH

No Excel, você pode usar uma combinação das funções VLOOKUP e MATCH para fazer uma pesquisa bidirecional, aplique a seguinte fórmula em uma célula em branco e pressione Entrar chave para obter o resultado.

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

Observação: Na fórmula acima:

  • H1: o valor de pesquisa na coluna em que você deseja obter o valor correspondente com base;
  • A2: E6: o intervalo de dados incluindo cabeçalhos de linha;
  • H2: o valor de pesquisa na linha em que você deseja obter o valor correspondente com base;
  • A1: E1: as células dos cabeçalhos das colunas.

Fórmula 2: Usando as funções INDEX e MATCH

Aqui está outra fórmula que também pode ajudá-lo a realizar uma pesquisa bidimensional, aplique a fórmula abaixo e pressione Entrar chave para obter o resultado que você precisa.

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

Observação: Na fórmula acima:

  • B2: E6: o intervalo de dados a partir do qual retornar o item correspondente;
  • H1: o valor de pesquisa na coluna em que você deseja obter o valor correspondente com base;
  • A2: A6: os cabeçalhos das linhas contêm o produto que você deseja procurar.
  • H2: o valor de pesquisa na linha em que você deseja obter o valor correspondente com base;
  • B1: E1: os cabeçalhos das colunas contêm o trimestre que você deseja procurar.

2. Valor de correspondência Vlookup com base em dois ou mais critérios

É fácil para você pesquisar o valor correspondente com base em um critério, mas se você tiver dois ou mais critérios, o que pode fazer? As funções LOOKUP ou MATCH e INDEX no Excel podem ajudá-lo a resolver esse trabalho de forma rápida e fácil.

Por exemplo, eu tenho a tabela de dados abaixo, para retornar o preço combinado com base no produto e tamanho específicos, as fórmulas a seguir podem ajudá-lo.

Fórmula 1: Usando a função LOOKUP

Por favor, aplique a fórmula abaixo em uma célula onde você deseja obter o resultado e pressione a tecla Enter, veja a captura de tela:

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

notas:

  • 1. Na fórmula acima:
    A2: A12 = G1: significa pesquisar os critérios de G1 na faixa A2: A12;
    B2: B12 = G2: significa pesquisar os critérios de G2 na faixa B2: B12;
    D2: D12: o intervalo para o qual você deseja retornar o valor correspondente.
  • 2. Se você tiver mais de dois critérios, precisará apenas juntar os outros critérios na fórmula, como: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

Fórmula 2: Usando as funções INDEXT e MATCH

A combinação da função Índice e Correspondência também pode ser usada para retornar o valor correspondido com base em vários critérios. Copie ou insira a seguinte fórmula:

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Em seguida, pressione as teclas Ctrl + Shift + Enter juntas para obter o valor relativo conforme necessário. Veja a imagem:

notas:

  • 1. Na fórmula acima:
    A2: A12 = G1: significa pesquisar os critérios de G1 na faixa A2: A12;
    B2: B12 = G2: significa pesquisar os critérios de G2 na faixa B2: B12;
    D2: D12: o intervalo para o qual você deseja retornar o valor correspondente.
  • 2. Se você tiver mais de dois critérios, precisará apenas juntar os novos critérios à fórmula, como: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3. Vlookup para retornar vários valores correspondentes com uma ou mais condições

No Excel, a função Vlookup procura um valor e só retorna o primeiro valor correspondente se houver vários valores correspondentes encontrados. Às vezes, você pode querer retornar todos os valores correspondentes em uma linha, em uma coluna ou em uma única célula. Esta seção falará sobre como retornar os vários valores correspondentes com uma ou mais condições em uma pasta de trabalho.

Vlookup todos os valores correspondentes com base em uma ou mais condições horizontalmente

Vlookup todos os valores correspondentes com base em uma condição horizontalmente:

Para Vlookup e retornar todos os valores correspondentes com base em um valor específico horizontalmente, a fórmula genérica é:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
Importante: m é o número da linha da primeira célula no intervalo de retorno menos 1.
      n é o número da coluna da primeira célula da fórmula menos 1.

1. Por favor, aplique a fórmula abaixo em uma célula em branco e pressione Ctrl + Shift + Enter chaves juntas para obter o primeiro valor correspondido, consulte a captura de tela:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Em seguida, selecione a primeira célula da fórmula e arraste a alça de preenchimento para as células certas até que a célula em branco seja exibida e todos os itens correspondentes tenham sido extraídos, consulte a captura de tela:

Dicas:

Se houver valores correspondentes duplicados na lista retornada, para ignorar os duplicados, use esta fórmula e pressione Entrar para obter o primeiro resultado: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

Continue inserindo esta fórmula: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") em uma célula ao lado do primeiro resultado e, em seguida, pressione Ctrl + Shift + Enter juntas para obter o segundo resultado e, em seguida, arraste esta fórmula para as células certas para obter todos os outros valores correspondentes até que a célula em branco seja exibida, consulte a captura de tela:


Vlookup todos os valores correspondentes com base em duas ou mais condições horizontalmente:

Para Vlookup e retornar todos os valores correspondentes com base em valores mais específicos horizontalmente, a fórmula genérica é:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
Importante: m é o número da linha da primeira célula no intervalo de retorno menos 1.
      n é o número da coluna da primeira célula da fórmula menos 1.

1. Aplique a seguinte fórmula em uma célula em branco onde deseja produzir o resultado:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2. Em seguida, selecione a célula da fórmula e arraste a alça de preenchimento para as células certas até que a célula em branco seja exibida e todos os valores correspondentes com base nos critérios específicos tenham sido retornados, consulte a captura de tela:

Importante: Para obter mais critérios, você só precisa juntar lookup_value e lookup_range na fórmula, como: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


Vlookup todos os valores correspondentes com base em uma ou mais condições verticalmente

Vlookup todos os valores correspondentes com base em uma condição verticalmente:

Para Vlookup e retornar todos os valores correspondentes com base em um valor específico verticalmente, a fórmula genérica é:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
Importante: m é o número da linha da primeira célula no intervalo de retorno menos 1.
      n é o número da linha da primeira célula da fórmula menos 1.

1. Copie ou digite a seguinte fórmula em uma célula onde deseja obter o resultado e pressione Ctrl + Shift + Enter chaves juntas para obter o primeiro valor correspondido, consulte a captura de tela:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Em seguida, selecione a primeira célula da fórmula e arraste a alça de preenchimento para outras células até que a célula em branco seja exibida e todos os itens correspondentes tenham sido listados em uma coluna, veja a captura de tela:

Dicas:

Para ignorar as duplicatas nos valores correspondentes retornados, use estas fórmulas: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

Então aperte Ctrl + Shift + Enter juntas para obter o primeiro valor correspondido e, em seguida, arraste esta célula de fórmula para outras células até que a célula em branco seja exibida, e você obterá o resultado que precisa:


Vlookup todos os valores correspondentes com base em duas ou mais condições verticalmente:

Para Vlookup e retornar todos os valores correspondentes com base em valores mais específicos verticalmente, a fórmula genérica é:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
Importante: m é o número da linha da primeira célula no intervalo de retorno menos 1.
      n é o número da linha da primeira célula da fórmula menos 1.

1. Copie a fórmula abaixo em uma célula em branco e pressione Ctrl + Shift + Enter juntas para obter o primeiro item correspondente.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2. Em seguida, arraste a célula da fórmula para outras células até que a célula em branco seja exibida, veja a captura de tela:

Importante: Para obter mais critérios, você só precisa juntar lookup_value e lookup_range na fórmula, como: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


Vlookup todos os valores correspondentes com base em duas ou mais condições em uma única célula

Se você deseja Vlookup e retornar vários valores correspondentes em uma única célula com o separador especificado, a nova função de TEXTJOIN pode ajudá-lo a resolver esse trabalho de forma rápida e fácil.

Vlookup todos os valores correspondentes com base em uma condição em uma única célula:

Por favor, aplique a fórmula simples abaixo em uma célula em branco e pressione Ctrl + Shift + Enter chaves juntas para obter o resultado:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Dicas:

Para ignorar as duplicatas nos valores correspondentes retornados, use estas fórmulas: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


Vlookup todos os valores correspondentes com base em duas ou mais condições em uma única célula:

Para lidar com várias condições ao retornar todos os valores correspondentes em uma única célula, aplique a fórmula abaixo e pressione Ctrl + Shift + Enter chaves juntas para obter o resultado:

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

notas:

1. A função TEXTJOIN está disponível apenas no Excel 2019 e Office 365.

2. Se você usar o Excel 2016 e versões anteriores, use a Função Definida pelo Usuário dos artigos abaixo:


4. Vlookup para retornar uma linha inteira ou inteira de uma célula correspondente

Nesta seção, falarei sobre como recuperar a linha inteira de um valor correspondente usando a função Vlookup.

1. Copie ou digite a fórmula abaixo em uma célula em branco onde deseja enviar o resultado e pressione Entrar chave para obter o primeiro valor.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

2. Em seguida, arraste a célula da fórmula para o lado direito até que os dados de toda a linha sejam exibidos, veja a captura de tela:

Importante: Na fórmula acima, F2 é o valor de pesquisa em que você deseja retornar a linha inteira, A1: D12 é o intervalo de dados que você deseja usar, A1 indica o primeiro número da coluna em seu intervalo de dados.

Dicas:

Se várias linhas forem encontradas com base no valor correspondente, para retornar todas as linhas correspondentes, aplique esta fórmula: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),""), e depois pressione Ctrl + Shift + Enter juntas para obter o primeiro resultado e, em seguida, arraste a alça de preenchimento direto para as células, veja a captura de tela:

E, em seguida, arraste a alça de preenchimento para baixo através das células para obter todas as linhas correspondentes, conforme mostrado na imagem abaixo:


5. Executar várias funções Vlookup (Vlookup aninhado) no Excel

Às vezes, você pode querer pesquisar valores em várias tabelas, se qualquer uma das tabelas contiver o valor de pesquisa dado conforme a captura de tela mostrada abaixo, neste caso, você pode combinar uma ou mais funções Vlookup junto com a função IFERROR para realizar pesquisas múltiplas.

A fórmula genérica para a função Vlookup aninhada é:

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

Observação:

  • lookup_value: o valor que procura;
  • Table1, Table2, Table3, ...: as tabelas nas quais o valor de pesquisa e o valor de retorno existem;
  • col: o número da coluna na tabela da qual você deseja retornar o valor correspondente.
  • 0: Isso é usado para uma correspondência exata.

1. Aplique a seguinte fórmula em uma célula em branco onde deseja colocar o resultado:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2. Em seguida, arraste a alça de preenchimento para baixo até as células às quais deseja aplicar esta fórmula, e todos os valores correspondentes foram retornados conforme a captura de tela abaixo:

notas:

  • 1. Na fórmula acima, J3 é o valor que você procura; A3: B7, D3: E7, G3: H7 são os intervalos da tabela em que o valor de pesquisa e o valor de retorno existem; O número 2 é o número da coluna no intervalo do qual retornar o valor correspondente.
  • 2. Se o valor de pesquisa não puder ser encontrado, um valor de erro será exibido, para substituir o erro por um texto legível, use esta fórmula: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

6. Vlookup para verificar se o valor existe com base nos dados de uma lista em outra coluna

A função Vlookup também pode ajudá-lo a verificar se existem valores com base em outra lista, por exemplo, se você deseja procurar os nomes na coluna C e apenas retornar Sim ou Não se o nome for encontrado ou não na coluna A conforme imagem abaixo mostrando.

1. Aplique a seguinte fórmula em uma célula em branco:

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

2. Em seguida, arraste a alça de preenchimento para baixo até as células que deseja preencher esta fórmula, e você obterá o resultado que você precisa, veja a captura de tela:

Importante: Na fórmula acima, C2 é o valor de pesquisa que você deseja verificar; A2: A10 é a lista de intervalos de onde os valores de pesquisa serão encontrados; o número 1 é o número da coluna de onde você deseja buscar o valor em seu intervalo.


7. Vlookup e soma todos os valores correspondentes em linhas ou colunas

Se você trabalha com dados numéricos, às vezes, ao extrair os valores correspondentes da tabela, você também pode precisar somar os números em várias colunas ou linhas. Esta seção apresentará algumas fórmulas para finalizar este trabalho no Excel.

Vlookup e soma todos os valores correspondentes em uma linha ou várias linhas

Suponha que você tenha uma lista de produtos com vendas de vários meses, conforme a imagem mostrada abaixo, agora, você precisa somar todos os pedidos em todos os meses com base nos produtos fornecidos.

Vlookup e soma os primeiros valores correspondentes em uma linha:

1. Copie ou insira a seguinte fórmula em uma célula em branco e pressione Ctrl + Shift + Enter chaves juntas para obter o primeiro resultado.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

2. Em seguida, arraste a alça de preenchimento para baixo para copiar esta fórmula para outras células de que você precisa, e todos os valores em uma linha do primeiro valor correspondente foram somados, consulte a captura de tela:

Importante: Na fórmula acima: H2 é a célula que contém o valor que você está procurando; A2: F9 é o intervalo de dados (sem cabeçalhos de coluna) que inclui o valor de pesquisa e os valores correspondentes; O número 2,3,4,5,6 {} são os números das colunas usados ​​para calcular o total do intervalo.


Vlookup e soma todos os valores correspondentes em várias linhas:

A fórmula acima só pode somar valores em uma linha para o primeiro valor correspondido. Se você deseja somar todas as correspondências em várias linhas, use a seguinte fórmula e arraste a alça de preenchimento para baixo até as células que deseja aplicar esta fórmula, e você obterá o resultado desejado, veja a captura de tela:

=SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)

Importante: Na fórmula acima: H2 é o valor de pesquisa que você está procurando; A2: A9 são os cabeçalhos de linha que contêm o valor de pesquisa; B2: F9 o intervalo de dados dos valores numéricos que você deseja somar.


Vlookup e soma todos os valores correspondentes em uma coluna ou várias colunas

Vlookup e soma os primeiros valores correspondentes em uma coluna:

Se você quiser somar o valor total para os meses específicos, conforme mostrado na imagem abaixo.

Aplique a fórmula abaixo em uma célula em branco e arraste a alça de preenchimento para baixo para copiar esta fórmula para outras células, agora, os primeiros valores correspondentes com base no mês específico em uma coluna foram somados, veja a captura de tela:

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Importante: Na fórmula acima: H2 é o valor de pesquisa que você está procurando; B1: F1 são os cabeçalhos das colunas que contêm o valor de pesquisa; B2: F9 o intervalo de dados dos valores numéricos que você deseja somar.


Vlookup e soma todos os valores correspondentes em várias colunas:

Para Vlookup e somar todos os valores correspondentes em várias colunas, você deve usar a seguinte fórmula:

=SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))

Importante: Na fórmula acima: H2 é o valor de pesquisa que você está procurando; B1: F1 são os cabeçalhos das colunas que contêm o valor de pesquisa; B2: F9 o intervalo de dados dos valores numéricos que você deseja somar.


Vlookup e soma os primeiros valores combinados ou todos os valores combinados com um recurso poderoso

Talvez as fórmulas acima sejam difíceis de lembrar, neste caso, vou recomendar um recurso útil - Pesquisa e Soma of Kutools for Excel, com esse recurso, você pode obter o resultado o mais fácil possível.    Clique para baixar Kutools para Excel agora!


Vlookup e soma todos os valores correspondentes em linhas e colunas

Se você deseja somar os valores, quando você precisa combinar coluna e linha, por exemplo, para obter o valor total da camisola do produto no mês de março conforme a imagem abaixo mostrada.

Aplique a seguinte fórmula em uma célula e pressione a tecla Enter para obter o resultado, veja a captura de tela:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

Importante: Na fórmula acima: B2: F9 é o intervalo de dados dos valores numéricos que você deseja somar; B1: F1 is os cabeçalhos das colunas contêm o valor de pesquisa que você deseja somar com base; I2 é o valor de pesquisa nos cabeçalhos de coluna que você está procurando; A2: A9 é que os cabeçalhos de linha contêm o valor de pesquisa no qual você deseja somar; H2 é o valor de pesquisa nos cabeçalhos de linha que você está procurando.


8. Vlookup para mesclar duas tabelas com base em uma ou mais colunas-chave

Em seu trabalho diário, ao analisar os dados, você pode precisar reunir todas as informações necessárias em uma única tabela com base em uma ou mais colunas-chave. Para resolver esse trabalho, a função Vlookup também pode fazer um favor.

Vlookup para mesclar duas tabelas com base em uma coluna-chave

Por exemplo, você tem duas tabelas, a primeira tabela contém os dados de produtos e nomes e a segunda tabela contém os produtos e pedidos. Agora, você deseja combinar essas duas tabelas combinando a coluna de produto comum em uma tabela.

Fórmula 1: usando a função VLOOKUP

Para mesclar as duas tabelas em uma com base em uma coluna-chave, aplique a seguinte fórmula em uma célula em branco onde deseja obter o resultado e, em seguida, arraste a alça de preenchimento para baixo até as células às quais deseja aplicar esta fórmula. obter uma tabela mesclada com a coluna de ordem juntando-se aos dados da primeira tabela com base nos dados da coluna-chave.

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

Importante: Na fórmula acima, A2 é o valor que você procura, E2: F8 é a mesa para pesquisar, o número 2 é o número da coluna na tabela da qual recuperar o valor.

Fórmula 2: Usando as funções INDEX e MATCH

Se seus dados comuns estão no lado direito e os dados retornados na coluna esquerda dentro da segunda tabela, para mesclar a coluna de ordem, a função Vlookup é incapaz de fazer o trabalho. Para olhar da direita para a esquerda, você pode usar as funções INDEX e MATCH para substituir a função Vlookup.

Copie ou insira a fórmula abaixo em uma célula em branco e, em seguida, copie a fórmula para baixo na coluna, e a coluna de ordem foi unida à primeira tabela, veja a captura de tela:

=INDEX($E$2:$E$8, MATCH($A2, $F$2:$F$8, 0))

Importante: Na fórmula acima, A2 é o valor de pesquisa que você está procurando, E2: E8 é o intervalo de dados que você deseja retornar, F2: F8 é o intervalo de pesquisa que contém o valor de pesquisa.


Vlookup para mesclar duas tabelas com base em várias colunas de chave

Se as duas tabelas que você deseja unir têm várias colunas de chave, para mesclar as tabelas com base nessas colunas comuns, as funções INDEX e MATCH podem ajudá-lo.

A fórmula genérica para mesclar duas tabelas com base em várias colunas-chave é:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

1. Por favor, aplique a fórmula abaixo em uma célula em branco onde você deseja colocar o resultado e pressione Ctrl + Shift + Enter chaves juntas para obter o primeiro valor correspondido, consulte a captura de tela:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Importante: Na fórmula acima, o que as referências de célula representam conforme a captura de tela abaixo:

2.Em seguida, selecione a primeira célula da fórmula e arraste a alça de preenchimento para copiar esta fórmula para outras células conforme necessário:

Tips: No Excel 2016 e versões posteriores, você também pode usar o Consulta de energia recurso para mesclar duas ou mais tabelas em uma com base em colunas-chave. Clique para saber os detalhes passo a passo.

9. Vlookup correspondendo valores em várias planilhas

Você já tentou Vlookup valores em várias planilhas? Supondo que eu tenha as três planilhas a seguir com intervalo de dados, e agora, quero obter parte dos valores correspondentes com base nos critérios dessas três planilhas para obter o resultado conforme a captura de tela mostrada abaixo. Neste caso, o Valores de Vlookup em várias planilhas o tutorial pode lhe fazer um favor passo a passo.


Os valores correspondentes de VLOOKUP mantêm a formatação da célula

1. Vlookup para obter a formatação da célula (cor da célula, cor da fonte) junto com o valor de pesquisa

Como todos sabemos, a função normal do Vlookup só pode nos ajudar a retornar o valor correspondido de outro intervalo de dados, mas às vezes você pode querer retornar o valor correspondente junto com a formatação da célula, como cor de preenchimento, cor da fonte, estilo da fonte como a imagem abaixo mostrada. Esta seção falará sobre como obter a formatação da célula com o valor retornado no Excel.

Siga as etapas a seguir para pesquisar e retornar seu valor correspondente junto com a formatação da célula:

1. Na planilha que contém os dados que você deseja ver, clique com o botão direito na guia da planilha e selecione Ver código no menu de contexto. Veja a imagem:

2. No aberto Microsoft Visual Basic para Aplicações janela, copie o código VBA abaixo na janela Código.

Código VBA 1: Vlookup para obter a formatação da célula junto com o valor de pesquisa

Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    Dim I As Long
    Dim xKeys As Long
    Dim xDicStr As String
    On Error Resume Next
    Application.ScreenUpdating = False
    xKeys = UBound(xDic.Keys)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                Range(xDic.Keys(I)).Interior.Color = _
                Range(xDic.Items(I)).Interior.Color
                Range(xDic.Keys(I)).Font.FontStyle = _
                Range(xDic.Items(I)).Font.FontStyle
                Range(xDic.Keys(I)).Font.Size = _
                Range(xDic.Items(I)).Font.Size
                Range(xDic.Keys(I)).Font.Color = _
                Range(xDic.Items(I)).Font.Color
                Range(xDic.Keys(I)).Font.Name = _
                Range(xDic.Items(I)).Font.Name
                Range(xDic.Keys(I)).Font.Underline = _
                Range(xDic.Items(I)).Font.Underline
            Else
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Next
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = True
End Sub

3. Ainda no Microsoft Visual Basic para Aplicações janela, clique em inserção > Móduloe, em seguida, copie o código 2 do VBA abaixo na janela Módulo.

Código VBA 2: Vlookup para obter a formatação da célula junto com o valor de pesquisa

Public xDic As New Dictionary
Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
    Dim xFindCell As Range
    On Error Resume Next
    Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
    If xFindCell Is Nothing Then
        LookupKeepFormat = ""
        xDic.Add Application.Caller.Address, ""
    Else
        LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
    End If
End Function

4. Após inserir os códigos acima, clique em Ferramentas > Referências no Microsoft Visual Basic para Aplicações janela. Em seguida, verifique o Microsoft Script Runtime caixa de seleção no Referências - VBAProject caixa de diálogo. Veja as capturas de tela:

5. Então clique OK para fechar a caixa de diálogo, salvar e fechar a janela de código, agora, volte a planilha e aplique esta fórmula: =LookupKeepFormat(E2,$A$1:$C$10,3) em uma célula em branco onde deseja produzir o resultado e, em seguida, pressione a tecla Enter. Veja a imagem:

Importante: Na fórmula acima, E2 é o valor que você procurará, A1: C10 é o intervalo da mesa e o número 3 é o número da coluna da tabela cujo valor correspondente deve ser retornado.

6. Em seguida, selecione a primeira célula de resultado e arraste a alça de preenchimento para baixo para obter todos os resultados junto com sua formatação. Veja a imagem.


2. Mantenha o formato de data de um valor retornado pelo Vlookup

Normalmente, ao usar a função Vloook para pesquisar e retornar o valor do formato de data correspondente, algum formato de número será exibido conforme a captura de tela abaixo. Para manter o formato de data de um resultado retornado, você deve incluir a função TEXT na função Vlookup.

Por favor, aplique a fórmula abaixo em uma célula em branco e arraste a alça de preenchimento para copiar esta fórmula para outras células, e todas as datas correspondentes foram retornadas conforme a captura de tela abaixo:

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Importante: Na fórmula acima, E2 é o valor da aparência, A2: C9 é o intervalo de pesquisa, o número 3 é o número da coluna que você deseja que o valor retorne, mm/dd/yyy é o formato de data que você deseja manter.


3. Vlookup e retorna o valor correspondente com o comentário da célula

Você já tentou o Vlookup para retornar não apenas os dados da célula correspondida, mas também o comentário da célula, bem como no Excel, conforme mostrado a seguir? Para resolver esta tarefa, a função definida pelo usuário abaixo pode lhe fazer um favor.

1. Segure o ALT + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe, em seguida, copie e cole o código a seguir na janela do módulo.

Código VBA: Vlookup e valor correspondente de retorno com comentário de célula:

Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
'Updateby Extendoffice
    Application.Volatile
    Dim xRet As Variant 'could be an error
    Dim xCell As Range
    xRet = Application.Match(LookVal, FTable.Columns(1), FType)
    If IsError(xRet) Then
        VlookupComment = "Not Found"
    Else
        Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
        VlookupComment = xCell.Value
        With Application.Caller
            If Not .Comment Is Nothing Then
                .Comment.Delete
            End If
            If Not xCell.Comment Is Nothing Then
                .AddComment xCell.Comment.Text
            End If
        End With
    End If
End Function

3. Em seguida, salve e feche a janela de código, insira esta fórmula: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) em uma célula em branco para localizar o resultado e, em seguida, arraste a alça de preenchimento para copiar esta fórmula para outras células, agora, os valores correspondentes, bem como os comentários são retornados de uma vez, veja a captura de tela:

Importante: Na fórmula acima, D2 é o valor de pesquisa que você deseja que retorne seu valor correspondente, A2: B9 é a tabela de dados que você deseja usar, o número 2 é o número da coluna que contém o valor correspondente que você deseja retornar.


4. Lide com o texto e números reais no Vlookup

Por exemplo, eu tenho um intervalo de dados, o número de ID na tabela original é o formato de número, na célula de pesquisa que é armazenada como texto, ao aplicar a função Vlookup normal, um resultado de erro # N / A é exibido como a imagem abaixo mostrando. Nesse caso, como você poderia obter as informações corretas se o número de pesquisa e o número original na tabela tivessem formatos de dados diferentes?

Para lidar com o texto e os números reais na função Vlookup, aplique a seguinte fórmula em uma célula em branco e arraste a alça de preenchimento para baixo para copiar esta fórmula, e você obterá os resultados corretos conforme a captura de tela abaixo:

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

notas:

  • 1. Na fórmula acima, D2 é o valor de pesquisa que você deseja que retorne seu valor correspondente, A2: B8 é a tabela de dados que você deseja usar, o número 2 é o número da coluna que contém o valor correspondente que você deseja retornar.
  • 2. Essa fórmula também funciona bem se você não tiver certeza de onde há números e texto.

Baixar arquivos de amostra de VLOOKUP

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • Super Formula Bar (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
  • Mesclar células / linhas / colunas e manutenção de dados; Dividir o conteúdo das células; Combine Linhas Duplicadas e Soma / Média... Evite células duplicadas; Comparar intervalos...
  • Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
  • Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
  • Fórmulas favoritas e de inserção rápida, Intervalos, gráficos e imagens; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
  • Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
  • Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
  • Agrupamento de tabela dinâmica por número da semana, dia da semana e mais ... Mostrar células desbloqueadas, bloqueadas por cores diferentes; Destacar células que possuem fórmula / nome...
guia kte 201905
  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.