Forum Discussion
Solved
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 clo...
=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.
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
Assignment number = column A
Date Opened = column D
Date Closed = column E
Status = column G
DD