✅ Como tornar o Excel VLOOKUP sensível a maiúsculas e minúsculas

Índice

Tornando VLOOKUP sensível a maiúsculas e minúsculas

Por padrão, o valor de pesquisa na função VLOOKUP não diferencia maiúsculas de minúsculas. Por exemplo, se seu valor de pesquisa for MATT, matt ou Matt, é tudo o mesmo para a função VLOOKUP. Ele retornará o primeiro valor correspondente, independentemente do caso.

Tornando VLOOKUP sensível a maiúsculas e minúsculas

Suponha que você tenha os dados conforme mostrado abaixo:

Como você pode ver, existem três células com o mesmo nome (A2, A4 e A5), mas com letras maiúsculas e minúsculas diferentes. À direita (em E2: F4), temos os três nomes (Matt, MATT e matt) junto com suas pontuações em matemática.

A função VLOOKUP do Excel não está equipada para lidar com valores de pesquisa que diferenciam maiúsculas de minúsculas. Neste exemplo acima, não importa qual caso de valor de pesquisa é (Matt, MATT ou matt), ele sempre retornará 38 (que é o primeiro valor correspondente).

Neste tutorial, você aprenderá como diferenciar maiúsculas de minúsculas em VLOOKUP ao:

Usando uma coluna auxiliar.
Sem usar uma coluna auxiliar e usando uma fórmula.

Tornando VLOOKUP sensível a maiúsculas e minúsculas - usando a coluna auxiliar

Uma coluna auxiliar pode ser usada para obter um valor de pesquisa exclusivo para cada item na matriz de pesquisa. Isso ajuda a diferenciar nomes com letras maiúsculas e minúsculas diferentes.

Aqui estão as etapas para fazer isso:

Insira uma coluna auxiliar à esquerda da coluna de onde deseja buscar os dados. No exemplo abaixo, você precisa inserir a coluna auxiliar entre as colunas A e C.
Na coluna auxiliar, insira a fórmula = ROW (). Ele irá inserir o número da linha em cada célula.
Use a seguinte fórmula na célula F2 para obter o resultado da pesquisa que diferencia maiúsculas de minúsculas.
```
= VLOOKUP (MAX (EXACT (E2, $ A $ 2: $ A $ 9) * (ROW ($ A $ 2: $ A $ 9))), $ B $ 2: $ C $ 9,2,0)
```
Copie e cole para as células restantes (F3 e F4).

Observação: Como esta é uma fórmula de matriz, use Control + Shift + Enter em vez de apenas Enter.

Como é que isso funciona?

Vamos analisar a fórmula para entender como ela funciona:

EXACT (E2, $ A $ 2: $ A $ 9) - Esta parte compara o valor de pesquisa em E2 com todos os valores em A2: A9. Ele retorna uma matriz de TRUEs / FALSEs onde TRUE é retornado quando há uma correspondência exata. Nesse caso, onde o valor em E2 é Matt, ele retornaria a seguinte matriz:
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
EXACT (E2, $ A $ 2: $ A $ 9) * (ROW ($ A $ 2: $ A $ 9) - Esta parte multiplica a matriz de VERDADEIROS / FALSOS pelo número da linha de A2: A9. Sempre que houver um VERDADEIRO, dá o número da linha, caso contrário, dá 0. Nesse caso, ele retornaria {2; 0; 0; 0; 0; 0; 0; 0}.
MAX (EXACT (E2, $ A $ 2: $ A $ 9) * (ROW ($ A $ 2: $ A $ 9))) - Esta parte retorna o valor máximo da matriz de números. Nesse caso, ele retornaria 2 (que é o número da linha onde há uma correspondência exata).
Agora, simplesmente usamos esse número como o valor de pesquisa e usamos a matriz de pesquisa como B2: C9.

Observação: você pode inserir a coluna auxiliar em qualquer lugar do conjunto de dados. Apenas certifique-se de que esteja à esquerda da coluna de onde você deseja buscar os dados. Você precisa então ajustar o número da coluna na função VLOOKUP de acordo.

Agora, se você não é fã da coluna auxiliar, também pode fazer uma pesquisa com distinção entre maiúsculas e minúsculas sem a coluna auxiliar.

Tornando VLOOKUP sensível a maiúsculas e minúsculas - sem a coluna auxiliar

Mesmo quando você não deseja usar a coluna auxiliar, ainda precisa ter uma coluna auxiliar virtual. Esta coluna virtual não faz parte da planilha, mas é construída dentro da fórmula (conforme mostrado abaixo).

Aqui está a fórmula que fornecerá o resultado sem a coluna auxiliar:

= VLOOKUP (MAX (EXACT (D2, $ A $ 2: $ A $ 9) * (ROW ($ A $ 2: $ A $ 9))), ESCOLHER ({1,2}, ROW ($ A $ 2: $ A $ 9) , $ B $ 2: $ B $ 9), 2,0)

Como é que isso funciona?

A fórmula também usa o conceito de coluna auxiliar. A diferença é que, em vez de colocar a coluna auxiliar na planilha, considere-a como dados auxiliares virtuais que fazem parte da fórmula.

Aqui está a parte que funciona como dados auxiliares (destacados em laranja):

= VLOOKUP (MAX (EXACT (D2, $ A $ 2: $ A $ 9) * (ROW ($ A $ 2: $ A $ 9))),ESCOLHER ({1,2}, ROW ($ A $ 2: $ A $ 9), $ B $ 2: $ B $ 9),2,0)

Deixe-me mostrar o que quero dizer com dados do auxiliar virtual.

Na ilustração acima, conforme eu seleciono a parte ESCOLHER da fórmula e pressiono F9, ela mostra o resultado que a fórmula ESCOLHER daria.

O resultado é {2,38; 3,88; 4,57; 5,82; 6,55; 7,44; 8,75; 9,38}

É uma matriz em que uma vírgula representa a próxima célula na mesma linha e o ponto e vírgula representa que os dados a seguir estão na próxima linha. Portanto, esta fórmula cria 2 colunas de dados - uma coluna tem o número da linha e a outra tem a pontuação matemática.

Agora, quando você usa a função VLOOKUP, ela simplesmente procura o valor de pesquisa na primeira coluna (desses dados de 2 colunas virtuais) e retorna a pontuação correspondente. O valor de pesquisa aqui é um número que obtemos da combinação das funções MAX e EXACT.

Baixe o arquivo de exemplo