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)
randbetweenwas not nice (makes the spreadsheet very slow).