Как вернуть несколько результатов в одной и той же ячейке с помощью индекса, совпадения и поиска

У меня есть рабочая формула Index, Match, Search, которая ищет в заголовках моих продуктов определенные термины, которые я определил в Ключе, если результат не найден, то в качестве альтернативы он ищет SKU моего продукта, используя другой ключ.

В настоящее время формула дает мне только первый результат, который она находит в моем ключе, но я хотел бы, чтобы она возвращала как можно больше результатов, разделенных каналом или запятыми.

Вот рабочая формула, которая дает мне 1 результат.

=IF((ARRAYFORMULA(INDEX('Array Key'!$J:$J,MATCH(TRUE,ISNUMBER(SEARCH('Array Key'!$I:$I,$B2)),0))))="",(ARRAYFORMULA(INDEX('Array Key'!$L:$L,MATCH(TRUE,ISNUMBER(SEARCH('Array Key'!$K:$K,$A2)),0)))),(ARRAYFORMULA(INDEX('Array Key'!$J:$J,MATCH(TRUE,ISNUMBER(SEARCH('Array Key'!$I:$I,$B2)),0)))))

Было бы здорово, если бы я мог заставить его работать. Вот ссылка на копию моей таблицы, если кто-то хочет увидеть, как она работает. Не стесняйтесь вносить любые правки.

https://docs.google.com/spreadsheets/d/1APLCVnqmP51UbimyZeY-S0BoSEuFWkPa2LtAxVX9H0E/edit?usp=sharing

Я пробовал использовать следующую формулу, но она не работает так, как мне хотелось бы.

=IF((ARRAYFORMULA(INDEX('Array Key'!$F:$F,MATCH(TRUE,ISNUMBER(SEARCH('Array Key'!$E:$E,$B2)),0))))="",(ARRAYFORMULA(INDEX('Array Key'!$H:$H,MATCH(TRUE,ISNUMBER(SEARCH('Array Key'!$G:$G,$A2)),0)))),(ARRAYFORMULA(INDEX('Array Key'!$F:$F,MATCH(TRUE,ISNUMBER(SEARCH('Array Key'!$E:$E,$B2)),0)))))&" | "&(ARRAYFORMULA(INDEX('Array Key'!$H:$H,MATCH(TRUE,ISNUMBER(SEARCH('Array Key'!$G:$G,$A2)),0))))

Эта формула снова ищет второй ключ и объединяет все результаты. Однако я получаю дубликаты, и он по-прежнему возвращает только первый найденный результат.


google-sheets google-sheets-formula array-formulas textjoin gs-vlookup
person Maladorf    schedule 10.07.2019    source источник
comment
Вам стоит заглянуть в TEXTJOIN() функцию :)   -  person JvdV    schedule 10.07.2019
comment
Я немного посмотрел, я просто не знаю, как применить это к моей формуле :(   -  person Maladorf    schedule 10.07.2019
comment
Есть ли причина, по которой вы вообще отметили Excel, потому что прямо сейчас другой пользователь удалил это из вопроса. Если это не имеет отношения к делу, вы должны пропустить его.   -  person JvdV    schedule 10.07.2019
comment
каким должен быть желаемый результат ячейки D2 ??   -  person player0    schedule 10.07.2019
comment
В настоящее время я работаю в таблицах Google, но если в Excel есть функция, при которой в таблицах Google не требуется, чтобы это работало, я также хотел бы знать :) Извините, если это неправильный способ пометить сообщение.   -  person Maladorf    schedule 10.07.2019
comment
Желаемый результат для ячейки D2 будет War | Ужас, как на вкладке Array Key в ячейках E25 и E41, смерть имеет 2 категории, которые я хотел бы назначить в ячейке D2   -  person Maladorf    schedule 10.07.2019
comment
Это нормально, но из вопроса не совсем ясно. Вы можете изменить свой вопрос, если хотите, и повторно пометить Excel. Но скорее всего все это можно сделать через GS   -  person JvdV    schedule 10.07.2019
comment
нет никакого смысла в том, чтобы D2 был таким, каким вы хотите его видеть ...   -  person player0    schedule 10.07.2019

Ответы (1)


arrow_upward
0
arrow_downward

если бы это было так:

введите здесь описание изображения

тогда ячейка D2 будет:

=ARRAYFORMULA(TEXTJOIN(" | ", 1, UNIQUE({
 IFERROR(VLOOKUP(TRANSPOSE(IFERROR(REGEXEXTRACT(SPLIT(B2, " "), 
 TEXTJOIN("|", 1, 'Array Key'!E$3:E)))), 'Array Key'!E$3:F, 2, 0));
 IFERROR(VLOOKUP(TRANSPOSE(IFERROR(REGEXEXTRACT(SPLIT(A2, " "), 
 TEXTJOIN("|", 1, 'Array Key'!G$3:G)))), 'Array Key'!G$3:H, 2, 0))})))

0

person player0    schedule 10.07.2019
comment
Это блестяще. Это работает именно так, как я хотел. Спасибо большое за вашу помощь! - person Maladorf; 11.07.2019
comment
Привет, я только что снова взялся за этот проект после небольшого перерыва, и мне было интересно, не могли бы вы помочь? Вот ссылка на лист, над которым я работаю, Я не могу понять, почему я получаю какие-то результаты. Например, продуктам из «Звездных войн», которые я выделил темно-серым цветом, не назначается тема, даже несмотря на то, что в названии присутствует слово «Звездные войны» (как вы можете видеть на вкладке «Ключ массива» в строке 103, там существуют «Звездные войны»). - person Maladorf; 08.08.2019
comment
проверь свой лист сейчас - person player0; 08.08.2019