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

Question

Eu tenho as duas tabelas abaixo (encurtadas):

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 examine a tabela de URLs, verifique se a string tem uma correspondência parcial na coluna "Se a pasta contém" da tabela de referência e retorne o valor. Por exemplo, se eu escolher o primeiro link (A2), esperaria que a fórmula retornasse "Acessórios para celular", já que A2 contém "acessórios para celular" na URL.

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

    

Answer 1

Basta saber se appliances / refrigerators deve mapear para Semiconductor como você mostrou em seu exemplo. Embora eu possa ser negócio / semicondutor pode mapear para Semiconductor . Mas isso não é um problema.

Neste exemplo, os dados da 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 + ENTER dentro da barra de fórmulas para criar uma fórmula de matriz. A fórmula agora deve ser incluída em Chaves Encaracoladas para indicar que é uma fórmula de matriz.

Arraste 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")

OIFERRORexternoéadicionadoapenasparafinscosméticos,apenasparamostrarotexto'SemCategoria'senãoforencontradacorrespondência.SenocasodesuaversãodoEXCELseranteriora2007,vocêpodedescartá-la.Emvezdamensagem"Nenhuma categoria", a fórmula só retornará o erro para tais instâncias ou, alternativamente, você também poderá usar a combinação da construção IF (ISERROR ()).

    

Answer 2

Para resolver o problema, você precisa da coluna auxiliar para armazenar sequências parciais de correspondência.

Escreva esta fórmula em Cell N41 & preencha.

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

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