INDEX-MATCH для частичного совпадения ссылочной строки с несколькими значениями поиска

Question

У меня есть две ниже (сокращенные) таблицы:

A1:A5 - Таблица данных

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 - Справочная таблица

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

Я пытаюсь создать формулу, которая будет смотреть на таблицу URL-адресов, проверять, есть ли частичное совпадение строки в столбце справочной таблицы "Если папка содержит", и возвращать значение. Например, если я выберу первую ссылку (A2), я ожидаю, что формула вернет "Мобильные аксессуары", так как A2 содержит "мобильные аксессуары" в URL.

Наоборот, я знаю, как это сделать (просто сделайте vlookup с подстановочным знаком до / после), но я не могу найти способ сделать это. Любое руководство будет оценено

Answer 1

Просто интересно, должны ли приборы / холодильники подключаться к Semiconductor, как вы показали в своем примере. Я, хотя, может быть, бизнес / полупроводник может сопоставить с Semiconductor. Но это не проблема.

В этом примере примерные данные URL находятся в A1:A5, таблица сопоставления в E1:F5. Теперь в B2 поместите следующую формулу и нажмите CTRL + SHIFT + ENTER на панели формул, чтобы создать формулу массива. Формула теперь должна быть заключена в фигурные скобки, чтобы указать, что это формула массива.

Перетащите его вниз до предполагаемых рядов.

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

Outer IFERROR добавляется только для косметических целей, просто для отображения текста "Нет категории", если совпадений не найдено. Если ваша версия EXCEL до 2007 года, вы можете ее удалить. Вместо сообщения "Нет категории" формула должна просто возвращать ошибку для таких экземпляров, или в качестве альтернативы вы также можете использовать сочетание конструкции IF(ISERROR()).

Answer 2

Чтобы решить эту проблему, вам понадобится вспомогательный столбец для хранения строк частичного совпадения.

Запишите эту формулу в ячейку N41 и заполните.

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

При необходимости измените ссылки на ячейки в формуле.