Find and search help

[Bruins11]

I have about 3000 lines of data and need a high level formula to help me "find" a single value(word) in a cell. Then return that value in a corresponding cell. I am using this formula =IF(ISNUMBER(SEARCH("X", A2)),"X","NA")
IE:
<TABLE style="WIDTH: 328pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=436 border=0 x:str><COLGROUP><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 9301" span=2 width=218><TBODY><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl20 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 164pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" width=218 height=22><TABLE style="WIDTH: 328pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=436 border=0 x:str><COLGROUP><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 9301" span=2 width=218><TBODY><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 164pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" width=218 height=22>x t v w q s p</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 164pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=218 x:fmla='=IF(ISNUMBER(SEARCH("X", A1)),"X","NA")'>X</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>a l e</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:fmla='=IF(ISNUMBER(SEARCH("X", A2)),"X","NA")'>NA</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>x t w q</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:fmla='=IF(ISNUMBER(SEARCH("X", A3)),"X","NA")'>X</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>q a s o</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:fmla='=IF(ISNUMBER(SEARCH("X", A4)),"X","NA")'>NA

</TD></TR></TBODY></TABLE>My dilemma is I also need to pull out another value at the same time. So I need a formula that will search one data column for two values, lets say "X" and "A", and if not found return "NA". Any help is much appreciated!</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 164pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=218 x:fmla='=IF(ISNUMBER(SEARCH("X", A1)),"X","NA")'></TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl20 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:fmla='=IF(ISNUMBER(SEARCH("X", A2)),"X","NA")'></TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl20 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:fmla='=IF(ISNUMBER(SEARCH("X", A3)),"X","NA")'></TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl20 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22></TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" x:fmla='=IF(ISNUMBER(SEARCH("X", A4)),"X","NA")'></TD></TR></TBODY></TABLE>

 

[matttclark]

If all you are trying to do is add a second value to your formula where both have to be present (in your note you wanted "X" and "A" although none of your sample values had both of these) then this moght do the job:

=IF(AND(ISNUMBER(SEARCH("x", A2)),ISNUMBER(SEARCH("a", A2))),"X","NA")

If you want either value this might help:
=IF(OR(ISNUMBER(SEARCH("x", A2)),ISNUMBER(SEARCH("a", A2))),"X","NA")

If this is not what you wanted please provide some addtional details

 

[Bruins11]

The values will never be present together in the same cell. As my example provides, the contents of the cell will have either "X" or "A" (but not both),and if neither "X" or "A" is present, I want it to return "NA". Any ideas on what needs to be altered in my formula to pull this data?

 

[Aladin Akyurek]

I have about 3000 lines of data and need a high level formula to help me "find" a single value(word) in a cell. Then return that value in a corresponding cell. I am using this formula =IF(ISNUMBER(SEARCH("X", A2)),"X","NA")
IE:
<TABLE style="WIDTH: 328pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=436 x:str><COLGROUP><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 9301" span=2 width=218><TBODY><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 164pt; HEIGHT: 16.5pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl20 height=22 width=218><TABLE style="WIDTH: 328pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=436 x:str><COLGROUP><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 9301" span=2 width=218><TBODY><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 164pt; HEIGHT: 16.5pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl22 height=22 width=218>x t v w q s p</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 164pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=218 x:fmla='=IF(ISNUMBER(SEARCH("X", A1)),"X","NA")'>X</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl22 height=22>a l e</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" x:fmla='=IF(ISNUMBER(SEARCH("X", A2)),"X","NA")'>NA</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl22 height=22>x t w q</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" x:fmla='=IF(ISNUMBER(SEARCH("X", A3)),"X","NA")'>X</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl22 height=22>q a s o</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" x:fmla='=IF(ISNUMBER(SEARCH("X", A4)),"X","NA")'>NA


</TD></TR></TBODY></TABLE>My dilemma is I also need to pull out another value at the same time. So I need a formula that will search one data column for two values, lets say "X" and "A", and if not found return "NA". Any help is much appreciated!</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 164pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=218 x:fmla='=IF(ISNUMBER(SEARCH("X", A1)),"X","NA")'></TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl20 height=22></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" x:fmla='=IF(ISNUMBER(SEARCH("X", A2)),"X","NA")'></TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl20 height=22></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" x:fmla='=IF(ISNUMBER(SEARCH("X", A3)),"X","NA")'></TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl20 height=22></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" x:fmla='=IF(ISNUMBER(SEARCH("X", A4)),"X","NA")'></TD></TR></TBODY></TABLE>

Try...

=LOOKUP(REPT("z",255),CHOOSE({1,2},"NA",
    LOOKUP(9.99999999999999E+307,SEARCH({"A","X"},A2),{"A","X"})))

 

[matttclark]

then use the second formaula, the one with "OR"

 

[Bruins11]

Thank you