CODEWITHSUNDEEP
arraysgoogle-sheetsmatcharray-formulasgoogle-sheets-query
johny
johny

Reputation: 159

Search for a value in multiple columns and return all results that are in the right column of the found column

Search the value "thisisit" in column A and then search in this line for all "de" columns and return me what is in the columns to the right of it. If multiple results are returned, return the results in one cell but separate these results with a line break.

A1          | B1        | C1          | D1          | E1        | F1        | G1
thisisit    | de        | Bicycle     | en          | Car       | de        | Boot

A3
Bicycle (line break)
Boot

Upvotes: 1

Views: 115

Answers (1)

player0
player0

Reputation: 1

try:

=TEXTJOIN(CHAR(10), 1, QUERY({TRANSPOSE(FILTER(
 INDIRECT(MATCH("thisisit", A1:A)&":"&MATCH("thisisit", A1:A)), 
 MOD(COLUMN(1:1), 2)=0)), {QUERY(TRANSPOSE(FILTER(
 INDIRECT(MATCH("thisisit", A1:A)&":"&MATCH("thisisit", A1:A)), 
 MOD(COLUMN(1:1)-1, 2)=0)), "offset 1", 0); ""}},
 "select Col2 where Col1 = 'de'", 0))

0

Upvotes: 1

Related Questions