Wildcard Lookup Using Excel

Hello All,

Ive tried all possible formulas to perform wildcard lookup bu invain

My requirement is as described below:
Sheet 1 have the following data in column A.
Solar
Wind
Fuel Cells
Renewable

Sheet 2 have the following data in column A
American Renewable Corp.
Infosys Ltd.
Tata BP Solar

I need flags in Sheet 2 if the text has words mentioned in Sheet 1.
Eg: American Renewable Corp. (sheet 2). has the word Renewable (sheet 1). Hence I want excel to add some kind of a flag in Sheet 2 adjacent to the cell which has the text "American Renewable Corp.".

Similarly I want excel to compare other texts which is on Sheet 2.

Please can someone help me

Thanks!

Originally Posted by Marcol

One way might be to use named ranges and conditional formatting.

Select from the dropdown in C2, this is the named range "FindWord" with data validation = "RefTable"
"RefTable" is dynamic, add more words to find as required.

Hope this helps.

[EDIT]
Added count of matches found in Sheet2 to cell D2 Sheet1

Thanks Marcol for the fast reply

Thanks a ton Daddylonglegs This really helps You made my day!

Originally Posted by daddylonglegs

Assuming data starts at row 2 in each sheet then try this formula in B2 in sheet2

=IF(COUNT(LOOKUP(2^15,SEARCH(Sheet1!A$2:A$5,A2))),"Yes","")

copy down

Hi, an extension to Dadylonglegs 's suggested formula. This will give name of defined word occurred
=VLOOKUP(SUMPRODUCT(Sheet1!$A$2:$A$5,ABS((ISERROR(SEARCH(Sheet1!$B$2:$B$5,A2)))-1)),Sheet1!$A$1:$B$6,2,1)

RESULT
Company Name Sector Type
American Renewable Corp. Renewable
Infosys None
Tata BP Solar Solar
SolarWindFuel CellsRenewable More than 1 word

Originally Posted by daddylonglegs

Assuming data starts at row 2 in each sheet then try this formula in B2 in sheet2

=IF(COUNT(LOOKUP(2^15,SEARCH(Sheet1!A$2:A$5,A2))),"Yes","")

copy down

Hi, an extension to Dadylonglegs 's suggested formula. This will give name of defined word occurred
=VLOOKUP(SUMPRODUCT(Sheet1!$A$2:$A$5,ABS((ISERROR(SEARCH(Sheet1!$B$2:$B$5,A2)))-1)),Sheet1!$A$1:$B$6,2,1)

RESULT
Company Name Sector Type
American Renewable Corp. Renewable
Infosys None
Tata BP Solar Solar
SolarWindFuel CellsRenewable More than 1 word