Искать в 2 столбцах и возвращать все совпадения в транспонированной строке

Пытаюсь взять это QUERY:

=IFERROR(TRANSPOSE(QUERY(Items!$A$2:$D,"SELECT D WHERE A = '"&A2&"' and B = '"&B2&"' and C = '"&C2&"' and C is not NULL and D is not NULL", 0)),)

И превратить его в формулу, которую я могу ввести один раз вверху листа и применить ко всем ячейкам в столбце ниже.

Я пробовал манипулировать различными другими формулами и ответами, которые я нашел / получил здесь, на SO, но не получил ничего, кроме ошибок.

Моя таблица


google-sheets google-sheets-formula array-formulas google-sheets-query gs-vlookup
person TC76    schedule 07.07.2019    source источник
comment
Есть ли конкретное название листа, или мы обсуждаем это в целом?   -  person Tedinoz    schedule 07.07.2019
comment
В конечном итоге я буду обращаться к нескольким листам, но конкретно к 'subItemDataValidations! D2'   -  person TC76    schedule 07.07.2019

Ответы (2)


arrow_upward
1
arrow_downward 

=ARRAYFORMULA(IFERROR(VLOOKUP(Estimate!A2:A&Estimate!B2:B, 
 TRIM(IFERROR(SPLIT(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 {INDEX(QUERY(IFERROR(SPLIT(SORT(UNIQUE(IF((LEN(Items!A2:A&Items!B2:B))*(LEN(Items!C2:C)),
 Items!A2:A&Items!B2:B&"♦"&Items!C2:C, )), 1, 1), "♦")), 
 "select Col1,count(Col1) where Col1 is not null group by Col1 pivot Col2", 0),,1), IF(
 ISNUMBER(QUERY(IFERROR(SPLIT(SORT(UNIQUE(IF((LEN(Items!A2:A&Items!B2:B))*(LEN(Items!C2:C)),
 Items!A2:A&Items!B2:B&"♦"&Items!C2:C, )), 1, 1), "♦")), 
 "select count(Col1) where Col1 is not null group by Col1 pivot Col2", 0)),
 QUERY(IFERROR(SPLIT(SORT(UNIQUE(IF((LEN(Items!A2:A&Items!B2:B))*(LEN(Items!C2:C)), 
 Items!A2:A&Items!B2:B&"♦♥"&Items!C2:C, )), 1, 1), "♦")),
 "select count(Col1) where Col1 is not null group by Col1 pivot Col2 limit 0", 0), )})
 ,,999^99))), "♥"))), {2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}, 0)))

0

person player0    schedule 07.07.2019
comment
Я попытался изменить эту формулу для поиска по нескольким столбцам, но безуспешно. В настоящее время он принимает только значение первого совпадения (на основе одного столбца), но я бы хотел, чтобы оно совпадало на основе трех столбцов (Task Group, Task, Action). - person TC76; 18.07.2019
comment
Я считаю, что это выходит за рамки ограничений ВПР. - person player0; 18.07.2019

arrow_upward
0
arrow_downward

Спасибо за эту огромную формулу! Подумать только, я пытался добиться этого с помощью простого FILTER. Будет ли это подходящей модификацией для включения другого столбца (столбец C) в критерии поиска?

=ARRAYFORMULA(IFERROR(VLOOKUP('task-itemAssociationsDV'!A2:A&'task-itemAssociationsDV'!B2:B&'task-itemAssociationsDV'!C2:C, 
 TRIM(IFERROR(SPLIT(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 {INDEX(QUERY(IFERROR(SPLIT(SORT(UNIQUE(IF((LEN(taskData!A2:A&taskData!B2:B&taskData!C2:C))*(LEN(taskData!D2:D)), 
 taskData!A2:A&taskData!B2:B&taskData!C2:C&"♦"&taskData!D2:D, )), 1, 1), "♦")), 
 "select Col1,count(Col1) where Col1 is not null group by Col1 pivot Col2", 0),,1), IF(
 ISNUMBER(QUERY(IFERROR(SPLIT(SORT(UNIQUE(IF((LEN(taskData!A2:A&taskData!B2:B&taskData!C2:C))*(LEN(taskData!D2:D)), 
 taskData!A2:A&taskData!B2:B&taskData!C2:C&"♦"&taskData!D2:D, )), 1, 1), "♦")), 
 "select count(Col1) where Col1 is not null group by Col1 pivot Col2", 0)),
 QUERY(IFERROR(SPLIT(SORT(UNIQUE(IF((LEN(taskData!A2:A&taskData!B2:B&taskData!C2:C))*(LEN(taskData!D2:D)), 
 taskData!A2:A&taskData!B2:B&taskData!C2:C&"♦♥"&taskData!D2:D, )), 1, 1), "♦")),
 "select count(Col1) where Col1 is not null group by Col1 pivot Col2 limit 0", 0), )})
 ,,999^99))), "♥"))), {2,3,4,5,6,7,8,9,10,11,12,13,14,15,16}, 0)))
person TC76    schedule 07.07.2019