create a sub array with multiple criteria

I have an array of data but i want to create a sub array based on 2 criteria

my formula works fine with one criteron but not works with multiple criteria. I also found my formula does not use calculated fields as condition


{=IFERROR(INDEX('PMO - VSN'!D$4:D$900,SMALL(IF('PMO - VSN'!$AJ$4:$AJ$900="-",ROW('PMO - VSN'!D$4:D$900)-ROW('PMO - VSN'!D$4)+1),ROWS('PMO - VSN'!D$4:D4))),"")} or
{=IFERROR(INDEX('PMO - VSN'!D$4:D$900,SMALL(IF('PMO - VSN'!$AN$4:$AN$900>DATE(2017,6,30),ROW('PMO - VSN'!D$4:D$900)-ROW('PMO - VSN'!D$4)+1),ROWS('PMO - VSN'!D$4:D4))),"")}


both of them works individually but when i combine them into one formula, only the 1st cell of the result appears and the rest of them are blank

{=IFERROR(INDEX('PMO - VSN'!D$4:D$900,SMALL(IF(AND('PMO - VSN'!$AJ$4:$AJ$900="-",'PMO - VSN'!$AN$4:$AN$900>DATE(2017,6,30)),ROW('PMO - VSN'!D$4:D$900)-ROW('PMO - VSN'!D$4)+1),ROWS('PMO - VSN'!D$4:D4))),"")}

I tried to generate a new field in row data to combine both criteria but does not work




can anyone help?

Hello mfhxilc2 and Welcome to Excel Forum.
Try the following:

Formula:

=IFERROR(INDEX('PMO - VSN'!D$4:D$900,SMALL(IF('PMO - VSN'!$AJ$4:$AJ$900="-",IF('PMO - VSN'!$AN$4:$AN$900>DATE(2017,6,30),ROW('PMO - VSN'!D$4:D$900)-ROW('PMO - VSN'!D$4)+1)),ROWS('PMO - VSN'!D$4:D4))),"")

With the 2010 version it is possible that this could be rewritten as an AGGREGATE based formula and not need to be array entered. If you could upload a sample of your data we could attempt to write that for you.
To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

The following regular AGGREGATE based formulas yield the same results as those that were previously in columns A, C, and G. I believe that the formula for column E is yielding the correct results although different from what displayed in the file attached to post #3.
Column A:

Formula:

=IFERROR(INDEX('PMO - VSN'!D$4:D$375,AGGREGATE(15,6,(ROW('PMO - VSN'!D$4:D$375)-ROW('PMO - VSN'!D$4)+1)/('PMO - VSN'!$AJ$4:$AJ$375="-"),ROWS('PMO - VSN'!D$4:D4))),"")

Column C:

Formula:

=IFERROR(INDEX('PMO - VSN'!D$4:D$375,AGGREGATE(15,6,(ROW('PMO - VSN'!D$4:D$375)-ROW('PMO - VSN'!D$4)+1)/('PMO - VSN'!$AN$4:$AN$375>DATE(2017,6,30)),ROWS('PMO - VSN'!D$4:D4))),"")

Column E:

Formula:

=IFERROR(INDEX('PMO - VSN'!D$4:D$375,AGGREGATE(15,6,(ROW('PMO - VSN'!D$4:D$375)-ROW('PMO - VSN'!D$4)+1)/(('PMO - VSN'!$AJ$4:$AJ$375="-")*('PMO - VSN'!$AN$4:$AN$375>DATE(2017,6,30))),ROWS('PMO - VSN'!D$4:D4))),"")

Column G:

Formula:

=IFERROR(INDEX('PMO - VSN'!D$4:D$375,AGGREGATE(15,6,(ROW('PMO - VSN'!D$4:D$375)-ROW('PMO - VSN'!D$4)+1)/('PMO - VSN'!$BU$4:$BU$375="Yes"),ROWS('PMO - VSN'!D$4:D4))),"")

Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools drop down in the ribbon above your first post. I hope that you have a blessed day.