Forum Discussion

Copper Contributor

Jan 31, 2022

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...

Formulas and Functions

HansVogelaar
Jan 31, 2022
Darkdata

=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.

HansVogelaar

MVP

Jan 31, 2022

Darkdata

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))

Darkdata

Copper Contributor

Jan 31, 2022

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

HansVogelaar
MVP
Jan 31, 2022
Darkdata

=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.
- Darkdata
  Copper Contributor
  Jan 31, 2022
  Thank you Hans, that was it, worked perfect.