Como Fazer Busca Invertida no Excel

Veja neste artigo como fazer busca invertida no Excel. Localizar com fórmula no Excel em qualquer posição, inclusive invertida.

1. Introdução Busca Invertida no Excel

Podemos ter um caso de busca especial no Excel em que a partir de um certo valor presente nos dados desejamos encontrar um valor de referência no rótulo da coluna.

Considere os dados a seguir.

Como Fazer Busca invertida no excel 1

Ao escolher um certo modelo de veículo, cuja posição poderá ocupar qualquer uma das 3 colunas a seguir, como podemos retornar o nome da montadora, presente no rótulo de colunas? Se escolhermos Camaro, por exemplo, o nosso resultado desejado será Chevrolet, visto que o modelo de veículo em questão está na segunda coluna, referente a esta montadora (Chevrolet).

Desta forma, precisamos ter alguma solução para pesquisar um dado e retornar o rótulo de coluna correto. Este problema é típico dos dados desestruturados e pode ser resolvido facilmente com o Power Query, baseado em uma simples transformação de linhas em colunas.

Veja na imagem a seguir o resultado de uma simples transformação destes dados no Power Query:

Como Fazer Busca invertida no excel 2

Esta transformação foi realizada com a transformação de colunas em linhas, partindo da seleção da tabela completa (CTRL + A).

Como Fazer Busca invertida no excel 3

Partindo dos dados transformados, uma simples mesclagem já resolve nosso problema. O recomendável é a solução a partir do Power Query. No entanto, este artigo objetiva resolver este problema com fórmulas, apresentando algumas soluções a seguir.

2. Fazendo a busca

Apresentaremos 5 soluções distintas com fórmulas, tomando como base as funções SE, CONCAT, ÍNDICE, INDIRETO e ESCOLHER.

a) Busca Invertida Usando SE e CORRESP no Excel

Esta solução não resulta em uma fórmula curta, mas é uma fórmula didática. Usamos a função CORRESP para verificar a existência do modelo do veículo pesquisado na 1ª e na 2ª colunas. Quando CORRESP encontra o modelo, é retornado um número inteiro indicando a sua posição na coluna onde o modelo do veículo foi localizado. Com ÉNÚM, validamos se o resultado de CORRESP é satisfatório, uma vez que teremos erro nos casos em que CORRESP não localiza o modelo do veículo pesquisado. Com a função SE, verificamos qual CORRESP resulta em número e saí sabemos qual das duas primeiras colunas possui o modelo pesquisado. Caso o modelo pesquisado não esteja na 1ª e na 2ª colunas, estará na 3ª coluna por consequência.

Como Fazer Busca invertida no excel 4

A fórmula descrita e inserida foi: =SE(ÉNÚM(CORRESP(G2;B3:B13;0));B2;SE(ÉNÚM(CORRESP(G2;C3:C13;0));C2;D2)).

b) Usando SE e CONCAT

A fórmula aqui apresentada é incrivelmente simples e curta. Comparamos todos os modelos de veículos nos dados com o modelo escolhido (B3:D13=G2) e retornamos os cabeçalhos. Quando o resultado da comparação é VERDADEIRO, restará apenas o cabeçalho da respectiva montadora. O valor FALSO para SE será vazio. Desta forma, teremos muitos vazios e apenas um resultado diferente: a nossa montadora. Quando concatenamos tudo com CONCAT, teremos o nosso resultado desejado.

Como Fazer Busca invertida no excel 5

A fórmula descrita e inserida foi: =CONCAT(SE(B3:D13=G2;B2:D2;””)).

c) Usando ÍNDICE e MÁXIMO

Aqui fazemos a mesma comparação do item b: comparamos cada modelo de veículos dos dados com o modelo do veículo escolhido. Nosso objetivo é identificar a comparação verdadeira para obter um número de 1 a 3, que será usado para definir a coluna desejada. A comparação realizada resulta em verdadeiro ou falsos que multiplicarão o vetor gerado com COL (indo de 1 a 3). Desta forma, com a função MÁXIMO, retornamos o maior valor das multiplicações e teremos o número que indica a posição da coluna desejada. Esta posição é usada em ÍNDICE para que retornemos o nome da montadora.

Como Fazer Busca invertida no excel 6

A fórmula descrita e inserida foi: =ÍNDICE(B2:D2;MÁXIMO((B3:D13=G2)*(COL(B2:D2)-1))).

d) Usando INDIRETO e ENDEREÇO

Aqui fazemos a mesma comparação dos itens b e c: comparamos cada modelo de veículos nos dados com o modelo pesquisado. A função COL, de maneira auxiliar, fornece um vetor de 1 a 3 (da mesma forma que realizamos no item c) que multiplica os resultados das comparações. Da mesma forma que ocorreu no item c, teremos resultados zerados e apenas um número inteiro entre 1 e 3, indicando a posição da coluna desejada. Para obter apenas esta posição, usamos a função SOMA (diferentemente da função MÁXIMO do item anterior). De posse desta posição, usamos a função ENDEREÇO para obter o endereço da célula do nosso resultado desejado, o que se converte em referência válida com INDIRETO. Após obter a posição desejada, poderíamos também usar DESLOC.

Como Fazer Busca invertida no excel 7

A fórmula descrita e inserida foi: =INDIRETO(ENDEREÇO(LIN($B$2);SOMA((B3:D13=G2)*COL(B2:D2)))). Com DESLOC a nossa fórmula seria: =DESLOC(A2;;SOMA((B3:D13=G2)*COL(B2:D2))-1).

e) Usando ESCOLHER e SOMA

Esta última abordagem usa o mesmo princípio da solução anterior em que encontramos a posição da montadora desejada, que pode ser 1, 2 ou 3. Com a função ESCOLHER, fornecemos os endereços das 3 opções possíveis de montadoras a serem retornadas como resultados: B2, C2 e D2, que são os resultados potenciais.

Como Fazer Busca invertida no excel 8

A fórmula descrita e inserida foi: =ESCOLHER(SOMA((B3:D13=G2)*(COL(B2:D2)-1));B2;C2;D2).

Download Planilha Busca Invertida Excel

Clique no botão abaixo para realizar o  download do arquivo do exemplo:

Baixe a planilha


Jardiel Euflázio

Administrador, MOS e MOSE em MS Excel, Jardiel é também MCT e desenvolvedor de soluções baseadas na ferramenta. Apaixonado pelo Excel desde cedo, é criador de conteúdo em seu canal de vídeos aberto ao público no YouTube (Jardiel Euflázio - Excel Prime), no qual trata sobre o tema sempre disposto a colaborar com a comunidade.

Excel não precisa ser complicado

Assine nossa newsletter e receba dicas práticas para dominar o excel