More help with OR

[PritiOne]

I got the last one - can you help me with this one?

=IF(OR(PromoPricing?="Crunch Time Promo", PromoPricing2?= "Crunch Time Promo"),IF(VLOOKUP(S19,ProductTable,20,0)="Y","MS 3000 Promotion",IF(OR(PromoPricing?="SN Unbundled",PromoPricing2?="SN Unbundled"),IF(ISNUMBER(MATCH(NewProdList,SNUnbundledList,0)),"SN Promo")),IF(W19>0,"A","")&IF(X19>0," B","")&IF(Y19>0," C","")&IF(Z19>0," D","")&IF(AA19>0," E","")&IF(AB19>0%," F","")))

 

[PritiOne]

PLEASE ...

 

[PritiOne]

I just can't figure it out - I can get a result if I have both PromoPricing? and promoPricing2? set as one of the values I have in the formul, but if the values are different (should go to default calculations) the answer is FALSE...????

Here's my formula:

=IF(OR(PromoPricing?="Crunch Time Promo",PromoPricing2?="Crunch Time Promo"),IF(VLOOKUP(S20,ProductTable,20,0)="Y","MS 3000 Promotion",IF(OR(PromoPricing?="SN Unbundled",PromoPricing2?="SN Unbundled"),IF(ISNUMBER(MATCH(NewProdList,SNUnbundledList,0)),"SN Promo"))),IF(W20>0,"A","")&IF(X20>0," B","")&IF(Y20>0," C","")&IF(Z20>0," D","")&IF(AA20>0," E","")&IF(AB20>0%," F",""))

 

[PritiOne]

PLEASE???...

 

[acw]

Hi

On the quick test I did, seems to be OK. I didn't have anything in the named ranges so it went to default. As I didn't have anything in the row 20 cells, it defaulted to blank. Tested this by changing the "" in the default output to "xx" in one of the outputs.


Tony

 

[Fergus]

Hi there,

You seem to have a problem after "MS 3000 Promotion" is the following IF intended to be the option if you don't find a "Y" in your look-up table or if neither PromoPricing? nor PromoPricing2? are equal to Crunch Time Promo.

As far as I can see the following formula will work except when you have no "Y" in your look-up table, for which you need to provide an option.

=IF(OR(PromoPricing?="Crunch Time Promo",PromoPricing2?="Crunch Time Promo"),IF(VLOOKUP(S20,ProductTable,20,0)="Y","MS 3000 Promotion"),IF(OR(PromoPricing?="SN Unbundled",PromoPricing2?="SN Unbundled"),IF(ISNA(MATCH(NewProdList,SNUnbundledList,0)),IF(W20>0,"A","")&IF(X20>0," B","")&IF(Y20>0," C","")&IF(Z20>0," D","")&IF(AA20>0," E","")&IF(AB20>0%," F",""),"SN Promo")))

 

[Fergus]

Furthermore, if both PromoPricing? and promopricing2? are blank the answer will be FALSE - is that what you want?

 

[PritiOne]

If both promos are blank then I need this logic to kick in:

IF(W20>0,"A","")&IF(X20>0," B","")&IF(Y20>0," C","")&IF(Z20>0," D","")&IF(AA20>0," E","")&IF(AB20>0%," F",""),"SN Promo")))


If only one promo is selected, it should result as the answer for the applicable line items.

 

[Fergus]

Hi again,

OK try this: =IF(OR(PromoPricing?="Crunch Time Promo",PromoPricing2?="Crunch Time Promo"),IF(VLOOKUP(S20,ProductTable,20,0)="Y","MS 3000 Promotion","No 'Y' entry in Product Table"),IF(OR(PromoPricing?="SN Unbundled",PromoPricing2?="SN Unbundled"),IF(ISNA(MATCH(NewProdList,SNUnbundledList,0)),"NewProdList No. not in SN Unbundled List","SN Promo"),IF(W20>0,"A","")&IF(X20>0," B","")&IF(Y20>0," C","")&IF(Z20>0," D","")&IF(AA20>0," E","")&IF(AB20>0%," F","")))

HTH

 

[PritiOne]

Thanks for your help!
MUCH APPRECITED!!!!!