Formulas: mix INDEX&MATCH with IFERROR

[cozzagiorgi]

Hi there!

What a great community this is! I already found many answers to my excel questions, but here is one I can’t seem to find:

I want to build a table which picks musicians according to their instrument from an Excel list. I have built something you can look at here:

https://app.box.com/s/oh2b9csnr2j0o1gt0rin8yum5gle99cp

Ranges A, B and C are my Database, which instrument is played by which musician and in what priority I want to have him in my ensemble.

Range E specifies the instrumentation I need for this particular orchestra.
Range F does an INDEX&MATCH formula to find musicians who play the instrument I want. The formula is: =INDEX(B:B,MATCH(E3,A:A,0)).

And this is where the problems begin: As I want more than one clarinet to play in my ensemble, I need the INDEX&MATCH function to give me only unique values. I know how to find only unique values with the formula I have put in range G, but can I mix these two formulas?
Formula is: {=IFERROR(INDEX($F$2:$F$15, MATCH(0, COUNTIF($G$1:G1, $F$2:$F$15), 0)), "")}

And now the EXTRASUPERBONUSPOINT goes to someone who can find a formula which also considers the priority I have given to the musicians to play in my ensemble. E.g: Whitley Leonard is my choice number one on Clarinet, for the second Clarinet I would like to see Fuentes Eaton (Priority 1), not Delaney Flynn (Priority 2, but right after Whitley Leonard in the database).

Any ideas on how to accomplish this? Of course a vba solution would also be welcome.

 

[cozzagiorgi]

Seems to be a hard question?

If formula isnt possible, is there a vba solution?

 

[Marcelo Branco]

Try this array formula in F2 copied down
=INDEX(B$2:B$81,MATCH(1,IF(A$2:A$81=E2,IF(ISNA(MATCH(B$2:B$81,F$1:F1,0)),IF(C$2:C$81=SMALL(IF(A$2:A$81=E2,IF(ISNA(MATCH(B$2:B$81,F$1:F1,0)),C$2:C$81)),1),1))),0))
Ctrl+Shift+Enter

Hope this helps

M.

 

[cozzagiorgi]

Although I don't understand the formula, this seems to work

Thank you!