Verifique com que palavra-chave, de uma lista, uma célula começa e retorna a palavra-chave correspondente

Question

Estou tentando obter uma lista de produtos e extrair o fabricante deles desde o início do nome do produto. Cada nome de produto começa com o fabricante. Isso é complicado pelo fato de alguns itens terem outros fabricantes no corpo do nome; Eu preciso ver o que o item começa com. Estou lidando com mais de 50.000 itens e mais de 3.000 fabricantes. A fórmula que tenho até agora é:

=LOOKUP(1,1/(FIND($C$2:$C$5,B2)),$C$2:$C$5)

Isso funciona algumas vezes, mas não em outras. Por exemplo, na planilha abaixo, as linhas 2 e 3 estão corretas, mas a linha 4 não é. O resultado na célula A4 para "Mike's Fun Toys" (na célula B4 ) deve ser "Mike's", mas surge como "diversão".

(Vejaosdadosemformatodetextoquevocêpodecopiarecolar:

+---+---------+-----------------+---------------+||A|B|C|+---+---------+-----------------+---------------+|1|Formula|Items|Manufacturers|+---+---------+-----------------+---------------+|2|Brown|BrownCatToys|Mike's||3|Cat|CatFunToys|Fun||4|Fun|Mike'sFunToys|Cat||5|||Brown|+---+---------+-----------------+---------------+

MasquandoalteroaordemdacolunaC(fabricantes):

alinha4setornacorreta("Mike's"), mas a linha 2 está errada.

A coluna A tem a fórmula durante todo o caminho. Resultados esperados:

A2 - Brown
A3 - Cat
A4 - Mike's

Como posso fazer com que a fórmula funcione independentemente da ordem da coluna C ?

    

Answer 1

Para o benefício dos leitores que não entendem a fórmula do RyanMark, é

• Encontrar a posição de cada um dos nomes dos fabricantes em um determinado nome de produto. Isto resulta em
  • um valor de 1 para o nome do fabricante que começa o nome do produto (porque aparece no 1 st),
  • números mais altos para nomes de outros fabricantes que aparecem no nome do produto (porque aparecem mais tarde, em números de posição mais alta) e
  • um código de erro #VALUE! para nomes de fabricantes que não aparecem no nome do produto.
• Invertendo cada um dos itens acima (dividindo 1 por ele), resultando em
  • 1 para o nome do fabricante que começa o nome do produto (que é o que queremos encontrar),
  • números positivos mais baixos para nomes de outros fabricantes que aparecem no nome do produto (porque 1 dividido por um número maior que 1 produz uma proporção menor que 1) e
  • um código de erro #VALUE! para nomes de fabricantes que não aparecem no nome do produto.
• Usando LOOKUP para encontrar o 1 acima.

Por exemplo, para a célula A4 (correspondente a "Mike's Fun Toys" na célula B4 ), na primeira imagem, nós temos, em ordem,

• 1 , porque “Mike's” ( C2 ) começa “Mike's Fun Toys”,
• %código%, porque “Fun” ( 8 ) aparece no 8º personagem de “Mike's Fun Toys”, e
• C3 e #VALUE! , porque "Cat" ( #VALUE! ) e "Brown" ( C4 ) não aparecem em "Mike's Fun Toys".

Inversão que resulta em C5 , 1 ( 0.125 ), 1/8 e #VALUE! . Em seguida, ele procura o #VALUE! nessa matriz. Isso “deveria” funcionar, porque 1 é o primeiro resultado, e "Mike's" é o primeiro nome na coluna 1 .

O problema pode ser visto na página de ajuda de C :

For the LOOKUP function to work correctly, the data being looked up must be sorted in ascending order.

e claramente LOOKUP seguido por 1 não é classificado em ordem crescente.

Como o 0.125 sugere, podemos resolver isso usando LOOKUP . A fórmula que você quer, que usa a mesma abordagem básica de sua fórmula (exceto sem a inversão, que é desnecessária), é

=INDEX($C$2:$C$5, MATCH(1, FIND($C$2:$C$5,$B2), 0))

O terceiro argumento para MATCH é chamado de "match_type". Eu configurei para MATCH aqui, o que significa que 0 procurará o primeiro elemento na matriz isso é exatamente MATCH , e não assumirá que a matriz está ordenada.

Esta é uma fórmula de matriz então você tem que pressionar Ctrl + Deslocar + Enter quando você entra.