Edited with additional context:
Embedded in a LibreOffice-5 Calc spreadsheet I have a two column array where one column contains dates and the other column values. I wish to select the row based on the MAX date found in a range and return that value in one cell elsewhere on the spreadsheet together with the adjacent cell's value in a separate cell elsewhere on the spreadsheet.
The way I conceive this is that the MAX(X30:X40) returns the most recent date. I also want to derive from this result the address of the adjacent cell (Y?) and return that value as well. For example given:
X Y 30 31 20011231 205 32 20110106 171 33 20120715 321 34 20151031 165 35 36
and elsewhere on the same sheet this:
A B 15
I wish cell
A15 initially to display
20151031 (X34) and
B15 to display
165 (Y34). If later someone updates the empty cell
20160101 and cell
37 then I wish cells
B15 to recalculate to show
How is this done if it is at all possible?