INDEX-MATCH para correspondência parcial da linha de referência com vários valores de pesquisa

Question

Eu tenho as duas tabelas abaixo (abreviadas):

A1:A5 - Tabela de dados

URL
shop.samsung.com/au/accessories/mobile-accessories
samsung.com/us/amp/us/smartphones/galaxy-s9
samsung.com/us/appliances/electric-ranges
samsung.com/global/business/semiconductor/minisite/SSD/notice.html

E1:F5 - Tabela de Referência

If Folder Contains          Value
mobile-accessories          Mobile Accessories
smartphones                 Mobile Phone
appliances/electric-ranges  Cooking Appliance
appliances/refrigerators    Semiconductor

Estou tentando criar uma fórmula que examinará a tabela de URLs, verificará se a string tem uma correspondência parcial na coluna "Se a pasta contém" da tabela de referência e retornará o valor. Por exemplo, se eu escolher o primeiro link (A2), esperaria que a fórmula retornasse "Acessórios para celular", pois A2 contém "acessórios para celular" no URL.

O inverso eu sei fazer (basta fazer um vlookup com um curinga antes/depois), mas não consigo descobrir uma maneira de fazer isso. Qualquer orientação será apreciada

Answer 1

Apenas me perguntando seeletrodomésticos/geladeirasdeveria mapear paraSemicondutorcomo você mostrou no seu exemplo. Eu embora possa sernegócio/semicondutorpode mapear paraSemicondutor. Mas isso não é um problema.

Neste exemplo, os dados de URL de amostra estão em A1:A5, a tabela de mapeamento está em E1:F5. Agora em B2 coloque a seguinte fórmula e pressione CTRL+ SHIFT+ ENTERna barra de fórmulas para criar uma Fórmula de Matriz. A fórmula agora deve ser colocada entre chaves para indicar que é uma fórmula de matriz.

Arraste-o para baixo até as linhas pretendidas.

=IFERROR(INDEX($F$2:$F$5,MAX(IF(ISERROR(SEARCH($E$2:$E$5,A2)),0,ROW($E$2:$E$5))-ROW($F$1))),"No Category")

O IFERROR externo é adicionado apenas para fins cosméticos, apenas para mostrar o texto 'Sem categoria' se nenhuma correspondência for encontrada. Se sua versão do EXCEL for anterior a 2007, você pode abandoná-la. Em vez da mensagem "Sem categoria", a fórmula deve apenas retornar erro para tais instâncias ou, alternativamente, você também pode usar a combinação de construção IF(ISERROR()).

Answer 2

Para resolver o problema, você precisa de uma coluna auxiliar para armazenar strings de correspondência parcial.

Escreva esta fórmula na célula N41e preencha.

=VLOOKUP($N36,$O36:$O36,1,TRUE)

Ajuste as referências de células na fórmula conforme necessário.