This is a classic example of anarray formula.
Excel Table
For convenience, all data is in anExcel Tablenameddatain the range B5:D16.
If you are new to Excel Tables,this article provides an overview.
Also see this short video:
Array formula
This is a classic array formula problem.
Subtracting the lows from the highs is anarray operationthat requires special handling in older versions of Excel.
In Legacy Excel, you must enter the formula with control + shift + enter.
This is done to confirm the formula works properly.
If you re-enter the formulawithoutcontrol + shift + enter, you will see an incorrect result.
The MAX function will then return the maximum absolute value change.
Date of max change
You may also want to know the date of the maximum change.
MATCH returns 9 to INDEX asrow_num, and INDEX returns the 9-Jun as a final result.
The result is assigned todata.
MAX ignores empty cells, the logical values TRUE and FALSE, and text values.
you’re free to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
XLOOKUP supports approximate and exact matching, wildcards (* ?)
Values can be sorted by one or more columns.
SORT returns a dynamic array of results.
VSTACK Function
The Excel VSTACK function combines arrays vertically into a single array.
Basic SORT function example
