Search and return a match from a range with a string of text

[VictoriaExcel]

Hi,

I need to search for a match between a range of text and strings of data in a table. If a match is found then I wish to return that match into the next column.

I have tried this formula =IF(ISNUMBER(SEARCH(Schools,[@Tags])),Schools,"") but it will only return data for cells on the same rows as the match with the range.

More details below ...

Range named 'Schools' on separate sheet (what is being searched for and also what should be returned:

[TABLE="width: 408"]
<tbody>[TR]
[TD]School of Art Architecture & Design[/TD]
[/TR]
[TR]
[TD]School of Built Environment & Engineering[/TD]
[/TR]
[TR]
[TD]Carnegie School of Education[/TD]
[/TR]
[TR]
[TD]Carnegie School of Sport[/TD]
[/TR]
[TR]
[TD]School of Clinical & Applied Sciences[/TD]
[/TR]
[TR]
[TD]School of Computing Creative Technologies & Engineering[/TD]
[/TR]
[TR]
[TD]School of Cultural Studies & Humanities[/TD]
[/TR]
[TR]
[TD]Department of Languages[/TD]
[/TR]
[TR]
[TD]School of Events Tourism & Hospitality Management[/TD]
[/TR]
[TR]
[TD]School of Film Music & Performing Arts[/TD]
[/TR]
[TR]
[TD]School of Health & Community Studies[/TD]
[/TR]
[TR]
[TD]Leeds Business School[/TD]
[/TR]
[TR]
[TD]Leeds Law School[/TD]
[/TR]
[TR]
[TD]Leeds School of Social Sciences


Example strings of text from 5 of 33,522 rows:

[TABLE="width: 1000"]
<tbody>[TR]
[TD]GuestMon15Jul1230,Monday 15 July 12:30,School of Clinical & Applied Sciences,Graduands & Guests,[/TD]
[/TR]
[TR]
[TD]GraduandMon15Jul1230,Monday 15 July 12:30,School of Clinical & Applied Sciences,Graduands & Guests,[/TD]
[/TR]
[TR]
[TD]School of Health & Community Studies,Monday 15 July 15:00,Black & White Lounge,HGMon15Jul1500,Honorary Graduates and Guests,[/TD]
[/TR]
[TR]
[TD]School of Clinical & Applied Sciences,Monday 15 July 12:30,Black & White Lounge,SGMon15Jul1230,Special Guests,[/TD]
[/TR]
[TR]
[TD]GuestThu18Jul1230,Leeds Business School,Thursday 18 July 12:30,Posthumous Award Guests,[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]
Many thanks in advance,

Victoria

 

[Peter_SSs]

Something like this?

Excel Workbook
	C	D	E	F
1	Tags	School		School of Art Architecture & Design
2	GuestMon15Jul1230,Monday 15 July 12:30,School of Clinical & Applied Sciences,Graduands & Guests,	School of Clinical & Applied Sciences		School of Built Environment & Engineering
3	GraduandMon15Jul1230,Monday 15 July 12:30,School of Clinical & Applied Sciences,Graduands & Guests,	School of Clinical & Applied Sciences		Carnegie School of Education
4	School of Health & Community Studies,Monday 15 July 15:00,Black & White Lounge,HGMon15Jul1500,Honorary Graduates and Guests,	School of Health & Community Studies		Carnegie School of Sport
5	School of Clinical & Applied Sciences,Monday 15 July 12:30,Black & White Lounge,SGMon15Jul1230,Special Guests,	School of Clinical & Applied Sciences		School of Clinical & Applied Sciences
6	GuestThu18Jul1230,Leeds Business School,Thursday 18 July 12:30,Posthumous Award Guests,	Leeds Business School		School of Computing Creative Technologies & Engineering
7				School of Cultural Studies & Humanities
8				Department of Languages
9				School of Events Tourism & Hospitality Management
10				School of Film Music & Performing Arts
11				School of Health & Community Studies
12				Leeds Business School
13				Leeds Law School
14				Leeds School of Social Sciences
Lookup

#VALUE!

</td></tr></table></td></tr></table>

 

[VictoriaExcel]

That worked perfectly, thank you!

 

[Peter_SSs]

That worked perfectly, thank you!

You're welcome. Thanks for the follow-up.