Contains Formula

[rtsripchikexcel]

IF(NOT(ISERROR(SEARCH("SD",O9))),"SD"

Im am currently using the above formula which works great. What i am trying to do and i cannot figure out is instead of hardcoding what i am looking for i am trying to find SD within a text string from a matrix i built and if true bring back SD. Can anyone help???

Thanks.

 

[Joe4]

It looks your formula may have got chopped off. You may want to re-try posting it again using the Code tags.

Also, could you post a small example of your data and expected results. I am not sure I fully understand your question.

 

[rtsripchikexcel]

=IF(NOT(ISERROR(SEARCH("SD",O9))),"SD",IF(NOT(ISERROR(SEARCH("MRC",O9))),"MRC",IF(NOT(ISERROR(SEARCH("MKG",O9))),"LJ",""))))

***The formula above is looking for a "word" within the data list in Tab 1.

The data set i have to work with is:

Tab 1: I have a list of data example below
<TABLE style="WIDTH: 93pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=124><COLGROUP><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 93pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=124>SCOTTE/SD</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>SCOTTRDE/SD</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>TC SCOTADE/SD</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>RDY2INV SD</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>SCOTTRADE/SD</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>TC ORDERS SD</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>MRC ORDERS CT</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>MRC ORDERS CT</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>TC ORDERS SD</TD></TR></TBODY></TABLE>


Tab 2: I have two rows...row 1 is what i am looking for the string to contain and row 2 is what i want to bring back if true

Row 1 Row 2
SD SD
MRC MRC
MKG LJ


My current formula i am hardcoding Tab 2 data into the formula and essentially having to do a imbedded if statement to run this. As i add additional items to the tab 2 list i am having to go back and change the formula and hardcoding additional data within formula. Is there a way i can use the above formula but instead of having to hardcode what i am looking for in tab 2 have the formula look for anything in row 1 and if true bring back data from row 2. I couldnt get this to work so i tried to simplify and just have formula look at row 1 in tab 2 and if true bring back text from row.

Thanks.

 

[Joe4]

If you wanted to place your hard-coded "SD" reference with the value in cell A2 on a sheet named "Tab 2", just replace:
"SD"
with
'Tab 2'!A2
in your formula.

You can do likewise for your other hard-coded values.

 

[rtsripchikexcel]

Joe4, I am trying to construct a formula that looks at the data in tab 2 and if it is found in tab 1 it brings back that value in tab one with out having to link it or hard code it.

Thanks.

 

[Joe4]

I don't know if I am quite clear where everything is. I thought that the formula you posted would be on Tab 2, looking up on to Tab 1. But your cell references are O9, which, since it has no sheet reference, would look up the range on the Tab that the formula is located on (not the other one).

Also, do you really want to search just one cell (O9), or a whole range of cells?

 

[Scott Huish]

I don't understand what you are trying to do, but your NOT(ISERROR could just be reduced to an ISNUMBER function:

=IF(ISNUMBER(SEARCH("SD",O9)),"SD",REST OF FORMULA HERE)

 

[rtsripchikexcel]

Hotpepper/Joe4 thanks for your help by the way. I think i am just not articulating my self well throught text. I am trying to essentially do a vlookup from one data set to another but looking for formula to find contains. The text string would be the unique value and the data set from tab 2 would be what im looking against. Hotpepper, thanks for shortening formula works great.