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

user_19559121 0
Viewed 87

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:

enter image description here

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:

enter image description here

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.

google-sheets filter datatable
2 Answers

Option 01

Paste this formula to get the "desired output", no need for the table either! Link to the Sheet.

=SORTN(QUERY(GOOGLEFINANCE("AAPL", "all" , DATE(2022,6,21), DATE(2022,6,29))," Select Col1,Col3,Col6 ",0),1,,2,0)

Explanation

1 - QUERY the input in this case GOOGLEFINANCE("AAPL", "all" , DATE(2022,6,21), DATE(2022,6,29)) and set "query" Col1,Col3 and Col6 Date, High and Volume, wiht [headers] set to 0

2 - SORTN the result and set The number of items to return [n] 1 to get the top 1 result including the headers in this case QUERY fuction [headers] is set to 0 so SORTN returns only one row , and set [sort_column] to 6 "Volume", and [is_ascending] to 0.

Option 02

To output the result with headers.

=SORTN(QUERY(GOOGLEFINANCE("AAPL", "all" , DATE(2022,6,21), DATE(2022,6,29))," Select Col1,Col3,Col6 "),2,,2,0)

Explanation

1 - QUERY the input in this case GOOGLEFINANCE("AAPL", "all" , DATE(2022,6,21), DATE(2022,6,29)) and set "query" Col1,Col3 and Col6 Date, High and Volume.

2 - SORTN the result and set The number of items to return [n] 2 to get the top 1 result including the headers, and set [sort_column] to 6 "Volume", and [is_ascending] to 0.


ُExample

I hope that helps.

user_195296940

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)
user_178873010
Related