vlookup with multiple output and combining filter() with arrayformula()

Question

I have 2 sheets : shot and deadline.

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

This is my shot sheet :

and this is the deadline sheet :

So in the deadline sheet, it's based on the dates. It will pull the shotid from shot sheet.

in A2 of deadline sheet , i use :

=ifna( filter(shot!$A$2:$A, shot!$B$2:$B=A$1) ,)

Then i copy the formula to B2:2. The image2 above is the ideal output i'm looking for. The formula works fine, but i want to make a single arrayformula().

My first trial to use arrayformula() with the previous filter() :

=arrayformula( ifna( filter(shot!$A$2:$A, shot!$B$2:$B=A$1:$1) ,))

and it returns error. Filter() doesn't work together with arrayformula. So i second trial is using vlookup :

=arrayformula( ifna( vlookup( A$1:$1,{ shot!$B$2:$B, shot!$A$2:$A },2,0) ,))

it will give me only 1 output per column. The vlookup only find the first match :

Can anyone help me ? is it possible to use arrayformula() for this case ?

Answer 1

Use this query-pivot-countifs pattern to get a list of dates in column B and the shots in column A associated with each date:

=arrayformula( 
  transpose(
    query( 
      query( 
        { 
          A1:B, 
          countifs(B1:B, B1:B, row(B1:B), "<=" & row(B1:B)) 
        }, 
        "select Col2, max(Col1) 
         where Col2 is not null 
         group by Col2 
         pivot Col3", 
        1 
      ), 
      "offset 1", 
      0 
    ) 
  ) 
)

Note that the formula will only list dates that actually appear in the data. To get a sparse result, match the output of the formula against your list of dates with an arrayformula(iferror(vlookup(row()))) pattern, like this:

=arrayformula( 
  iferror( 
    vlookup( 
      A1:1, 
      query( 
        query( 
          { 
            data!A1:B, 
            countifs(data!B1:B, data!B1:B, row(data!B1:B), "<=" & row(data!B1:B)) 
          }, 
          "select Col2, max(Col1) 
           where Col2 is not null 
           group by Col2 
           pivot Col3", 
          1 
        ), 
        "offset 1", 
        0 
      ), 
      row(data!A2:A), 
      false 
    ) 
  ) 
)