Need IF Then Statement

[CWallace212]

Hi,

I am working to create a billing template, however i'm not sure how to enter multiple if then statements. For example, i need to take column Q and link to column E. In the excel sheet, the penetration is above 20% so it needs to take line Q6 so it can link to the total amount due in line F14. I also need an if then for A3 so if the date is in 2014, it should take L6.

[Speshul]

Not sure I follow exactly what you need, however here is how multiple IF's work

Nesting if statements works like this:
{ } are not part of the formula and there as describers of the line

=IF(test_statements,
{then}do this,
{else}IF(test2_statements,
{then}do this,
{else}IF(test3_statements,
{then}do this,
{else},do this)

So for example, the following logic:
If A1 > 10 then B2, but if A1 < 10 then C2, if neither then return D2
would be

=IF(A1>10,B2,IF(A1<10,C2,D2)

Note: If your test statement comes back TRUE it will use the first argument, FALSE will use the second. Either argument can be another IF statement with it's own test, and result arguments.


So
=IF(1=1, "Result A","Result B") will return "Result A", but
=IF(1=2, "Result A","Result B") will return "Result B".

[Arkadi]

Hi CWallace,

You said you need a formula for A3? I assume the formula will go somewhere else? If the formula is in A3 then it will be replaced by the date that gets entered. What if the year is not 2014, just return a blank value? If you put

=IF(YEAR(A3)=2014, L6,"")

in a cell it should return the L6 value...

[newdoverman]

From what I see, you are having a problem with the formula in E7. I found a problem with the construction of the first VLOOKUP. The column that you want to return a value from is 5 but that is outside the ARRAY that you have defined for the VLOOKUP. Column 1 is the column where the value is that is being looked up. Go through all your VLOOKUP statements and be sure that the column that you want to retrieve a value from is in the array defined for the VLOOKUP. Column P doesn't have values in it and if it is to remain empty, delete it. Taking a quick look at the rest of the formula, all the VLOOKUPs that have 5 as the column, are incorrect, being outside the ARRAY for the lookup. So redefine the ARRAY and adjust the column numbers that you want to retrieve data from.


Concerning A3 question: Where you want the answer enter this formula:

=VLOOKUP(A3,K5:L12,2,1)