How do I write this IF statement in Excel?

Question

I am trying to build a complicated spreadsheet and I need a statement to do the following:

If C2=R2 and D2 is < T2 then U2, if D2 is >T but T3 but < T4 then U4 if D2 is > T4 but < T5 then U5, if D2 is > T5 but < T6 then U6 BUT if C2 does not equal R2 then S8

I think it needs to be some sort of IF statement, but i am tearing my hair out.

UPDATE:

Vicky's formula almost worked. I have jigged it a bit and it now looks like this.

=IF(C6=$R$3,IF(D6<=0.99,$U$2,IF(AND(D6>0.99,D6<=4.99),$U$3,IF(AND(D6>4.99,D6<=14.99),$U$4,IF(AND(D6>14.99,D3<=29.99),$U$5,IF(AND(D6>29.99,D6<99.99),$U$6,""))))),$S$8)

It all works fine until you change the value in cell D6 to say £45 when it still picks up the figure in cell U5. What do I need to tweak to fix that?

Answer 1

Yes, this would normally be expressed as an IF statement.

You could write this as one long complicated set of nested IF statements in your spreadsheet or you could write it in VBA. The VBA option would be easier to comprehend and therefore easier to maintain, but it would be a slightly steeper learning curve.

Answer 2

Here you go:

=IF(C2=R2, IF(D2<T2, U2, IF(AND(D2>T3, D2<T4), U4, IF(AND(D2>T4, D2<T5), U5, IF(AND(D2>T5, D2<T6), U6, "")))), S8)

Note that I am assuming that "if D2 is >T but T3 but < T4" is a typo for "if D2 is >T3 but < T4" and that if C2=R2 but D2 is not in any of the other ranges you just want the cell left blank.

Answer 3

What you're after is a VLOOKUP, combined with an IF

=IF(C2=R2, VLOOKUP(D2, T2:U6, 2, TRUE), S8)

This says:

• If C2 = R2

  • find the first row in T2:U6 where the value in the first column is greater than D2
  • return the value in the second column from that row

• otherwise return S8.

Which is equivalent to your initial criteria.

Answer 4

IF(AND(D6>14.99,D3<=29.99)

you've got a reference to D3 here not D6.