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.
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:
Esta transformação foi realizada com a transformação de colunas em linhas, partindo da seleção da tabela completa (CTRL + A).
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.
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.
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.
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.
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.
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:
