Использование ВПР или ПОИСКПОЗ для ячеек, содержащих значения, разделенные запятыми

Я пытаюсь использовать VLOOKUP или комбинацию INDEX и MATCH для небольшого проекта, над которым я работаю в Google Таблицах, но у меня возникают проблемы, когда ячейка содержит значения, разделенные запятыми. Я привел пример ниже:

Пример

Столбец A и Столбец B составляют диапазон поиска, а Столбец C содержит ключи поиска. В столбце D используется метод VLOOKUP, а в столбце E используются методы INDEX и MATCH. Формулы для ячеек в первой строке следующие:

=VLOOKUP("*"&$C1&"*", A$1:B$4, 2, FALSE)

=INDEX($B$1:$B$4, MATCH("*"&$C1&"*", $A$1:$A$4, 0))

Столбцы D и E должны указывать "УСПЕШНО" по вертикали, если все работает правильно, но, очевидно, есть некоторые проблемы. Когда я изначально начал искать помощь, я нашел метод добавления подстановочного знака (*) в начало и конец ключа поиска, чтобы игнорировать текст до или после значения, которое я ищу. Это работает, за исключением случаев, когда критерии для одного ключа поиска могут соответствовать другому ключу поиска, например, когда я ищу в строке 1 "Pg 3", но также "Pg 32" и "Pg 33". существовать. Проблема возникает только тогда, когда значение, которое я ищу, не находится в отдельной ячейке И когда ему предшествует «соответствующее» значение в более ранней строке (например, «Pg 33» соответствует ключу «Pg 3» в < strong> Строка 1 и «Стр. 64», соответствующие ключу «Стр. 6» в Строке 7).

К сожалению, в проекте, над которым я работаю, я не смогу отсортировать диапазон в алфавитно-цифровом порядке, поэтому приветствуется любая помощь в поиске обходного пути, желательно просто с использованием формул, а не вникания в Google Apps Script. Надеюсь, мое объяснение достаточно ясное, и я не знаю, использую ли я VLOOKUP или MATCH.


google-sheets google-sheets-formula array-formulas google-sheets-query gs-vlookup
person MBearnstein37    schedule 14.08.2019    source источник

Ответы (1)


arrow_upward
2
arrow_downward 

=ARRAYFORMULA(IFERROR(VLOOKUP(C1:C, 
 TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(IFERROR(SPLIT(A1:A, ","))<>"", "♦"&SPLIT(A1:A, ",")&"♠"&B1:B, ))
 ,,999^99)),,999^99), "♦")), "♠")), 2, 0)))

0

person player0    schedule 14.08.2019
comment
Спасибо вам за быстрый ответ. Я просматриваю вашу формулу, и у меня возникли проблемы с пониманием некоторых из них, особенно, какова цель символов ромба и лопаты. Не могли бы вы объяснить ваш процесс? Кроме того, приведенный мной пример использовался исключительно для объяснения моей проблемы, но когда я реализую этот метод в реальном проекте, над которым я работаю, есть ли способ сделать это без создания массива, поскольку я не хочу возвращать столько значений как есть в диапазоне, который я ищу? Если хотите, я могу представить макет того, как будет выглядеть этот проект. - person MBearnstein37; 15.08.2019
comment
конечно, это ничего не значит. ♦ ♠ - это просто уникальные символы для формулы SPLIT. это может быть любой символ ♥ ☺ ♫ ♀♂↨ ♣ ... в основном эта формула разделяет значения, разделенные запятыми, из столбца A и присваивает каждому значение из столбца B. тогда он просто выполняет простой vlookup в массиве - person player0; 15.08.2019
comment
О'кей, в этом гораздо больше смысла. Я пытаюсь сделать это самостоятельно, но если у меня возникнут слишком большие трудности, знаете ли вы, как я могу реализовать это в одной ячейке, а не в массиве? - person MBearnstein37; 19.08.2019
comment
для отдельной ячейки: =ARRAYFORMULA(IFERROR(VLOOKUP(C1, TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE( IF(IFERROR(SPLIT(A1:A, ","))<>"", "♦"&SPLIT(A1:A, ",")&"♠"&B1:B, )) ,,999^99)),,999^99), "♦")), "♠")), 2, 0))) - person player0; 19.08.2019