A
analystgirl
HELP! I have a pricing model in which I have 4 pull down lists in which the
user can select multiple combinations of criteria:
Pulldown list #1
Country
State
City
Zip
Pulldown list #2
Yes
No
Pulldown list #3
Hosted
GDS
Data File
Pulldown list #4
Under $1M
$1-$5M
Over $5M
I currently have pulldown lists #1 & #2 in the condition below but now need
to add pulldown lists #3 & #4 to the formula below without going over the 7
"if" limit. The catch is there is a discount % that needs to be added on to
the existing criteria from the vlookup table from pulldown list #1 & #2 if
the user selects "data file" and/or under $1M in list #3 and #4.
=IF(AND($D$8="Yes",$D$7="Country"),VLOOKUP(B$44,Tables!$B$10:$P$16,6),IF(AND($D$8="Yes",$D$7="State"),VLOOKUP(B$44,Tables!$B$10:$P$16,7),IF(AND($D$8="Yes",$D$7="City"),VLOOKUP(B$44,Tables!$B$10:$P$16,8),IF(AND($D$8="Yes",$D$7="Zip"),VLOOKUP(B$44,Tables!$B$10:$P$16,9),""))))
user can select multiple combinations of criteria:
Pulldown list #1
Country
State
City
Zip
Pulldown list #2
Yes
No
Pulldown list #3
Hosted
GDS
Data File
Pulldown list #4
Under $1M
$1-$5M
Over $5M
I currently have pulldown lists #1 & #2 in the condition below but now need
to add pulldown lists #3 & #4 to the formula below without going over the 7
"if" limit. The catch is there is a discount % that needs to be added on to
the existing criteria from the vlookup table from pulldown list #1 & #2 if
the user selects "data file" and/or under $1M in list #3 and #4.
=IF(AND($D$8="Yes",$D$7="Country"),VLOOKUP(B$44,Tables!$B$10:$P$16,6),IF(AND($D$8="Yes",$D$7="State"),VLOOKUP(B$44,Tables!$B$10:$P$16,7),IF(AND($D$8="Yes",$D$7="City"),VLOOKUP(B$44,Tables!$B$10:$P$16,8),IF(AND($D$8="Yes",$D$7="Zip"),VLOOKUP(B$44,Tables!$B$10:$P$16,9),""))))