# Create a nesting formula w/ multiple conditions based on fields w/VLOOKUP

1. ## Create a nesting formula w/ multiple conditions based on fields w/VLOOKUP

I have an excel 2007 formula/macro that is far to complex for me to create. Here is what I am trying to do.

I have a workbook that has multiple sheet ties for auto filling. I want to look in column/field C66 and if (RD) is part of the text I want S66 to be 0 otherwise =R66. NOTE: the text that appear in the cells of column C are as a result of an automated population from the below VLOOKUP formula;

=IF(B65="","",VLOOKUP(B65,'Combined Price Master 1-12-2011'!A1:G3246,2,FALSE))
Is this achievable? I want to drag this formula down to Cell S79.  Register To Reply

2. ## Re: Complex Excel2007 Formula/Macro

Not sure I totally understand, but try this in S66 and copy down:

=IF(ISERROR(SEARCH("RD",C66)),R66,0)

HTH,
Jason  Register To Reply

3. ## Re: Create a nesting formula w/ multiple conditions based on fields w/VLOOKUP

I am making a sad attempt to build a complex formula or code I guess. Here is what I have:

Column C list a discription; the discription is a result of the below VLOOKUP code referencing a worksheet within the workbook:

=IF(B67="","",VLOOKUP(B67,'Combined Price Master 1-12-2011'!A3:G3248,2,FALSE))

Some of my descriptions include a ** which denotes a product type, see below example:

Bridge Unit BU3020 **

I have additional columns to the right that are calculating cost. My ultimate goal is to have any product with the ** roll up as a total dollar amount to the first line item in a column on the far right. So in column R I would like a formula that goes back to column C, looks for the **, if the ** is there enters the dollar amount listed in column Q and if the ** is not listed in the description enters 0.

Because this seems to be too many conditions I decided to build the final result one column at a time. In Column S I will take the sum of R65-R79. All of the other rows in S will equal the adjacent cell in R, in the end resulting in \$0 listed for products in the same row that contain a ** and the actual dollar of all other products. The products with the ** will be part of the combined total of in S65 based on sum of R65-R79.

I believe my formula hang up has to do with the fact that the original reference fields C66-C79 are the result of a formula, thus not true text or value, am I correct??? Can I get around this??  Register To Reply

4. ## Re: Complex Excel2007 Formula/Macro Originally Posted by jasoncw Not sure I totally understand, but try this in S66 and copy down:

=IF(ISERROR(SEARCH("RD",C66)),R66,0)

HTH,
Jason
Thanks Jasoncw, but that did not work. Did you by chance see my last explination?  Register To Reply

5. ## Re: Create a nesting formula w/ multiple conditions based on fields w/VLOOKUP

jasoncw is not regularly online, so perhaps I can try to help you...

If you are summing values in a column based on another column have text with ** at the end, then you can do it with one formula (Sumif).

e.g.

=SUMIF(C66:C79,"*~*~*",Q66:Q79)

Does that work or do you still want to do it on individual lines?  Register To Reply