Forum Discussion
Display cell information based on cell with lowest date and another cell with the word Open.
Hello all,
I am sure this is an easy formula, but it is escaping me... I have a spreadsheet which is used to track open assignments. Each row displays assignment number, Date opened, date closed, and status (Open or Closed). I am looking to have a line to show the oldest assignment which is still Open based on all the Assignments within the spreadsheet. So if there are 1500 assignments, I would like to know the single oldest assignment number.
=INDEX(A2:A1500,MATCH(1,(D2:D1500=MIN(IF(G2:G1500="Open",D2:D1500)))*(G2:G1500="Open"),FALSE))
Remember, confirm with Ctrl+Shift+Enter if you're not on Microsoft 365 or Office 2021.
7 Replies
- Hello Sergei,
Thank you as well, yours worked too!
To all who reach this thread, looking for the same output. Either of the provided formulas work!
Again thank you both for the quick response.Darkdata , you are welcome
The following formula should work in all versions of Excel, but if you don't have Office 2021 or Microsoft 365, you'll have to confirm the formula with Ctrl+Shift+Enter.
I assumed that Assignment Number = column A, Date Opened = column B, Date Closed = column C and Status = column D, with data starting in row 2.
=INDEX(A2:A1500,MATCH(1,(B2:B1500=MIN(IF(D2:D1500="Open",B2:B1500)))*(D2:D1500="Open"),FALSE))
- Sorry, would help to know which Column goes with which data set.
Assignment number = column A
Date Opened = column D
Date Closed = column E
Status = column G
DD=INDEX(A2:A1500,MATCH(1,(D2:D1500=MIN(IF(G2:G1500="Open",D2:D1500)))*(G2:G1500="Open"),FALSE))
Remember, confirm with Ctrl+Shift+Enter if you're not on Microsoft 365 or Office 2021.
