-1

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 X35 with 20160101 and cell Y35 with 37 then I wish cells A15 and B15 to recalculate to show20160101 and 37 respectively.

How is this done if it is at all possible?

1
  • I don't think I follow you correctly... would a VLOOKUP solve your issue? – Burgi Jan 5 '16 at 16:41
1

The solution to this is as follows:

Given:

          X          Y
29    Heading 1  Heading 2
30
31    20011231      205
32    20110106      171
33    20120715      321
34    20151031      165
35
36

Then this gives the desired result:

          A                B
14    Heading 1        Heading 2
15    =MAXA(X29:X36)   =DGET(X29:Y36,"Heading 2",A14:B15)

And this also works:

          A                B
14    Heading 1        Heading 2
15    =MAXA(X29:X36)   =DGET(X29:Y36,Y29,A14:B15)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.