Vlookup через несколько столбцов

Question

В одной книге у меня есть две таблицы.
Лист А ("Мастер") выглядит следующим образом - он показывает основную ветвь с ее подветвлениями.

Лист B ("Запрос") выглядит следующим образом:

Проблема в том, что многие ветви в столбце C в "Query" являются подветвлениями, которые находятся только в Col D, E, F или G "Master". В колонке "Запрос" я хочу отобразить основную ветвь моей подветви в колонке С.

Я пробовал что-то вроде этого, чтобы проверить 2 столбца, но он возвращает мне имя основной ветви и, если это подветвление, только число:

=IFERROR(VLOOKUP(B:B,Master!A:G,3,FALSE), VLOOKUP(B:B,Master!D:D,1,FALSE))

Любая помощь приветствуется!

Answer 1

Вот решение формулы массива. Выберите A2 на листе запросов и вставьте следующее в строку формул.

=INDEX(Master!$C$1:$C$9,MAX(IF(ISERROR(FIND("|"&B2&"|","|"&Master!$A$1:$A$9&"|"&Master!$D$1:$D$9&"|"&Master!$E$1:$E$9&"|"&Master!$F$1:$F$9&"|"&Master!$G$1:$G$9&"|")),-1,1)*ROW(Master!$A$1:$A$9)))

Нажмите Ctrl+Shift+Enter, чтобы ввести в качестве формулы массива. Затем заполните колонку.

Объяснение:
Эта формула создает массив объединенных кодов ветвей и подветвлений для каждого хранилища на мастер-листе. Коды разделены символом, которого нет ни в одном из кодов (|). Например, первая запись в массиве будет

|42981|0|0|21743|0|

Затем формула ищет код из столбца B листа запросов, завернутый в символы разделителя (например, |26183|). Для любого совпадения номер строки совпадения сохраняется в массиве. Для не совпадений -1 сохраняется.

Значение MAX этого массива номеров строк и -1 берется и передается в функцию INDEX , которая ищет этот конкретный индекс в столбце C на мастер-листе. Если -1 передается в функцию INDEX , т. Е. Если соответствующий код не найден, возвращается ошибка.

Answer 2

Вот решение, которое легко расширяется для дополнительных подколонок:

=OFFSET(INDEX(MainList,IF(SUMPRODUCT(--(SubTable=a2))<>1,NA(),SUMPRODUCT(ROW(SubTable)*(SubTable=a2))-ROW(SubTable)+1)),0,2)

MainList: Именованный диапазон идентификаторов главной ветви (A2: Axxx)

SubTable: Именованный диапазон вложенных записей (d2: gxxx)