Use switch to count cost

[andrewb90]

Hi all,

I just need a little help using a switch formula, I've used it before, but now I can't seem to make it work.

I'm using a nested if...

=IF(C5='Item Pricing'!C3,'Item Pricing'!G3,IF(C5='Item Pricing'!C4,'Item Pricing'!G4,IF(C5='Item Pricing'!C5,'Item Pricing'!G5,"-")))*B5

Which works, but I have another ten rows to add, and I know that switch is easier to use. Any help would be greatly appreciated!

 

[FDibbins]

To the best of my knowledge, there is no "switch" function in excel formulas (if that is what you meant?).
Perhaps you mean something like this?

=sumif('Item Pricing'!$C$3:$C$100,$C$5,'Item Pricing'!$G$3:$G$5)*B5

 

[andrewb90]

That only returned a True/False result. The SWITCH formula is 2016 and newer for excel. I had a formula that did this on another file, but I lost it, and can't remember what I did to make the formula work.

 

[FDibbins]

That only returned a True/False result.

Then perhaps you entered it wrong, SUMIF will only return a value (sum of values or 0) or an error, not T/F

What do you get with just the SUMIF?
=sumif('Item Pricing'!$C$3:$C$100,$C$5,'Item Pricing'!$G$3:$G$5)

 

[Aladin Akyurek]

If you insist on an IF-like function...

=$B5*INDEX('Item Pricing'!$G$3:$G$25,MATCH($C5,'Item Pricing'!$C$3:$C$25,0))