nth Occurrence lookup within an array returning from specific column

[Safire]

Hello,
I'm trying to do a nth occurrence lookup (probably with a macro), among an array of data and returning the value from a specific column within the same row.

I'm trying to look up student scholarship assignments from the following data:

Scholarship Name	Awardee Student#1	Awardee Student#2	Awardee Student#3	Awardee Student#4
Louis Jones Scholarship	Jonah Band, Class of 2017	Sean Ascot, Class of 2017	Ashley Verra, Class of 2015	Josh Dean, Class of 2018
Blank-Smith Scholarship	Jane Smith, Class of 2018	Jonah Band, Class of 2017
Foundation Scholarship	John Smith, Class of 2015
Class of 1910 Scholarship	Jonah Band, Class of 2017	Nikki McGuire, Class of 2018
Dr. Doug Scholarship	Josh Dean, Class of 2018	Jane Smith, Class of 2018	John Smith, Class of 2015	Sean Ascot, Class of 2017
Daniel Scholarship	Jane Smith, Class of 2018	Sean Ascot, Class of 2017

<tbody>
</tbody>

On a different tab, using the student name as the lookup, the 1st occurrence of the scholarship name would appear in column B, the second scholarship in column C, etc. It would always be returning the data from the scholarship name column.

Student Name for lookup	Scholarship Assignment #1	Scholarship Assignment #2	Scholarship Assignment #3
Jane Smith, Class of 2018	Blank-Smith Scholarship	Dr. Doug Scholarship	Daniel Scholarship
John Smith, Class of 2015

<tbody>
</tbody>

I'm rather new to using macros, my experience being limited to cutting and pasting code. So I'm hoping for some assistance.

Thank you for your help!

 

[GerryZ]

try:

Book1
	A	B	C	D	E
1	Scholarship Name	Awardee Student#1	Awardee Student#2	Awardee Student#3	Awardee Student#4
2	Louis Jones Scholarship	Jonah Band, Class of 2017	Sean Ascot, Class of 2017	Ashley Verra, Class of 2015	Josh Dean, Class of 2018
3	Blank-Smith Scholarship	Jane Smith, Class of 2018	Jonah Band, Class of 2017
4	Foundation Scholarship	John Smith, Class of 2015
5	Class of 1910 Scholarship	Jonah Band, Class of 2017	Nikki McGuire, Class of 2018
6	Dr. Doug Scholarship	Josh Dean, Class of 2018	Jane Smith, Class of 2018	John Smith, Class of 2015	Sean Ascot, Class of 2017
7	Daniel Scholarship	Jane Smith, Class of 2018	Sean Ascot, Class of 2017
8
9
10
11
12	Student Name for lookup	Scholarship Assignment #1	Scholarship Assignment #2	Scholarship Assignment #3
13	Jane Smith, Class of 2018	Blank-Smith Scholarship	Dr. Doug Scholarship	Daniel Scholarship
14	John Smith, Class of 2015	Foundation Scholarship	Dr. Doug Scholarship
Foglio1

Cell Formulas
Range	Formula
B13	{=IFERROR(INDEX($A$2:$A$7,SMALL(IF($B$2:$E$7=$A13,ROW($A$2:$A$7)-ROW($A$2)+1),COLUMNS($B$13:B13))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Safire]

Thank you so much! I think this will work quite well.

 

[GerryZ]

You are welcome I' m glade it helped you
Thank you For Your feed-back