Проверьте, какое ключевое слово, из списка, с которого начинается ячейка, и верните соответствующее ключевое слово

Question

Я пытаюсь взять список продуктов и извлечь их производителя с начала названия продукта. Каждое название продукта начинается с их производителя. Это осложняется тем фактом, что у некоторых предметов есть другие производители в теле названия; Мне нужно посмотреть, с чего начинается предмет. Я имею дело с 50000+ предметов и более 3000 производителей. У меня есть формула:

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

Это работает несколько раз, но не другие.  Например, на приведенном ниже листе строки 2 и 3 являются правильными, а строка 4 - нет.  Результат в ячейке A4 для «Mike's Fun Toys» (в ячейке B4) должен быть «Mike's», но он выглядит как «Fun».

(Вот данные в текстовой форме, которые вы можете скопировать и вставить:

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

)

Но когда я меняю порядок столбца C (Производители):

строка 4 становится правильной («Майк»), но строка 2 идет не так.

Столбец А имеет формулу весь путь вниз. Ожидаемые результаты:

A2 - Brown
A3 - Cat
A4 - Mike's

Как я могу заставить формулу работать независимо от порядка столбца C?

Answer 1

Для читателей, которые не понимают формулу RyanMark, это

• Поиск позиции каждого из названий производителей в данном названии продукта.  Это приводит к
  • значение 1 для имени производителя, с которого начинается название продукта (потому что оно появляется у 1- го символа),
  • более высокие числа для названий других производителей, которые появляются в названии продукта (потому что они появляются позже, в более высоких позициях), и
  • # #VALUE! код ошибки для названий производителей, которые не указаны в названии продукта.
• Инвертирование каждого из вышеперечисленных (деление 1 на него), в результате чего
  • 1 для имени производителя, с которого начинается название продукта (именно того, которое мы хотим найти),
  • более низкие положительные числа для названий других производителей, которые появляются в названии продукта (потому что 1, разделенное на число, которое больше 1, дает соотношение, которое меньше 1), и
  • # #VALUE! код ошибки для названий производителей, которые не указаны в названии продукта.
• Использование LOOKUP чтобы найти 1 в приведенном выше.

Например, для ячейки A4 (соответствующей «Mike's Fun Toys» в ячейке B4) на первом изображении мы получаем по порядку:

• 1 , потому что «Mike's» (C2) начинает «Mike's Fun Toys»,
• 8 , потому что «Fun» (C3) появляется на 8-м персонаже «Mike's Fun Toys», и
• #VALUE! и # #VALUE! потому что «Кошка» (C4) и «Браун» (C5) не появляются в «Mike's Fun Toys».

Инвертирование, которое приводит к 1 , 0.125 (1/8), # #VALUE! и # #VALUE! ,  Затем он ищет 1 в этом массиве.  Это «должно» сработать, потому что 1 - это первый результат, а «Mike's» - это первое имя в столбце C

Проблема может быть замечена на странице помощи для LOOKUP:

Для правильной работы функции LOOKUP просматриваемые данные должны быть отсортированы в порядке возрастания.

и ясно, что 1 за которым следует 0.125 , не сортируется в порядке возрастания.

Как предполагает LOOKUP , мы можем решить это, используя MATCH . Формула, которую вы хотите, которая использует тот же базовый подход, что и ваша формула (за исключением того, что без инвертирования, которое не нужно),

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

Третий аргумент MATCH называется «match_type».  Я установил здесь 0 , что означает, что MATCH будет искать первый элемент в массиве, который точно равен 1 , и не будет предполагать, что массив отсортирован.

Это формула массива, поэтому вы должны нажать Ctrl+Shift+Enter при ее вводе.