Expected outcome based on 3 criteria

[Neilesh Kumar]

Dear Experts,

Please find here attached sample workbook where I am looking for help to get expected outcome based on 3 criteria. The first criteria would be from cell A1 Sheet1 the second one from cell F1 Sheet1 and the last would be from the Sheet1 dropdown. So if dropdown select as "All" then only Yes marked activities want to get reflect in sheet1 from sheet2 and if any specific activity name selects from Dropdown then only Yes marked activities want to get reflected.

Thank you for your valuable support. Please feel free to revert in case of any further clarification if required.

Regards,

Neilesh

[AliGW]

Which version of Excel are you using?

For this sort of thing, I would set up the dataset as a structured table and use slicers instead of trying to fudge the filtering with formulae.

[Neilesh Kumar]

Thank you so much for precious support currently i am working on 2016 but only by applying the formulae can be seen as best option in my current situation. Though slicers can also give the same expected output but i would prefer using the formulae instead slicer.

Thank you once again AligW. Can you please provide the solution on Formulae based.

Regards,

Neilesh

[AliGW]

No - I don't have time at the moment, sorry. What's the problem with slicers? In what way do you think that formulae are better? Or is this simply an exercise?

[JohnTopley]

Why "Keshav" as outcome if parameter is "Joseph" ?

[Neilesh Kumar]

The reason is Keshav pertains to the same activity which Joseph carries and with Keshav the acitivity due marked as Yes.

[JohnTopley]

Try

=IFERROR(INDEX(Sheet2!A$2:A$9,AGGREGATE(15,6,(ROW($A$2:$A$9)-ROW($A$2)+1)/(Sheet2!$B$2:$B$9=$F$1)/(Sheet2!$C$2:$C$9=$A$1)/(Sheet2!$D$2:$D$9="yes"),ROWS($1:1))),"")

Copy across and down

Your reply conflicts with requirement to match with A1

[wk9128]

worksheet name : Sheet1

Pls Click the left mouse button to select the ranges area A7:D7 , pls Place the following formula in Formula Bar
Enter , Drag down

[kvsrinivasamurthy]

Criteria 1 is A1 of Sheet1. IF DEF is selected only Def with name in A1 is to be selected. How is the expected result selects all names.
Try this.
In A7 copied to full range.

Please Login or Register  to view this content.

[Neilesh Kumar]

Thank you Experts for valuable contribution. Thank you so much once again.