Excel formula help! 2

[jfussell]

I think I'm missing a very simple step but I'm just not seeing it...

On Input sheet I have this:

Column B Column C
Row 13 | Combined Full Time |
Row 14 | Combined Part Time |
Row 15 | Total Combined | =SUM(C13:C14)
skip a few rows
Row 19 | EMR Full Time |
Row 20 | EMR Part Time |
Row 21 | Total EMR | =SUM(C19:C20)

Then on my main pricing sheet I'm attempting to use this formula...
=IF(Input!C21=0,(LOOKUP(Input!$C$21,{0,5,6,10,11,15},{40,40,30,30,25,25}))*Input!$C$21,(LOOKUP(Input!$C$15,{0,5,6,10,11,15},{40,40,30,30,25,25}))*Input!$C$15)

The criteria is this: there will only be one or the other, never 1 combined and 1 EMR, which is why I used the IF formula. So, if Input!C21 is zero, then pull the figure from Combined, because there will always be one or the other. However, it's not working as I thought it should and I've used this same formula in the past but without the IF. What am I doing wrong?

HELP!

 

[xlhelp]

If I am not mistaken, you want it as

=IF(Input!C21=0,(LOOKUP(Input!$C$15,{0,5,6,10,11,15},{40,40,30,30,25,25}))*Input!$C$15,(LOOKUP(Input!$C$21,{0,5,6,10,11,15},{40,40,30,30,25,25}))*Input!$C$21)

basically you have transposed 15 and 21

 

[jfussell]

Ah, you're right, thanks! However, when I plug in 1 under EMR Full Time it calculates the formula correctly, but if I plug 1 under Combined Full Time it doesn't give me 40. I'm still having problems with this formula somewhere...

 

[xlhelp]

Works fine for me. Only suggestion I have to make sure all cells are formatted properly.

 

[jfussell]

OMG the simple things that can reek havoc to a formula...

In my actual formula I had entered Input!C21="0" even though I wrote it correctly here...*sigh*

Thank you so much for your time xlhelp.

 

[firefytr]

Why not shorten that formula?? ...

=LOOKUP(Input!$C$15,{0,5,6,10,11,15},{40,40,30,30,25,25}))*IF(Input!C21=0,Input!$C$15,Input!$C$21)

-----------
Regards,
Zack Barresse

 

[jfussell]

WOW so simple! I tried that one and it worked great. Thank you very much!

 

[firefytr]

You're very welcome, glad to help!

-----------
Regards,
Zack Barresse