Showing if Open but also show the ticket number-CodePudding

I am working on an excel sheet that is a ticket tracker. I currently have a formula that will tell me which is the oldest ticket # by two fields, first if the ticket is open or waiting(Column B) and then uses another column (Column D) to look at the completed date. This is currently working using

=MIN(IF((Main!B:B="Open") (Main!B:B="Awaiting"),Main!D:D))

but would like to have it show the actual oldest date(which it shows now) along with the ticket number which would be column A

In Yellow shows the oldest date but also want to display the ticket number beside it

CodePudding user response：

Improved answer to account for multiple entries for the same date: =INDEX(A:B,MATCH(1,(MIN(IF((B:B="Open") (B:B="Awaiting"),D:D))=D:D)*((B:B="Open") (B:B="Awaiting")),0),)

Old answer: If you have Office 365 you could use =FILTER(A:B,D:D=MIN(IF((B:B="Open") (B:B="Awaiting"),D:D)))

Else you could use: =INDEX($A:$B,MATCH(MIN(IF(($B:$B="Open") ($B:$B="Awaiting"),$D:$D)),$D:$D,0),COLUMN(A:A)) Enter with ctrl shift enter and drag to the right one cell.

Both of these only work for unique date values in column D (thanks to Scott Craner pointing this out)