Alternative to FILTER function in Excel 2010?

[adamchater]

One one sheet I have a large list of doctors, and against each doctor I have listed their secretary. Some of the doctors share the same secretary too.

On a separate sheet, if I choose the secretary, I want to be able to return which doctor(s) that secretary works for.

I got this working perfectly in Excel 365 using the FILTER function:

=FILTER('Consultant Data'!BB9:BB423,'Consultant Data'!N9:N423=Y11,"None")

"Consultant Data" is the sheet containing the list of consultants. Column BB on that sheet is the consultant's full name, and column N is the name of the secretary entered against that consultant. Cell Y11 is the lookup field on my other sheet - this is the name of the secretary I want to return the list of the consultants they are linked to.

I didn't realise FILTER wasn't available for Excel 2010, which is the version most of our users use, and so this won't work for most people.

From searching I realise I probably need some combination of INDEX and maybe AGGREGATE to replace this functionality in Excel 2010. Can anyone advise how to format this please, as I cannot work it out? The filter can return multiple results, as each secretary could look after up to ten consultants. Any assistance would be very gratefully received.

 

[Fluff]

If the formula is in Z11 try

=IFERROR(INDEX('Consultant Data'!BB:BB,AGGREGATE(15,6,ROW('Consultant Data'!$BB$9:$BB$423)/('Consultant Data'!$N$9:$N$423=$Y$11),ROWS(Z$11:Z11))),"None")

 

[adamchater]

If the formula is in Z11 try

=IFERROR(INDEX('Consultant Data'!BB:BB,AGGREGATE(15,6,ROW('Consultant Data'!$BB$9:$BB$423)/('Consultant Data'!$N$9:$N$423=$Y$11),ROWS(Z$11:Z11))),"None")

That's perfect, thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.