Index Match with Partial Match in Lookup Array

This is doing my head in because I am sure I have done it before, but just can't get the syntax right!

I have this on my summary sheet:

Excel 2010 32 bit

	B	C	D	E	F	G
2	ID	First Language	Gender	Boarder	House	Academic Scholar
3
4
5	1630512497	English	F		HD
6	0912236113857	English	F		RL
7	141933705547	English	M	Y	SV
8	1634630165	English	M		CW
9	141022705547	English	F	Y	HD
10	1637242533	English	M		RL	Y

Sheet: Summary Data

I am trying to populate the academic scholar column with "Y" if the corresponding row here contains the word "Academic":

Excel 2010 32 bit

	F	L
1	Scholarship List	SchoolID
2		141933705547
3		135903705547
4	Sailing	130239705547
5		132411705547
6		102451705547
7		104143705547
8		165353705547
9		0913041499961
10	Music	1733060042
11		0913115655475
12	Academic, Music	131341705547
13	Sailing	151120705547
14		163406705547
15		1753219100
16	Academic	1804166356

Sheet: Personal Data

I am using the ID as the match. I am at work and do not have all of my test files to hand, so can't find the file where I did this before: could some kind soul put me out of my misery? By the way, I know there is a leading space in the lookup data column which is the fault of the report generator in the MIS software we use. This is just a small sample of the data: there are matches in the full dataset, and that is not the issue: it's dealing with the match when there is more text in the lookup column (e.g. F12). Thanks in advance!

Hi,

It appears you want something like
=if(isnumber(search("academic",index('personal data'!F:F,match(b5,'personal data'!L:L,0)))),"Y","")

I'm posting from a phone but hopefully got the correct brackets in there.

Thank you!

I knew it was easy ...