If cell contains text found in list

[mljohn]

How do I find a word contained in a cell that matches a word in a list in a separate sheet?

I am using this formula now but it is getting too long.

=IFERROR(LOOKUP(1E+100,SEARCH({"Ink";"Toner";"Candy";"Drum";"Micr";"TNR";"WASTE";"Ribbon";"PRINT CTG";"Staple"},C2),{"Ink";"Toner";"Candy";"Drum";"Micr";"Toner";"WASTE";"Ribbon";"PRINT CTG";"Staple"}),""))

I would like to have a list of words in Sheet2 and have the formula in Sheet1 look for a match found in sheet2.

Matt

 

[nigelk]

You could try using the "Match" function. Your list of words is in sheet 2. In sheet 1, A1, you could put in word you need to find and then in B1, it would be:

=MATCH(A1,Sheet2!A:A,0)

which would return the row number of where the word is.

Nigel.

 

[Domenic]

If, for example, Sheet2!A2:A12 contains the list, the following formula could be used...

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$2:$A$12,C2),Sheet2!$A$2:$A$12),""))

 

[mljohn]

Domenic,

I can't get your formula to work.

 

[mljohn]

It is pasting as text

 

[T. Valko]

How do I find a word contained in a cell that matches a word in a list in a separate sheet?

I am using this formula now but it is getting too long.

=IFERROR(LOOKUP(1E+100,SEARCH({"Ink";"Toner";"Candy";"Drum";"Micr";"TNR";"WASTE";"Ribbon";"PRINT CTG";"Staple"},C2),{"Ink";"Toner";"Candy";"Drum";"Micr";"Toner";"WASTE";"Ribbon";"PRINT CTG";"Staple"}),""))

I would like to have a list of words in Sheet2 and have the formula in Sheet1 look for a match found in sheet2.

Matt

Create a list like this...

Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 78px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Ink</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Candy</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Micr</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">WASTE</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">PRINT CTG</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Toner</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Drum</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">TNR</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Ribbon</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Staple</TD></TR></TBODY></TABLE>


Then the formula becomes:

=IFERROR(LOOKUP(1E100,SEARCH(Sheet2!A$2:A$11,C2),Sheet2!A$2:A$11),"")

 

[Aladin Akyurek]

It is pasting as text

You need to create a list on Sheet2 from A2 on housing

Ink
Toner
Candy
Drum

and so on and run the suggested formula on Sheet1, say, in D2

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$2:$A$11,C2),Sheet2!$A$2:$A$11),""))

If you name the range on Sheet2 housing the list of your keywords, say, KeywordList, the formula becomes:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(KeywordList,C2),KeywordList),""))

 

[T. Valko]

If, for example, Sheet2!A2:A12 contains the list, the following formula could be used...

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(Sheet2!$A$2:$A$12,C2),Sheet2!$A$2:$A$12),""))

Domenic,

I can't get your formula to work.

There's an extra closing parenthesis ")" in the formula...

=IFERROR(LOOKUP(1E100,SEARCH(Sheet2!$A$2:$A$12,C2),Sheet2!$A$2:$A$12),"")

 

[mljohn]

Thanks that works great!

 

[T. Valko]

Thanks that works great!

You're welcome. Thanks for the feedback!