Google sheets lookup with the query function within an array

0
votes

I have a following formula in my google sheets

=TEXTJOIN(" -- ",TRUE,QUERY('sheetName'!B2:F,"SELECT F WHERE B = '"&$A3&"'"))

The formula is in a different sheet, same workbook though, let's call it "sheetResult". Basically it looks-up values and returns them if there is a match. There are two things I would like to achieve with it further. I need it to be an array so that it applies to all of the rows and I need it to return only the unique values found, I have tried the following but it does not work.

=ARRAYFORMULA(IF(A2:A = "" , , TEXTJOIN(", ",TRUE,UNIQUE(QUERY('sheetName'!B2:F,"SELECT F WHERE B = '"&$A2&"'"))) )) --> not sure what syntax to use

I tried filter but filter just returns all of the info stacked up, need the formula to return the data considering the rows in which the lookup value is held.

EDIT: Added a link to shared file to better describe the question.

I want to make the formula in Y3 on the "Students" sheet apply to all of the cells below it, much like an array formula does.

Example

1
google-sheetslookup
Hi there! To better study your question, please share some examples that show how you want the formula to behave. – Jacques-Guzel Heron
Hi, edited the question and added the link to a file. Thanks – SystemWorks

1 Answers

0
votes

After further studying your situation I came with a simple fix based on your original formula. I understand that you want to apply the Y3 formula to the whole table, but without altering its behaviour. I assume that the only moving part would be the students ID (Column A). Then you only need to modify your formula to lock the fixed variables with something like:

=TEXTJOIN(" -- ",TRUE,UNIQUE(QUERY('.data'!$B$2:$F,"SELECT F WHERE B = '"&A3&"'")))

After you write that on Y3 you would need to select it and drag it down to fill the table. Please leave a comment if you need further help.