Combining IF statements

[thewiseguy]

Hi all,

I am trying to combine these 3 IF statements but having some trouble: Any help on how to do this would be GREATLY appreciated!

=IF(O4="Direct",(XLOOKUP(B4,Tbl_Proposed_Fixtures[MaskedPartNumber],Tbl_Proposed_Fixtures[Cost_Direct],XLOOKUP(B4,Tbl_Proposed_Lamps[MaskedPartNumber],Tbl_Proposed_Lamps[Cost_Direct],XLOOKUP(B4,Tbl_Proposed_Retrofit_Kits[MaskedPartNumber],Tbl_Proposed_Retrofit_Kits[Cost_Direct],XLOOKUP(B4,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[Cost_Direct],XLOOKUP(B4,Tbl_Controls[MaskedPartNumber],Tbl_Controls[Cost_Direct],XLOOKUP(B4,Tbl_Materials[MaskedPartNumber],Tbl_Materials[Cost_Direct],XLOOKUP(B4,Tbl_Recycling[MaskedPartNumber],Tbl_Recycling[Cost_Direct],XLOOKUP(B4,Tbl_Rental[MaskedPartNumber],Tbl_Rental[Cost_Direct],XLOOKUP(B4,Tbl_Labor[MaskedPartNumber],Tbl_Labor[Cost_Direct]))))))))))

IF(O4="Distribution",(XLOOKUP(B4,Tbl_Proposed_Fixtures[MaskedPartNumber],Tbl_Proposed_Fixtures[Cost_Dist],XLOOKUP(B4,Tbl_Proposed_Lamps[MaskedPartNumber],Tbl_Proposed_Lamps[Cost_Dist],XLOOKUP(B4,Tbl_Proposed_Retrofit_Kits[MaskedPartNumber],Tbl_Proposed_Retrofit_Kits[Cost_Dist],XLOOKUP(B4,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[Cost_Dist],XLOOKUP(B4,Tbl_Controls[MaskedPartNumber],Tbl_Controls[Cost_Dist],XLOOKUP(B4,Tbl_Materials[MaskedPartNumber],Tbl_Materials[Cost_Dist],XLOOKUP(B4,Tbl_Recycling[MaskedPartNumber],Tbl_Recycling[Cost_Dist],XLOOKUP(B4,Tbl_Rental[MaskedPartNumber],Tbl_Rental[Cost_Dist],XLOOKUP(B4,Tbl_Labor[MaskedPartNumber],Tbl_Labor[Cost_Dist]))))))))))))


IF(O4="Override",P4,0)

 

[RobP]

Hi,

maybe the SWITCH() function could be easier for you :

SWITCH Function

The Excel SWITCH function compares one value against a list of values, and returns a result corresponding to the first match found. When no match is found, SWITCH can return an optional default value.

exceljet.net

So it would look something like :

