0

I have a document where one column is a date and another is a value for the date. I'd like to be able to get the maximum value and the date when that value appeared. For example if I had

A (Date) B (Value)
1 2023-02-18 2
2 2023-02-19 7
3 2023-02-20 3
4 2023-02-21 9
5 2023-02-22 5

I know I could use MAX to get the max value of 9 but I also want to know the date when that value appears. How can I do that?

Improve this question
3
  • Will the values in the “B” column always be unique? If they will, you can achieve your objective by using the MAX, MATCH, and INDEX functions together.
    – Alper
    Commented Feb 27, 2023 at 1:01
  • Something like index(a:a,match(max(b:b),b:b,0)) but not tested
    – Solar Mike
    Commented Feb 27, 2023 at 5:24
  • @Alper The values are not guaranteed to be unique. Now that I think of it I’m not sure what the best result would be if they aren’t.
    – Tom Harrington
    Commented Feb 27, 2023 at 15:55

1 Answer 1

Reset to default
0

One way is achieving desired result is using LOOKUP function.

"Algorithm" can be described as: "find maximum value in Values column and return corresponding row in Date column".

Assuming that data provided is in table starting from A1: maximum value (MAX(C2:C6)), range where to look for this value (C2:C6) and range where corresponding row should be returned (B2:B6):

LOOKUP(MAX(C2:C6),C2:C6,B2:B6)

Improve this answer
1
  • I went with XLOOKUP, since that lets me control what happens if the max value isn't unique.
    – Tom Harrington
    Commented Feb 27, 2023 at 18:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .