Google Sheets: Get max value and corresponding values from other columns in a query function-CodePudding

I'm a bit lost with Google Sheets.

This is just a example with Google Finance to illustrate my problem!

I use a code like this

=GOOGLEFINANCE("AAPL", "all" , DATE(2022,6,21), DATE(2022,6,29))

to get this table:

My goal is to get the max value in Column 6 (Volume) with the corresponding values from Column 1(Date) & 3(High). The output has to be in the same cell as the formula (the whole table should never show up).

It should basically look like this:

I use this code to get the max value from Column 6

=QUERY(GOOGLEFINANCE("AAPL", "all" , DATE(2022,6,21), DATE(2022,6,29)),"select Max(Col6) label Max(Col6)''")

but I can't find a solution to add the corresponding values from Col1 and Col3 to the output.

CodePudding user response：

Paste this formula, no need for the table either!

I hope that helps.

CodePudding user response：

Try:

=QUERY(GOOGLEFINANCE("AAPL", "all" , DATE(2022,6,21), DATE(2022,6,29)),
"select Col1, Col3, Max(Col6) group by Col1, Col3 order by Max(Col6) desc limit 1 label Max(Col6) ''",0)