Como usar o vlookup para procurar palavras no Excel?

Question

Aqui está uma maquete dos meus dados:

A coluna I tem categorias de comida e a coluna H tem os códigos para essas categorias.

A coluna E tem subcategorias, com as quais preciso trabalhar. Ele contém o nome da categoria mais um número seqüencial que distingue a subcategoria.

Nas colunas F e G, preciso encontrar o código e o nome da categoria pai nas colunas H e I. A imagem mostra a coluna G já preenchida.

Os dados reais têm 348 categorias e 1000 subcategorias.

Eu tentei usar Vlookup, o que me dá # N / A, um valor não disponível erro. Por exemplo, tentei isso para o valor da coluna G:

=VLOOKUP(E4,H4:I6,2,FALSE)  

Como pesquiso os valores das colunas F e G para cada valor na coluna E?

    

Answer 1

Você tem duas colunas para preencher. Vamos começar com o nome da categoria na coluna G. Tentar fazer isso com VLOOKUP seria complicado porque os valores na coluna E não correspondem aos valores da coluna I. Entretanto, você está na sorte por causa da sua convenção de nomenclatura. Os nomes das subcategorias da coluna E contêm o nome da categoria como a primeira parte e o número da sequência é sempre de três dígitos. Podemos usar isso para extrair diretamente o nome da categoria.

O Excel tem funções de cadeia de caracteres para fazer esse tipo de manipulação de cadeias de caracteres. LEFT (string, número) lhe dará o número dos caracteres mais à esquerda em string . Da mesma forma, RIGHT (string, number) forneceria o número dos caracteres mais à direita. Então, se você quisesse extrair o número sequencial da subcategoria para E4, seria: DIREITO (E4,3).

Outra função de string é LEN, que lhe dá o comprimento (número de caracteres) em uma string. Para extrair o nome da categoria para G4, que é todos os caracteres, exceto os quatro últimos (ou seja, espaço mais número sequencial), você pode usar:

=LEFT(E4,LEN(E4)-4)

Agora você tem o nome da categoria e pode usá-lo para pesquisar o código de categoria associado. Infelizmente, o VLOOKUP possui uma limitação de que a comparação é sempre feita na coluna mais à esquerda do intervalo de pesquisa. Sua tabela está configurada com o código na coluna mais à esquerda. Em vez disso, podemos usar uma combinação de INDEX e MATCH. MATCH localizará um valor correspondente e ÍNDICE selecionará o valor associado de uma coluna diferente. Na F4, podemos usar:

=INDEX(H4:H6,MATCH(G4,I4:I6,0))

ÍNDICE retorna o valor do intervalo H4: H6 para a linha dentro desse intervalo dada pela função MATCH. MATCH identifica a linha em I4: I6, em que o valor é uma correspondência exata com o valor em G4 (o 0 no final designa uma correspondência exata, portanto, a sequência dos dados não é importante). Neste exemplo, Fruit corresponde à primeira linha dentro do intervalo, então INDEX retorna X040001 da primeira linha do intervalo.