This is what I have so far, I am getting stuck where I would usually do the IN syntax also AND with SQL WHERE

See the images for explanation of the following: An example data for A in sheet1:

cell1 = [A,B,C]
cell2 = [A,B]
Cell3 = [A]

The sheet2 "jobs" has in column A just one letter out of the potential lists in cell1,2,3..., delimited by comma.

The format allowed the most condensed view of the jobs on the first sheet, then the jobs sheet expands them for specific when and where per project.

I know that is isn't conventional to have multivalued fields, but it was necessary to summarize the data view rather than have a flooded screen of replicates to manage missing elements in this school project.

Overall Groups Sheet

Jobs Sheet <-- the bring column is where the query will be used and say to return the answer there

An example problem: I want to find WHERE TEAM A IN [A,B,C] exists in one instance and the related project from B in the query to explain what to bring to class. The selection returns one block of text content from C. And the condition that project matches the with the team letter.

=QUERY({OverallGroups'!A:C; Jobs'!A}, "Select C WHERE A matches '" & OverallGroups'!A:A & '" AND A matches " & Jobs'!A & "'")

I tried using &TEXTJOIN() but this works for one to one cells, and won't parse the inner list by the comma.

CodePudding user response：

The answer is from Martín on this post:

"Something like: =query('OverallGroups'!A:C;"Select C where A contains '"&A2&"' and B contains '"&B2&"'") should do the job for one row, Let me know"

CodePudding user response：

I'm glad it was useful! Here you have a multirow option:

=byrow(A2:A;lambda(each;if(each="";"";query('OverallGroups'!A:C;"Select C where A contains '"&each&"' and B contains '"&offset(each;0;1)&"'"))))