yogi_ArrayFormula ( VLookup ( Query with dynamic limit clause)))

Google Spreadsheet   Post  #2404

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Mar-23-2018

question by: Tom Stroll
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/YamDGiRzi7k;context-place=mydiscussions

ArrayFormula ( VLookup ( Query with dynamic limit clause)))

Hey there, Happy Friday! :)

****UPDATED SIMPLER SPECS at BOTTOM**** (See my final post from March 23, 2018 )

https://docs.google.com/spreadsheets/d/1G6O0AoZ_urgEodExnWeD1R6Tr_F1AU5uSUwKCS326yw/edit?usp=sharing

Desired Result in Red (ColE). Orange cell F2 is the closest I got.

=ArrayFormula(if(Len(C2:C),VLOOKUP(C2:C,{Collaborators!C2:D},2,TRUE),""))

Logic:

If a song has 3 composers and 1 publisher, the ArrayFormula would VLookup the 'Collaborators' sheet and return the 1st composer's corresponding publisher name (1 row below on the 'Collaborators' sheet) and that would be the only publisher name we see for that song. The other two would basically be filtered out by the count of publishers for that song.

HOWEVER, if a song has an equal number of composers and publishers, we would see all corresponding publisher names offset more simply by the Count of composers (grouped by that song) so that publisher names ONLY display in Rows where ColB says "Publisher" and Composer Names (Col C and D) are Blank. 

In other words the Greyed out text in ColF should not display because there are more composers than publishers for that Song b and Song e for example.

So, I'd like to replace {Collaborators!C2:D} with a Query that limits the number of Publishers per song to match the actual number of Publishers in ColB for each specific song. 

When I've tried this, I get all the same Publishers or weird offsets and errors.

ColP has all my failed ArrayFormula attempts.

NOTE: I realize Offset may be volatile with large data sets, so i'm hoping Query's limit clause can replace the need for Offset.

Maybe something like... Query(range,  "select ....  group by A  limit='"&indirect(counta())&"' "

Still, I'm using the word 'Offset' now to illustrate my point.

Thanks so much for looking into this! :)

-tom