Another lookup multiple values with multiple criteria showstopper

[ykobure]

Hi experts!

I am currently working on with a formula-driven sheets in Excel, and as far as I am concern, I am pretty much happy with what it can do now. But alas, a new requirement from the big boss surfaces and I can't seem to marry together his wants to my current system. I explained some workarounds, yet his tantrums prevail.

This is how the system goes:

• Sheet1 serves as the "dashboard", with formulas and all
• Sheet2 is the main database
• On Sheet1, $D$1 has a Data Validation list that filters the table below based on its status (Ongoing, Completed, All Projects, and end dates of the projects)

Now to my problem, basically I am good with the current functionality. What I am asking now is how to add his preference to the system.

• When I select a month (yellow cell), apart from the projects with "Ongoing" (and "Completed") status for the said month listed in the table, I also want to show projects from previous months with "Ongoing" status
• For this example, if I select "March" (or 31-Mar-15) from the drop down menu, "Ongoing" projects from previous months (say February and January) will also need to appear in the table.

Attached is a sample file, with formulas and all for your reference.
Lookup Multiple Values with Multiple Critera.xlsx

Hope you guys will be able to help me on this, and a million thanks in advance.

[FDibbins]

I have a different approach for you. Instead of using all those ARRAY formulas - which will start to slow your file down - use a helper in sheet 2 (I used Y), put this in Y2, and copy down...
=IF(Sheet1!$D$1="all projects",Y2+1,IF(AND(Sheet1!$D$1="Ongoing",R3="Ongoing"),Y2+1,IF(AND(Sheet1!$D$1="Completed",R3="Completed"),Y2+1,IF(ISNUMBER(Sheet1!$D$1),IF(OR(Q3=Sheet1!$D$1,AND(R3="Ongoing",Q3<=Sheet1!$D$1)),Y2+1,Y2),Y2))))
You can hide this if you want.

Then to pull out the data...
=iferror(INDEX(Sheet2!$A:$W,MATCH(ROW(A1),Sheet2!Y:Y,0),MATCH(Sheet1!A$3,Sheet2!$A$2:$W$2,0)),"")
copy across and down as needed

[ykobure]

I have a different approach for you. Instead of using all those ARRAY formulas - which will start to slow your file down - use a helper in sheet 2 (I used Y), put this in Y2, and copy down...
=IF(Sheet1!$D$1="all projects",Y2+1,IF(AND(Sheet1!$D$1="Ongoing",R3="Ongoing"),Y2+1,IF(AND(Sheet1!$D$1="Completed",R3="Completed"),Y2+1,IF(ISNUMBER(Sheet1!$D$1),IF(OR(Q3=Sheet1!$D$1,AND(R3="Ongoing",Q3<=Sheet1!$D$1)),Y2+1,Y2),Y2))))
You can hide this if you want.

Then to pull out the data...
=iferror(INDEX(Sheet2!$A:$W,MATCH(ROW(A1),Sheet2!Y:Y,0),MATCH(Sheet1!A$3,Sheet2!$A$2:$W$2,0)),"")
copy across and down as needed

I was actually thinking of restructuring the entire formula to accommodate my boss's request. I just want to know if there is a way to incorporate that to the existing formula, or should I take a detour.

I will definitely try that sir, and will get back to you with the results! Thank you, good sir!

[ykobure]

@fdibbins, I tried your workaround, but it returns a result I am not looking for. Upon entering the above formula on Y2, it returns a circular reference error, which also affects the formula you want to inject in Sheet1. Or am I missing something in the process.