How to run a google sheets query against a dynamic range

Question

Background

I have a spreadsheet that displays data internet consumption by users that looks like this

userid    feb1    feb2    feb3     .    feb29 
u1        100     34      23       .     4
u2        0       24      21             62
u3        300     25      5              1
u4        50      5       6      
.
.
un        23      52      3        .     42

I already wrote a formula that lists the daily consumption of each user like so:

date        daily consumption
feb1        14,971     
feb2        6,898       
feb3        10,666     
.
.

feb29        10,543     

from the daily consumption I wrote a formula that gives me the highest amount consumed by a user on a daily basis so it looks like something like this

date        daily consumption    highest consumption
feb1        14,971               7,523
feb2        6,898                3,422
feb3        10,666               5,411
.
.

feb29        10,543              6,234

(this is what the query is where March is the sheet that contains the raw data

query(transpose(QUERY(March!B3:C,"Select "&textjoin(",",true,ARRAYFORMULA("Max("& REGEXEXTRACT(address(1,column(March!B2:C),4,true,"March"),"March!(.*)\d") &")")) &"",0)),"select Col2")

Problem

What I'm trying to do now is find out on a daily basis the ID of the highest consumer like so:

date        daily consumption    highest consumption    highest consumer id
feb1        14,971               7,523                  4779221
feb2        6,898                3,422                  1047223  
feb3        10,666               5,411                  7505020
.
.

feb29        10,543              6,234                  2994922

I have two ways of getting the highest consumer Id manually:

VLOOKUP method

ie

=vlookup(D2  ,{March!$I:$I,March!$B:$B},2,false)
=vlookup(D3  ,{March!$J:$J,March!$B:$B},2,false)
=vlookup(D4  ,{March!$K:$K,March!$B:$B},2,false)
..

query method

ie

=query({March!$I:$I,March!$B:$B},"select Col2 where Col1 contains "&trunc(D2,5))
=query({March!$J:$J,March!$B:$B},"select Col2 where Col1 contains "&trunc(D3,5))
=query({March!$K:$K,March!$B:$B},"select Col2 where Col1 contains "&trunc(D4,5))

However I'm struggling with writing a formula that makes either work in a single line. What I'm trying to do is something along these lines:

for vlookup option:

arrayformula(vlookup(D2:D3,indirect("March!$"& REGEXEXTRACT(address(1,column(March!$I:$J),4,true,"March"),"March!(.)\d") &":$" & REGEXEXTRACT(address(1,column(March!$I:$J),4,true,"March"),"March!(.)\d")), indirect("March!$B:$B"),2,false))

for query option:

arrayformula(query({indirect("March!$"& REGEXEXTRACT(address(1,column(March!$I:$J),4,true,"March"),"March!(.)\d") &":$" & REGEXEXTRACT(address(1,column(March!$I:$J),4,true,"March"),"March!(.)\d")), indirect("March!$B:$B")},"select *"))

How do I make this work?

why I need arrayformula rather than copy/pasting formulas across cells

the same sheet is very minimalist compared to the actual data I get from vendors. And this question is a about a single data analysis point I'm interested in - there are many more. Dragging formulas around will soon be cumbersome and not realistic. That's why I want to create arrayformulas instead.

Sample sheet

note: the formulas will be a bit different on this sample sheet (same structure, different cell references)

https://docs.google.com/spreadsheets/d/1OmVczb6KS8E6biDrtn8xSx1u1-urFjaFhY8lOVJdtGk/edit#gid=510221509

Answer 1

Try this:

=if( 
  sum(B3:B), 
  textjoin( ", ", true, filter($A3:$A, B3:B = max(B3:B)) ), 
  iferror(1/0) 
)

To do the same with an array formula, try this:

=arrayformula( 
  transpose( 
    iferror( 1 / ( 1 / 
      dmax( 
        B3:Z, 
        sequence(columns(B3:Z)), 
        transpose(iferror(column(B3:Z) / 0)) 
      ) 
    ) ) 
  ) 
)

To place these values in the 'Analysis' sheet, use this:

=arrayformula( iferror( hlookup( A2:A, { March!B2:2; March!B1:1 }, 2, false) ) )

Note that I had to replace the formula in A2 with a much simpler transpose(). The original formula is in B2.

See the 'Analysis' sheet in your sample spreadsheet.