Match Keyword in Excel

[Mohanmoni]

Hi,

I am looking for formula that match keyword in from 2 files.

File1:

[TABLE="width: 190"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Keywords Exclude[/TD]
[/TR]
[TR]
[TD]intern[/TD]
[/TR]
[TR]
[TD]accountant[/TD]
[/TR]
[TR]
[TD]accounting[/TD]
[/TR]
[TR]
[TD]admin[/TD]
[/TR]
[TR]
[TD]advertising[/TD]
[/TR]
[TR]
[TD]advisor[/TD]
[/TR]
[TR]
[TD]analyst[/TD]
[/TR]
[TR]
[TD]architect[/TD]
[/TR]
[TR]
[TD]assistant to vice president[/TD]
[/TR]
[TR]
[TD]assistant to vp[/TD]
[/TR]
[TR]
[TD]Attorney[/TD]
[/TR]
[TR]
[TD]auditor[/TD]
[/TR]
[TR]
[TD]Author[/TD]
[/TR]
[TR]
[TD]brand[/TD]
[/TR]
[TR]
[TD]Building[/TD]
[/TR]
[TR]
[TD]Bus Development[/TD]
[/TR]
[TR]
[TD]business[/TD]
[/TR]
[TR]
[TD]cafeteria[/TD]
[/TR]
[TR]
[TD]cash[/TD]
[/TR]
[TR]
[TD]Category[/TD]
[/TR]
[TR]
[TD]catering

and File2:

[TABLE="width: 463"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Title[/TD]
[/TR]
[TR]
[TD]Vice President - Operations[/TD]
[/TR]
[TR]
[TD]Senior Product Manager, Single Cell Genomics[/TD]
[/TR]
[TR]
[TD]Vice President, Architect Manufacturing And Process Development[/TD]
[/TR]
[TR]
[TD]Head of Manufacturing Systems Architect[/TD]
[/TR]
[TR]
[TD]Director - Operations[/TD]
[/TR]
[TR]
[TD]Operations Analyst[/TD]
[/TR]
[TR]
[TD]Operations Specialist[/TD]
[/TR]
[TR]
[TD]Director of Operations[/TD]
[/TR]
[TR]
[TD]Residential Operations Admin. Manager[/TD]
[/TR]
[TR]
[TD]Director of Operations[/TD]
[/TR]
[TR]
[TD]Operations Manager

Now I want the formula the find Keyword mentioned in file1 & Match with File2.

Thanks,
Mohan[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

As you didn't explain how you want your results, and I'm not sure what you mean by "file1", "file2", different workbooks, or tabs?

Anyway, maybe this will get you started:

Book1
	A	B	C	D	E
1	Keywords Exclude		Title		Keyword
2	intern		Vice President - Operations
3	accountant		Senior Product Manager, Single Cell Genomics
4	accounting		Vice President, Architect Manufacturing And Process Development		architect
5	admin		Head of Manufacturing Systems Architect		architect
6	advertising		Director - Operations
7	advisor		Operations Analyst		analyst
8	analyst		Operations Specialist
9	architect		Director of Operations
10	assistant to vice president		Residential Operations Admin. Manager
11	assistant to vp		Director of Operations
12	Attorney		Operations Manager
13	auditor
14	Author
15	brand
16	Building
17	Bus Development
18	business
19	cafeteria
20	cash
21	Category
22	catering
Sheet323

Cell Formulas
Range	Formula
E2	=IFERROR(LOOKUP(2,1/SEARCH(" "&A$2:A$22&" "," "&C2&" "),A$2:A$22),"")

E2 formula copied down.

 

[Aladin Akyurek]

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&A$2:A$22&" "," "&C2&" "),A$2:A$22),"")

which is faster...