get value left of max value

Question

I have a sheet that looks like:

Date         Value      Highest     Lowest
----------------------------------------------------------------
1-1-2020     23         26           20 
2-1-2020     20
3-1-2020     26
4-1-2020     22
5-1-2020     24

Where highest is the highest value in the value row =MAX(B2:B999) and lowest is the lowest value in the value row =MIN(B2:B999) What I want is that below the highest value (26) I have the date of that value in this case 3 January. Also for the lowest I want to get the lowest date 2 January.

Answer 1

Please Place under Highest

=ARRAY_CONSTRAIN(SORT(A2:B7,2,0),1,1)

and under Lowest

=ARRAY_CONSTRAIN(SORT(A2:B7,2,1),1,1)

Adjust ranges accordingly.

Functions used:

• ARRAY_CONSTRAIN
• SORT