Keyword Matching

Try this in B2 and copy down

=IFERROR(IF(LOOKUP(10^308,SEARCH($C$2:$C$4,A2)),1),"")

Row\Col	A	B	C
2	Accounts Manager		Administrator
3	Chief Information Officer	1	Information
4	Commercial Manager		Software
5	Contract Manager
6	Deputy Chief Information Officer	1
7	Director of Research QA
8	Director of Transition Organization
9	Group Publisher
10	Head of Information Technology	1
11	Head of Information Technology	1
12	Head of Support
13	Information Technology Director	1
14	Information Technology Director	1
15	Information Technology Director	1
16	Information Technology Manager	1
17	Information Technology Manager	1
18	Information Technology Manager	1
19	Information Technology Project Manager	1
20	Language Manager
21	Office Manager
22	PR Director
23	Priority Specialist
24	Quality Director
25	Software Architect	1
26	Software Developer	1
27	Software Developer	1
28	Software Engineer	1
29	Software Manager	1
30	VP of Information Technology	1
31	VP of Marketing

Do you mean instead of 1 return the keyword?

=IFERROR(LOOKUP(10^308,SEARCH($C$2:$C$4,A2),$C$2:$C$3),"")

Row\Col	A	B	C
2	Accounts Manager		Administrator
3	Chief Information Officer	Information	Information
4	Commercial Manager		Software
5	Contract Manager
6	Deputy Chief Information Officer	Information
7	Director of Research QA
8	Director of Transition Organization
9	Group Publisher
10	Head of Information Technology	Information
11	Head of Information Technology	Information
12	Head of Support
13	Information Technology Director	Information
14	Information Technology Director	Information
15	Information Technology Director	Information
16	Information Technology Manager	Information
17	Information Technology Manager	Information
18	Information Technology Manager	Information
19	Information Technology Project Manager	Information
20	Language Manager
21	Office Manager
22	PR Director
23	Priority Specialist
24	Quality Director
25	Software Architect	Software
26	Software Developer	Software
27	Software Developer	Software
28	Software Engineer	Software
29	Software Manager	Software
30	VP of Information Technology	Information
31	VP of Marketing

Originally Posted by sathishkm

What is 10^308 in lookup formula? What exactly does it function.

This is an incredibly large number that can entered in Excel and most likely doesn't exist withing the lookup_vector. Since the LOOKUP function typically works on a sorted (ascending order) range it will go for the last row assuming that this where the largest value exists. However, when used with SEARCH or FIND functions, the only number it will find is the one returned by search function.