# Formulas: mix INDEX&MATCH with IFERROR

#### cozzagiorgi

##### New Member
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.

Last edited:

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### cozzagiorgi

##### New Member
Seems to be a hard question?

If formula isnt possible, is there a vba solution?

#### Marcelo Branco

##### MrExcel MVP
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

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

Thank you!

Replies
1
Views
96
Replies
14
Views
99
Replies
2
Views
66
Replies
20
Views
170
Replies
3
Views
80