Extract data based on 2 criteria (using Index & Match)

[emirmansouri]

I would like to extract data that is equal to High in the "Priority" and does not have a value in "Date Closed".
Basically need to list the High priority jobs which have not been closed, so in the Priority column it must be High and in the Date Closed column it must be a blank.
If these conditions are met then extract the data from the Issue (Column C) and list it in Column L. Then find the next high priority item that has no date closed.

So first one that meets these conditions is "Budget has not been initiated yet" in C2.
But as I have built my code I have not found away to extract data using blank cells as a criteria, hence, my last result shows No8, but this is wrong as it has a date because it was closed.

My criteria will not change, its always the same.

1st Criteria = High
2nd Criteria = Blank Cell (as in there is no closed date)

Here is my code so far and the table below

=IF(ISERROR(INDEX($B$2:$G$61,SMALL(IF($D$2:$D$61=$I$2,ROW($D$2:$D$61)),ROW(1:1))-1,2)),"",INDEX($B$2:$G$62,SMALL(IF($D$2:$D$61=$I$2,ROW($D$2:$D$61)),ROW(1:1))-1,2))

[TABLE="width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 1376"]
<tbody>[TR]
[TD][/TD]
[TD]#
[/TD]
[TD]Issue
[/TD]
[TD]Priority
[/TD]
[TD]Date Opened
[/TD]
[TD]Date Closed
[/TD]
[TD]Comments
[/TD]
[TD][/TD]
[TD="colspan: 2"]Open Issue Priorities
[/TD]
[TD][/TD]
[TD]Top 5 Issues
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1
[/TD]
[TD]Budget has not been initiated yet.
[/TD]
[TD]High
[/TD]
[TD]6-May-15
[/TD]
[TD][/TD]
[TD]To be expedited
[/TD]
[TD][/TD]
[TD]High
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2
[/TD]
[TD]TAPL still not selected
[/TD]
[TD]Low
[/TD]
[TD]6-May-15
[/TD]
[TD]10-May-15
[/TD]
[TD]SDM to take action on an urgent basis
[/TD]
[TD][/TD]
[TD]Medium
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Issue
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3
[/TD]
[TD]Strategy Document Delayed
[/TD]
[TD]Medium
[/TD]
[TD]2-Feb-15
[/TD]
[TD]10-May-15
[/TD]
[TD]Data missing
[/TD]
[TD][/TD]
[TD]Low
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Budget has not been initiated yet.
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4
[/TD]
[TD]No4
[/TD]
[TD]High
[/TD]
[TD]6-May-15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]No4
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5
[/TD]
[TD]No5
[/TD]
[TD]High
[/TD]
[TD]6-May-15
[/TD]
[TD]10-May-15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]No5
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6
[/TD]
[TD]No6
[/TD]
[TD]Medium
[/TD]
[TD]6-May-15
[/TD]
[TD]10-May-15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]No7
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7
[/TD]
[TD]No7
[/TD]
[TD]High
[/TD]
[TD]6-May-15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]No8
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8
[/TD]
[TD]No8
[/TD]
[TD]High
[/TD]
[TD]6-May-15
[/TD]
[TD]10-May-15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9
[/TD]
[TD]No9
[/TD]
[TD]Low
[/TD]
[TD]6-May-15
[/TD]
[TD]10-May-15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10
[/TD]
[TD]No10
[/TD]
[TD]High
[/TD]
[TD]6-May-15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Please help as I have been searching for 2 days and so far I have not seen this done anywhere.

Thanks in advance.

 

[gaz_chops]

Try

=IFERROR(INDEX($B$2:$G$61,SMALL(IF($F$2:$F$62<>"",IF($D$2:$D$61=$I$2,ROW($D$2:$D$61))),ROW(1:1))-1,COLUMN(A2)),"")

 

[jasonb75]

It looks like the first formula goes in L4 in your sheet, based on that, try

=IFERROR(INDEX($C:$C,SMALL(IF($D$2:$D$61=$I$2,IF($F$2:$F$61="",ROW($F$2:$F$61))),ROWS($L$4:$L4))),"")

 

[gaz_chops]

Think it should be <>""

It looks like the first formula goes in L4 in your sheet, based on that, try

=IFERROR(INDEX($C:$C,SMALL(IF($D$2:$D$61=$I$2,IF($F$2:$F$61="",ROW($F$2:$F$61))),ROWS($L$4:$L4))),"")

 

[gaz_chops]

Think it should be <>""

Oops my bad! You're correct should be =""

 

[jasonb75]

Think it should be <>""

I Don't

But as I have built my code I have not found away to extract data using blank cells as a criteria, hence, my last result shows No8, but this is wrong as it has a date because it was closed.

My criteria will not change, its always the same.

1st Criteria = High
2nd Criteria = Blank Cell (as in there is no closed date)

 

[gaz_chops]

That's why I said

Oops my bad! You're correct should be =""

 

[jasonb75]

Oops, overlapping replies again.

 

[emirmansouri]

Thank you so much jasonb75 & gaz_chops. Does exactly what I require.

: )

 

[gaz_chops]

You're welcome, although Jasonb got it right 1st time.