Match Keyword in Excel

[Mohanmoni]

Hi,

I am looking for excel formula i.e matches keyword in entire column. for example in "Column A" i have 1000 list of details about products. Now i want to find who are using product "Red", "Green", "Orange"......etc

Column "A"
ABC Red
Green XYZ
Orange GHI
--
--
--

Please get me formula for find the cell that contains above keyword (Red, Green, Orange...)

Regards,
Mohan

 

[Aladin Akyurek]

What is the output that you require for the sample you posted?

 

[Mohanmoni]

I want to see the list that contains the keyword (Red, Green, Orange...etc) in "Column A"

 

[BrianJN1]

Hi,

I am looking for excel formula i.e matches keyword in entire column. for example in "Column A" i have 1000 list of details about products. Now i want to find who are using product "Red", "Green", "Orange"......etc

Column "A"
ABC Red
Green XYZ
Orange GHI
--
--
--

Please get me formula for find the cell that contains above keyword (Red, Green, Orange...)

Regards,
Mohan

We would want more details if to assist. Firstly your example is confusing as the initial entry is not consistent with the other two.
Secondly, are those products replicated in that list?
Lastly, where are the clients/users located, and are their names also likely to be replicated?

Um? What do you wish as an outcome? Like, how would you see your report?

I'm unlikely to develop any of that, but they are questions that clearly need to be answered.

 

[Mohanmoni]

[TABLE="width: 1066"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Title[/TD]
[TD]Keyword[/TD]
[TD][/TD]
[TD]Title_Result[/TD]
[TD]Keyword[/TD]
[/TR]
[TR]
[TD]Technology - Compliance and Business Continuity[/TD]
[TD]Human Resource[/TD]
[TD][/TD]
[TD]Technology - Compliance and Business Continuity[/TD]
[TD]Technology[/TD]
[/TR]
[TR]
[TD]Foundation Training Operations[/TD]
[TD]Operation[/TD]
[TD][/TD]
[TD]Foundation Training Operations[/TD]
[TD]Operation[/TD]
[/TR]
[TR]
[TD]Human Resource Supervisor[/TD]
[TD]Information Technology[/TD]
[TD][/TD]
[TD]Human Resource Supervisor[/TD]
[TD]Human Resource[/TD]
[/TR]
[TR]
[TD]Vendor Training Operations[/TD]
[TD]Engineering[/TD]
[TD][/TD]
[TD]Vendor Training Operations[/TD]
[TD]Operation[/TD]
[/TR]
[TR]
[TD]Industry and Application Delivery Training Operations[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Industry and Application Delivery Training Operations[/TD]
[TD]Operation[/TD]
[/TR]
[TR]
[TD]Vice President, Claim Operations[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Vice President, Claim Operations[/TD]
[TD]Operation[/TD]
[/TR]
[TR]
[TD]Managing Director, Underwriting Operations[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Managing Director, Underwriting Operations[/TD]
[TD]Operation[/TD]
[/TR]
[TR]
[TD]Information Technology Manager[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Information Technology Manager[/TD]
[TD]Information Technology[/TD]
[/TR]
[TR]
[TD]Director of Servicing Carrier Operations[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Director of Servicing Carrier Operations[/TD]
[TD]Operation[/TD]
[/TR]
[TR]
[TD]Vice President, Claim Operations[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Vice President, Claim Operations[/TD]
[TD]Operation[/TD]
[/TR]
[TR]
[TD]Vice President - Regional Operations[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Vice President - Regional Operations[/TD]
[TD]Operation[/TD]
[/TR]
[TR]
[TD]Senior Global Information Technology Officer[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Senior Global Information Technology Officer[/TD]
[TD]Information Technology[/TD]
[/TR]
[TR]
[TD]Vice President Global Operations Compliance[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Vice President Global Operations Compliance[/TD]
[TD]Operation[/TD]
[/TR]
[TR]
[TD]Information Technology Manager[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Information Technology Manager[/TD]
[TD]Information Technology[/TD]
[/TR]
[TR]
[TD]Associate Director Engineering Operations[/TD]
[TD] [/TD]
[TD][/TD]
[TD]Associate Director Engineering Operations[/TD]
[TD]Engineering[/TD]
[/TR]
</tbody>[/TABLE]


Please check above example.

 

[Aladin Akyurek]

Let column A house the data.

Let a convenient range somewhere else house the relevant keywords. Name this range KeywordList via the Name Box or the Name Manager. It's advisable to sort this list in ascending order.

In B2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&KeywordList&" ",$A2),KeywordList)