If functions and possibly or

Hi, I am trying to create a formula that will look for a value "BL" in a cell and if it is not found, return a number for that cell.

If the cell is "BL" then the number is the lookup cell needs to be divided by a quantity (another cell) and then multiplied by a static number.

If the cell is not "BL" then a vlookup is done to pull back a number from another spreadsheet that corresponds to what the value is looking up.

so far, this is what I have in two cells and each work but I cannot figure out how to combine them into one function.

=IF(LEFT(D2,2)<>"BL",VLOOKUP(D2,'Master Resource Generic Rates'!A:C,3)*'106979Sep06Actuals Extract_TO11'!F2)


=IF(F2<>0,(G2/F2*2.44),IF(F2=0,G2))


I have tried using OR but am not that familiar with it and am at a roadblock.

Any help would be appreciate.

Originally Posted by facmess1

Hi, I am trying to create a formula that will look for a value "BL" in a cell and if it is not found, return a number for that cell.

If the cell is "BL" then the number is the lookup cell needs to be divided by a quantity (another cell) and then multiplied by a static number.

If the cell is not "BL" then a vlookup is done to pull back a number from another spreadsheet that corresponds to what the value is looking up.

so far, this is what I have in two cells and each work but I cannot figure out how to combine them into one function.

=IF(LEFT(D2,2)<>"BL",VLOOKUP(D2,'Master Resource Generic Rates'!A:C,3)*'106979Sep06Actuals Extract_TO11'!F2)


=IF(F2<>0,(G2/F2*2.44),IF(F2=0,G2))


I have tried using OR but am not that familiar with it and am at a roadblock.

Any help would be appreciate.

I'm assuming this:
=IF(LEFT(D2,2)<>"BL",VLOOKUP(D2,'Master Resource Generic Rates'!A:C,3)*'106979Sep06Actuals Extract_TO11'!F2)
is in G2?

then simply replacing G2 with that formula will work. i.e.

=IF(F2<>0,(IF(LEFT(D2,2)<>"BL",VLOOKUP(D2,'Master Resource Generic Rates'!A:C,3)*'106979Sep06Actuals Extract_TO11'!F2)/F2*2.44),IF(F2=0,IF(LEFT(D2,2)<>"BL",VLOOKUP(D2,'Master Resource Generic Rates'!A:C,3)*'106979Sep06Actuals Extract_TO11'!F2)))

sorry if i misunderstood. If I did can you post a bit of your worksheet so I can see exactly what you mean.

I have attached the instances that need to be considered. Please let me know if this is helpful.

Thanks again

Originally Posted by facmess1

I have attached the instances that need to be considered. Please let me know if this is helpful.

Thanks again

alright. i'm still lost.
you want columns L and M combined into one column? in which instances do you want L to be the answer and when do you want M?

This is what I am trying to accomplish.

I want to create a pivot table that provides the sum total costs for each WBS ID. In order to do this I need to create the formulas to populate the numbers in columns L & M and if possible, one column only. Here is the answer to your question.


I want L to be the answer is the following conditions are met: Column D starts with "BL". If that’s the situation then I want column G divided by column F and I want that result to be multiplied by 2.44. The one condition I need to also add is that if Column F is equal to 0 then I need the value in column G to be returned.

I want M to be the answer when Column D does not start with "BL" In addition, when it does not start with "BL" but one of the following ("IL", "BS". Or "IS") I need to do a vlookup that matches the string in another worksheet. Then, multiply that value by column F. This would result in the total cost. If Column D is ODC or TRV I need the value of Column G returned.

Hopefully this will make more sense.

If possible, I would like to get this all in one formula.

Originally Posted by facmess1

This is what I am trying to accomplish.

I want to create a pivot table that provides the sum total costs for each WBS ID. In order to do this I need to create the formulas to populate the numbers in columns L & M and if possible, one column only. Here is the answer to your question.


I want L to be the answer is the following conditions are met: Column D starts with "BL". If that’s the situation then I want column G divided by column F and I want that result to be multiplied by 2.44. The one condition I need to also add is that if Column F is equal to 0 then I need the value in column G to be returned.

I want M to be the answer when Column D does not start with "BL" In addition, when it does not start with "BL" but one of the following ("IL", "BS". Or "IS") I need to do a vlookup that matches the string in another worksheet. Then, multiply that value by column F. This would result in the total cost. If Column D is ODC or TRV I need the value of Column G returned.

Hopefully this will make more sense.

If possible, I would like to get this all in one formula.

think i got it now. thanks for the clarification

I put in a column N doing what I think you're trying to do.
N2 has the vlookup formula in it but I think it's going to have to be re-adjuted by you as it's defaulting to my winzip temp folder.
n3,n4,n5 use columns L and M but i left them there as a simplified version of the formula in N2 that is more legible.

You might want to look into using named ranges for your lookups, it cleans up the formula a bit and makes for easier troubleshooting

hope this works

Thanks again for your help. I will definately look into using named ranges in the future. I am just beginning to figure these functions out so I am sure this will not be my last pos.