Skip to main content
add array formula version with dmax()
Source Link
doubleunary
  • 16.8k
  • 9
  • 27
  • 63

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.

Try this:

=if( sum(B3:B), textjoin( ", ", true, filter($A3:$A, B3:B = max(B3:B)) ), iferror(1/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.

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.

adapt formulas to new sample spreadsheet
Source Link
doubleunary
  • 16.8k
  • 9
  • 27
  • 63

Try this:

=if( sum(B3:B), textjoin( ", ", true, filter($A3:$A, B3:B = max(B3:B)) ), iferror(1/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 new 'Get user with highest daily consumption''Analysis' sheet in your original sample spreadsheet.

Try this:

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

See the new 'Get user with highest daily consumption' sheet in your original sample spreadsheet.

Try this:

=if( sum(B3:B), textjoin( ", ", true, filter($A3:$A, B3:B = max(B3:B)) ), iferror(1/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.

Source Link
doubleunary
  • 16.8k
  • 9
  • 27
  • 63

Try this:

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

See the new 'Get user with highest daily consumption' sheet in your original sample spreadsheet.