Get Max value from range (multiple sheets) grouped by Name

Question

I have 3 sheets that have the exact same format

Sheet1

A           B          C         D
George      10         2         8
Nick        15         89        0
Mike        13         1         50
Lucas       9         -5         12

Sheet2

A           B          C         D
Nick        1          9         5
Mike        1          10        6
George      11         22        5
Lucas       10         5         2
Panos       55         0         1

Sheet3

A            B         C         D
Panos        0         9         1
George       1         2         5
Nick         7         2         1
Lucas        1         5         1

I want to query the range {'Sheet1'!A1:D5; 'Sheet2'!A1:D5; 'Sheet3'!A1:D5}

And get something like MAX(Col2:Col4) Group By Col1

Which would return something like:

George 22
Nick   89
Mike   50
Lucas  12
Panos  55 

I tried:

=sort(query({'Sheet1'!A1:D5; 'Sheet2'!A1:D5;'Sheet3'!A1:D5}, "select Col1, MAX(Col2:Col4) Group by Col1 Label MAX(Col2:Col4) '' " ),2, FALSE) 

and

=sort(query({'Sheet1'!A1:D5; 'Sheet2'!A1:D5;'Sheet3'!A1:D5}, "select Col1, MAX(MAX(Col2),MAX(Col3), MAX(Col4)) Group by Col1 " ),2, FALSE) 

Both didn't work. Any ideas?

Answer 1

Please try:

=query(sort(transpose(query({Sheet1!A1:D5;Sheet2!A1:D5;Sheet3!A1:D5},"select max(Col2), max(Col3), max(Col4) pivot Col1"))),"select Col1, max(Col2) group by Col1 label(Col1) ''")

Answer 2

To sum up your question, It requires finding the MAX across the columns to the right as well as down. As such, QUERY does NOT have such 2D function.

So, Use a Helper column E&F in each sheet:

Max of B&C:

E2:

=ARRAYFORMULA(IF(B2:B>C2:C,B2:B,C2:C))

Max of B,C&D:

F2:

=ARRAYFORMULA(IF(D2:D>E2:E,D2:D,E2:E))    

Now, Use Query:

Query:

=ARRAYFORMULA(QUERY({Sheet1!A2:F;Sheet2!A2:F;Sheet3!A2:F}, "Select Col1,max(Col5) where Col1 is not null group by Col1 order by max(Col5) desc"))

Notes:

• Change ranges to suit
• You could also simply use MAX for each row without the ARRAYFORMULA
• Theoretically, For a single cell solution, You could enter this formula to find the max of 3 real numbers

Answer 3

Another approach perhaps a bit simpler but needing two queries

=sort(unique(({Sheet1!A1:A5;Sheet2!A1:A5;Sheet3!A1:A5})))

to get the names starting in (say) F2

Then this to get the maximum values for each name in (say) G2 and pulled down

max(query({Sheet1!A$1:D$5;Sheet2!A$1:D$5;Sheet3!A$1:D$5},"select max(Col2),max(Col3),max(Col4) where Col1='"&F2&"'"))