Google Sheet: How to find Highest Value along with the date for given set of data(date-value pair) in Google Sheet Formula?

user_6534956 0
Viewed 331

I have below data in date-value pair format in Google Sheet:

Date Value
1/8/2021 1301.85
1/11/2021 1303.9
1/12/2021 1320.05
1/13/2021 1291.55
1/14/2021 1287.45
1/15/2021 1270

I'm looking for a google sheet formula that will return output: highest value along with its associated date. That means I'm trying to get the output as below: 1/12/2021 1320.05

google-sheets-formula google-sheets
3 Answers

try this:

=INDEX(SORT(A2:B), 2, 0), 1)
user_56326290

You can also use the SORTN function.
The difference being, by using an INDEX formula you need to re-format the column A result to a date while using SORTN you don't.

=SORTN(A2:B,1,0,2,0)

enter image description here

How the SORTN function in our formula works

  • 1 is "the number of items to return" (since we need just the max we set it to 1)
  • 0 "Shows at most the first n rows" (this is the so called ties_mode, in our case 0)
  • 2 is "the index of the column containing the values to sort by" which here is the 2nd column
  • 0 "indicates how to sort the sort_column. TRUE (or 1) sorts in ascending order. FALSE (or 0) sorts in descending order".
user_15277800

Another approach:

=index(A2:B,match(max(B2:B),B2:B,0))

enter image description here

Like this answer, you don't need to reformat the date.

user_112252910
Related