Formulas: mix INDEX&MATCH with IFERROR

cozzagiorgi

New Member
Joined
Jun 27, 2018
Messages
41
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:

Some videos you may like

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.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,451
Messages
5,528,829
Members
409,839
Latest member
akashsadhu
Top