SWITCH(O4,"Direct",[enter1st xlookup here],"Distribution",[2nd xlookup formula here], "Override",[3rd xlookup here],0)[/CODE



cheers
Rob

 

[thewiseguy]

Hi,

maybe the SWITCH() function could be easier for you :

SWITCH Function

The Excel SWITCH function compares one value against a list of values, and returns a result corresponding to the first match found. When no match is found, SWITCH can return an optional default value.

exceljet.net

So it would look something like :

SWITCH(O4,"Direct",[enter1st xlookup here],"Distribution",[2nd xlookup formula here], "Override",[3rd xlookup here],0)[/CODE



cheers
Rob

I can get this to work with the first 2 functions but the last one is giving me a problem. The 3rd statement is not an XLOOKUP - it just needs to reference a single cell (P4).

 

[RobP]

yep sorry, my bad - then just put P4 in place of the 3rd function.

(of course omit my [] square brackets on all .. just put functions between the , , commas..)

SWITCH(O4,"Direct",[enter1st xlookup here],"Distribution",[2nd xlookup formula here], "Override",P4,0)

Rob

 

[thewiseguy]

yep sorry, my bad - then just put P4 in place of the 3rd function.

(of course omit my [] square brackets on all .. just put functions between the , , commas..)

SWITCH(O4,"Direct",[enter1st xlookup here],"Distribution",[2nd xlookup formula here], "Override",P4,0)

Rob

can't seem to get this to work?


=SWITCH(O4,"DIRECT",(XLOOKUP(B4,Tbl_Proposed_Fixtures[MaskedPartNumber],Tbl_Proposed_Fixtures[Cost_Direct],XLOOKUP(B4,Tbl_Proposed_Lamps[MaskedPartNumber],Tbl_Proposed_Lamps[Cost_Direct],XLOOKUP(B4,Tbl_Proposed_Retrofit_Kits[MaskedPartNumber],Tbl_Proposed_Retrofit_Kits[Cost_Direct],XLOOKUP(B4,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[Cost_Direct],XLOOKUP(B4,Tbl_Controls[MaskedPartNumber],Tbl_Controls[Cost_Direct],XLOOKUP(B4,Tbl_Materials[MaskedPartNumber],Tbl_Materials[Cost_Direct],XLOOKUP(B4,Tbl_Recycling[MaskedPartNumber],Tbl_Recycling[Cost_Direct],XLOOKUP(B4,Tbl_Rental[MaskedPartNumber],Tbl_Rental[Cost_Direct],XLOOKUP(B4,Tbl_Labor[MaskedPartNumber],Tbl_Labor[Cost_Direct])))))))))),"DISTRIBUTION",(XLOOKUP(B4,Tbl_Proposed_Fixtures[MaskedPartNumber],Tbl_Proposed_Fixtures[Cost_Dist],XLOOKUP(B4,Tbl_Proposed_Lamps[MaskedPartNumber],Tbl_Proposed_Lamps[Cost_Dist],XLOOKUP(B4,Tbl_Proposed_Retrofit_Kits[MaskedPartNumber],Tbl_Proposed_Retrofit_Kits[Cost_Dist],XLOOKUP(B4,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[Cost_Dist],XLOOKUP(B4,Tbl_Controls[MaskedPartNumber],Tbl_Controls[Cost_Dist],XLOOKUP(B4,Tbl_Materials[MaskedPartNumber],Tbl_Materials[Cost_Dist],XLOOKUP(B4,Tbl_Recycling[MaskedPartNumber],Tbl_Recycling[Cost_Dist],XLOOKUP(B4,Tbl_Rental[MaskedPartNumber],Tbl_Rental[Cost_Dist],XLOOKUP(B4,Tbl_Labor[MaskedPartNumber],Tbl_Labor[Cost_Dist]))))))))))),"OVERRIDE",P4,0)

 

[RobP]

what kind of output does it give you ? (I note you have moved from "Direct" to "DIRECT" (uppercase) in your implementation - so I assume your O4 is all uppercase ?

Rob

 

[thewiseguy]

what kind of output does it give you ? (I note you have moved from "Direct" to "DIRECT" (uppercase) in your implementation - so I assume your O4 is all uppercase ?

Rob

#N/A Value Not Available

 

[Fluff]

How about

=IF(O4="DIRECT",XLOOKUP(B4,Tbl_Proposed_Fixtures[MaskedPartNumber],Tbl_Proposed_Fixtures[Cost_Direct],XLOOKUP(B4,Tbl_Proposed_Lamps[MaskedPartNumber],Tbl_Proposed_Lamps[Cost_Direct],XLOOKUP(B4,Tbl_Proposed_Retrofit_Kits[MaskedPartNumber],Tbl_Proposed_Retrofit_Kits[Cost_Direct],XLOOKUP(B4,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[Cost_Direct],XLOOKUP(B4,Tbl_Controls[MaskedPartNumber],Tbl_Controls[Cost_Direct],XLOOKUP(B4,Tbl_Materials[MaskedPartNumber],Tbl_Materials[Cost_Direct],XLOOKUP(B4,Tbl_Recycling[MaskedPartNumber],Tbl_Recycling[Cost_Direct],XLOOKUP(B4,Tbl_Rental[MaskedPartNumber],Tbl_Rental[Cost_Direct],XLOOKUP(B4,Tbl_Labor[MaskedPartNumber],Tbl_Labor[Cost_Direct]))))))))),IF(o4="DISTRIBUTION",XLOOKUP(B4,Tbl_Proposed_Fixtures[MaskedPartNumber],Tbl_Proposed_Fixtures[Cost_Dist],XLOOKUP(B4,Tbl_Proposed_Lamps[MaskedPartNumber],Tbl_Proposed_Lamps[Cost_Dist],XLOOKUP(B4,Tbl_Proposed_Retrofit_Kits[MaskedPartNumber],Tbl_Proposed_Retrofit_Kits[Cost_Dist],XLOOKUP(B4,Tbl_Accessory[MaskedPartNumber],Tbl_Accessory[Cost_Dist],XLOOKUP(B4,Tbl_Controls[MaskedPartNumber],Tbl_Controls[Cost_Dist],XLOOKUP(B4,Tbl_Materials[MaskedPartNumber],Tbl_Materials[Cost_Dist],XLOOKUP(B4,Tbl_Recycling[MaskedPartNumber],Tbl_Recycling[Cost_Dist],XLOOKUP(B4,Tbl_Rental[MaskedPartNumber],Tbl_Rental[Cost_Dist],XLOOKUP(B4,Tbl_Labor[MaskedPartNumber],Tbl_Labor[Cost_Dist]))))))))),IF(o4="OVERRIDE",P4,"")))

 

[RobP]

@Fluff, the override last option needs to be 0 rather than "" .. but you can fix that

I think at the end of the SWITCH statement the 0 might need to be "0" instead (eg inside inverted commas)
Rob

 

[thewiseguy]

excellent - thank you both!!