Excel and Match Index

[Karen2403]

I have a dataset in excel as below (this is made up data). On a separate sheet I have drop down lists to select the subject ie Mechanics or Engineering and then a second drop down to select the level ie Awareness or Senior.

I have tried so many formulas and I think my problem is using a range rather than a column. Is it possible to use index match or anything so that if I select Engineering and Awareness, it will then list the people who meet this criteria ie John Smith and Peter Jones? I can get it to work if I stipulate the column to look in (in bold below) but I this will change dependent on what is selected in the drop down box, so I need a range in here if at all possible.

Full Name Mechanics Engineering Software

John Smith Awareness Awareness Expert
Paul Walters Senior Senior Senior
Peter Jones Senior Awareness Awareness


=IFERROR(INDEX(Employee_Data[Full Name],SMALL(IF(Employee_Data[Engineering]=$K$3,ROW(Employee_Data[EmployeeID])-ROW(INDEX(Employee_Data[EmployeeID],1,1))+1), ROWS($D$2:D2))),"")

Any help appreciated

 

[Fluff]

How about

=IFERROR(INDEX(Employee_Data[full Name],AGGREGATE(15,6,(ROW(Employee_Data[full Name])-ROW(Employee_Data[[#Headers],[full Name]]))/(INDEX(Employee_Data,,MATCH(K$2,Employee_Data[#Headers],0)) Employee_Data[Engineering]=$K$3),ROWS($D$2:D2))),"")

Where K2 has the header value

 

[Karen2403]

How about

=IFERROR(INDEX(Employee_Data[full Name],AGGREGATE(15,6,(ROW(Employee_Data[full Name])-ROW(Employee_Data[[#Headers],[full Name]]))/(INDEX(Employee_Data,,MATCH(K$2,Employee_Data[#Headers],0)) Employee_Data[Engineering]=$K$3),ROWS($D$2:D2))),"")

Where K2 has the header value

Morning

I have tried this but I can't get it to work . I am attaching a dummy dataset to explain better what I would like to do. On sheet 'Search', I would like to select the three items highlighted in green and then on the same sheet in rows 9 downwards, for it to return everyone from the employee data who matches these 3 criteria, similar to that in the sheet 'Func Area'. Is this possible?

Many thanks

 

[Fluff]

The formula should be

=IFERROR(INDEX(Employee_Data[full Name],AGGREGATE(15,6,(ROW(Employee_Data[full Name])-ROW(Employee_Data[[#Headers],[full Name]]))/(INDEX(Employee_Data,,MATCH(F$3,Employee_Data[#Headers],0))=$I$3),ROWS($D$2:D2))),"")

although I'm not sure where the C3 value fits in.

 

[Karen2403]

The formula should be

=IFERROR(INDEX(Employee_Data[full Name],AGGREGATE(15,6,(ROW(Employee_Data[full Name])-ROW(Employee_Data[[#Headers],[full Name]]))/(INDEX(Employee_Data,,MATCH(F$3,Employee_Data[#Headers],0))=$I$3),ROWS($D$2:D2))),"")

although I'm not sure where the C3 value fits in.

Wow - works perfectly - thank you so much.

I'm guessing I only need the C3 to direct the drop down in F3 - I absolutely would not have worked this out on my own so I am very grateful!

 

[Fluff]

You're welcome & thanks for the feedback.