Help with INDEX MATCH maybe???

[Hawkmek]

Been inactive for years and just logged back in using my 2004 Password!
Looking for some assistance on this sheet my wife sent this morning. Should be easy for you guys on this board. Pretty sure it can be accomplished with those hairy INDEX/MATCH functions but they are Greek to me!

We need to populate the Student's Books columns C,D,E from the Book List. I use VLOOKUP all the time, but there are duplicate Student IDs on this list and it isn't working very well. I get Book1 only in all three Book columns. Students will never have more than 3 books if that matters.


Column A---------B------------C----------D-----------E
STUDENT LIST
Student ID------ Course-----Book1-----Book2-------Book3
11111------------Math
22222------------English
33333------------Math

Column M----------N
BOOK LIST
Student ID-------Book
11111------- Math 1
11111------- Math 2
11111------- Math 3
22222------- Reading
22222------- Writing
33333------- Math 3

Thanks for the help!

Hawkmek

 

[cunningAce]

Hi,

This is an array formula so enter it as usual, and then confirm with ctrl - shift - enter. drag down and across.

Book1
	A	B	C	D	E
1	Student ID	Course	Book 1	Book 2	Book 3
2	11111	Math	Math 1	Math 2	Math 3
3	22222	English	Reading	Writing
4	33333	Math	Math 3
5
6
7
8
9	Student ID	Book
10	11111	Math 1
11	11111	Math 2
12	11111	Math 3
13	22222	Reading
14	22222	Writing
15	33333	Math 3
Sheet1

 

[cunningAce]

Apologies,

Forgot the formulas.

Book1
	A	B	C	D	E
1	Student ID	Course	Book 1	Book 2	Book 3
2	11111	Math	Math 1	Math 2	Math 3
3	22222	English	Reading	Writing
4	33333	Math	Math 3
5
6
7
8
9	Student ID	Book
10	11111	Math 1
11	11111	Math 2
12	11111	Math 3
13	22222	Reading
14	22222	Writing
15	33333	Math 3
Sheet1

Cell Formulas
Range	Formula
C2	{=IFERROR(INDEX($B$10:$B$15,SMALL(IF($A$10:$A$15=$A2,ROW($A$10:$A$15)-ROW($A$10)+1),COLUMNS($A$2:A2))),"")}
C3	{=IFERROR(INDEX($B$10:$B$15,SMALL(IF($A$10:$A$15=$A3,ROW($A$10:$A$15)-ROW($A$10)+1),COLUMNS($A$2:A3))),"")}
C4	{=IFERROR(INDEX($B$10:$B$15,SMALL(IF($A$10:$A$15=$A4,ROW($A$10:$A$15)-ROW($A$10)+1),COLUMNS($A$2:A4))),"")}
C5	{=IFERROR(INDEX($B$10:$B$15,SMALL(IF($A$10:$A$15=$A$2,ROW($A$10:$A$15)-ROW($A$10)+1),ROWS($A$2:A5))),"")}
C6	{=IFERROR(INDEX($B$10:$B$15,SMALL(IF($A$10:$A$15=$A$2,ROW($A$10:$A$15)-ROW($A$10)+1),ROWS($A$2:A6))),"")}
D2	{=IFERROR(INDEX($B$10:$B$15,SMALL(IF($A$10:$A$15=$A2,ROW($A$10:$A$15)-ROW($A$10)+1),COLUMNS($A$2:B2))),"")}
D3	{=IFERROR(INDEX($B$10:$B$15,SMALL(IF($A$10:$A$15=$A3,ROW($A$10:$A$15)-ROW($A$10)+1),COLUMNS($A$2:B3))),"")}
D4	{=IFERROR(INDEX($B$10:$B$15,SMALL(IF($A$10:$A$15=$A4,ROW($A$10:$A$15)-ROW($A$10)+1),COLUMNS($A$2:B4))),"")}
E2	{=IFERROR(INDEX($B$10:$B$15,SMALL(IF($A$10:$A$15=$A2,ROW($A$10:$A$15)-ROW($A$10)+1),COLUMNS($A$2:C2))),"")}
E3	{=IFERROR(INDEX($B$10:$B$15,SMALL(IF($A$10:$A$15=$A3,ROW($A$10:$A$15)-ROW($A$10)+1),COLUMNS($A$2:C3))),"")}
E4	{=IFERROR(INDEX($B$10:$B$15,SMALL(IF($A$10:$A$15=$A4,ROW($A$10:$A$15)-ROW($A$10)+1),COLUMNS($A$2:C4))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Hawkmek]

Thank you for the quick reply and please excuse my ignorance.

What does this mean? Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces

I cut and pasted all but the brackets and got nothing. The formulas are int he right cells, but they are blank.

Thx again!

 

[cunningAce]

Paste this formula into C2,

=IFERROR(​

INDEX($B$10:$B$15,SMALL(IF($A$10:$A$15=$A2,ROW($A$10:$A$15)-ROW($A$10)+1),COLUMNS($A$2:A2))),""​

)​

Press Ctrl + Shift + Enter at the same time to confirm the formula. This is an array formula that wont work if you just hit enter after typing it in.
Then you can drag down and across as far as you need.

 

[Hawkmek]

OK, I got it! I had to be in 'EDIT MODE' in Cell C2 to do the C+S+E thing. Pretty cool stuff.
I'll work with getting this going in the real deal sheet now.

Thank you so much. Good see Mr. Excel still going strong after all these years and the service never dropped off a bit!

I'll be back later with another issue. Have a good weekend!

 

[cunningAce]

YourWelcome

OK, I got it! I had to be in 'EDIT MODE' in Cell C2 to do the C+S+E thing. Pretty cool stuff.
I'll work with getting this going in the real deal sheet now.

Thank you so much. Good see Mr. Excel still going strong after all these years and the service never dropped off a bit!

I'll be back later with another issue. Have a good weekend!