# 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:

for query option:

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)

• Most of March columns are empty, the highest consumption formula doesn't match the formula in the sample sheet. Apr 6, 2021 at 20:50
• I populated the rest of march's columns and fixed the formula Apr 7, 2021 at 5:18
• Filling the spreadsheet with so many `randbetween` was not nice (makes the spreadsheet very slow). Apr 8, 2021 at 3:02
• You seem hard to impress ruben Apr 8, 2021 at 4:51

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.

• your answer doesn't work b/c I need a single line formula, yours has a formula for each column which isn't scalable: `=if( sum(C3:C), textjoin( ", ", true, filter(\$A3:\$A, C3:C = max(C3:C)) ), iferror(1/0) )` and `=if( sum(D3:D), textjoin( ", ", true, filter(\$A3:\$A, D3:D = max(D3:D)) ), iferror(1/0) )` etc Apr 5, 2021 at 13:14