Returning Value for Search

[nycard64]

I am using the following formula below to search in a cell for 13 items in quotes, then returning a Y or N. Can I return the actually value I am searching for within the cell? eg. if I am searching for "EHARP" i return "EHARP" instead of a "Y".

=IF( SUM(--ISNUMBER(SEARCH({"EHARP","PTEOS","OMCTS","NBLOK","FTEOS","UVN","NIT","SiCOH","SiCOHCure","SILHS","LTO","TEOS","OZONE"}, Q43))),"Y","N")

Thanks

 

[Jonmo1]

Try

=LOOKUP(2^15,SEARCH(" " &$E$1:$E$13 & " "," " & Q43 & " "),$E$1:$E$13)

E1:E13 = your list of 13 items

Hope that helps.

 

[nycard64]

I input the following formula, my first row starts at P3 was off by a column sorry.

=LOOKUP(2^15,SEARCH(" " &'Process List'!$A$2:$A$14 & " "," " & P3 & " "),'Process List'!$A$2:$A$14)

and put the item list on another tab, I am getting #N/A in field. Not sure why.

 

[Jonmo1]

It works for me..

An error in formula means none of the values from Process List - A2:A14 exist in P3
Is the formula entered on the same sheet that holds P3 ?

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 135px"><COL style="WIDTH: 74px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid">P</TD><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid">Q</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>This is SiCOHCure</TD><TD>SiCOHCure</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>PTEOS is this one</TD><TD>PTEOS</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Put OMCTS in middle</TD><TD>OMCTS</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>Q3</TD><TD>=LOOKUP(2^15,SEARCH(" " &'Process List'!$A$2:$A$14 & " "," " & P3 & " "),'Process List'!$A$2:$A$14)</TD></TR><TR><TD>Q4</TD><TD>=LOOKUP(2^15,SEARCH(" " &'Process List'!$A$2:$A$14 & " "," " & P4 & " "),'Process List'!$A$2:$A$14)</TD></TR><TR><TD>Q5</TD><TD>=LOOKUP(2^15,SEARCH(" " &'Process List'!$A$2:$A$14 & " "," " & P5 & " "),'Process List'!$A$2:$A$14)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Process List

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid">A</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>EHARP</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>PTEOS</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>OMCTS</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>NBLOK</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>FTEOS</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>UVN</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>NIT</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: left">SiCOHCure</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: left">SiCOH</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>SILHS</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>LTO</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD>TEOS</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>OZONE</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[nycard64]

Cell in P looks like this "INSFEOL/STI/AMAT/JLD2.EHARP20LPREV4.0" I have the formula in same sheet, Column A. I was trying to use me excel jeanie with no luck. sorry for that. I am sure this is something simple I am just missing, thanks for your help.

 

[Jonmo1]

OK, I see the problem...
And the resolution presents another problem...

Try
=LOOKUP(2^15,SEARCH('Process List'!$A$2:$A$14,P3),'Process List'!$A$2:$A$14)

This works, but an occasional problem is now present..

If your string contains say SiCOHCure, it will not distinguish that from SiCOH
So it will return SiCOH

Same problem with TEOS, FTEOS and PTEOS


To resolve, put them in order so the LONGER phrase is last in the list on A2:A14
As such...


Process List

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><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 style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid">A</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>EHARP</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000">TEOS</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>OMCTS</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>NBLOK</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000">FTEOS</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>UVN</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>NIT</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000">SiCOH</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000">SiCOHCure</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD>SILHS</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>LTO</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-WEIGHT: bold; COLOR: #ff0000">PTEOS</TD></TR><TR style="HEIGHT: 17px"><TD style="BORDER-RIGHT: #f0f0f0 3px solid; BORDER-TOP: #f0f0f0 3px solid; BORDER-LEFT: #f0f0f0 3px solid; BORDER-BOTTOM: #f0f0f0 3px solid; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD>OZONE</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[T. Valko]

Cell in P looks like this "INSFEOL/STI/AMAT/JLD2.EHARP20LPREV4.0" I have the formula in same sheet, Column A. I was trying to use me excel jeanie with no luck. sorry for that. I am sure this is something simple I am just missing, thanks for your help.

jonmo1's formula is looking for the keyword with spaces on either side of it.

In your sample string the keyword doesn't have any spaces around it.

Try this tweaked version of the formula:

=LOOKUP(2^15,SEARCH('Process List'!$A$2:$A$14,P3),'Process List'!$A$2:$A$14)

 

[Jonmo1]

jonmo1's formula is looking for the keyword with spaces on either side of it.

In your sample string the keyword doesn't have any spaces around it.

Try this tweaked version of the formula:

=LOOKUP(2^15,SEARCH('Process List'!$A$2:$A$14,P3),'Process List'!$A$2:$A$14)

Yes, the spaces around the keyword were an attempt to resolve the above described problem..

 

[nycard64]

That did the trick thanks so much. This is a large project I am sure I will be on here again asking something else.

Have a great day....