VLOOKUP IF AND Nested Statement Help

[TheCobbler]

Hi, I'm trying to nest all of these statements together without much luck. Could somebody help? Thanks in advance. Cobb


=

IF(AND(J2="PHD",F2="CDs FP10/09"),(VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE))+0.25),

IF(AND(J2="PHD",F2="NEW09 Vinyl"),(VLOOKUP(H2,'LP BIN'!$A$2:$B$88,2,TRUE))+0.50),

IF(AND(J2="PRP",F2="DVDs FP10/09"),(VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+0.25)),

IF(AND J2="PRP",F2="Plastic Head Hoodies"),(VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE))+1.25),

IF(AND(J2="PRP",F2="Plastic Head T-Shirts"),(VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE))+1.25),

IF(F2="CD",(VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE),

IF(F2="LP",(VLOOKUP(H2,'LP BIN'!$A$2:$B$88,2,TRUE),

"MANUAL")

 

[Fluff]

Maybe

=IF(AND(J2="PHD",F2="CDs FP10/09"),VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+0.25,
IF(AND(J2="PHD",F2="NEW09 Vinyl"),VLOOKUP(H2,'LP BIN'!$A$2:$B$88,2,TRUE)+0.5,
IF(AND(J2="PRP",F2="DVDs FP10/09"),VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+0.25,
IF(AND(J2="PRP",F2="Plastic Head Hoodies"),VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+1.25,
IF(AND(J2="PRP",F2="Plastic Head T-Shirts"),VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+1.25,
IF(F2="CD",VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE),
IF(F2="LP",VLOOKUP(H2,'LP BIN'!$A$2:$B$88,2,TRUE),
"MANUAL")))))))

 

[Joe4]

Note that it also looks like you could reduce the levels of nesting down a bit by incorporating some OR statements since there are multiple scenarios that have the same end results.

For example, these two:

IF(AND(J2="PHD",F2="CDs FP10/09"),VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+0.25

and

IF(AND(J2="PRP",F2="DVDs FP10/09"),VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+0.25

both perform this step when TRUE:

VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+0.25

So, you could combine the two to this:

IF(OR(AND(J2="PHD",F2="CDs FP10/09"),AND(J2="PRP",F2="DVDs FP10/09")),VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+0.25

And you have another one that can be combined in this matter to (the two that both have:

VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+1.25

in the TRUE clause).

 

[TheCobbler]

Note that it also looks like you could reduce the levels of nesting down a bit by incorporating some OR statements since there are multiple scenarios that have the same end results.

For example, these two:

IF(AND(J2="PHD",F2="CDs FP10/09"),VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+0.25

and

IF(AND(J2="PRP",F2="DVDs FP10/09"),VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+0.25

both perform this step when TRUE:

VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+0.25

So, you could combine the two to this:

IF(OR(AND(J2="PHD",F2="CDs FP10/09"),AND(J2="PRP",F2="DVDs FP10/09")),VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+0.25

And you have another one that can be combined in this matter to (the two that both have:

VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+1.25

in the TRUE clause).

Thanks Joe. I'll definitely investigate this. Makes complete sense once I see it typed up!

 

[TheCobbler]

Maybe

=IF(AND(J2="PHD",F2="CDs FP10/09"),VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+0.25,
IF(AND(J2="PHD",F2="NEW09 Vinyl"),VLOOKUP(H2,'LP BIN'!$A$2:$B$88,2,TRUE)+0.5,
IF(AND(J2="PRP",F2="DVDs FP10/09"),VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+0.25,
IF(AND(J2="PRP",F2="Plastic Head Hoodies"),VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+1.25,
IF(AND(J2="PRP",F2="Plastic Head T-Shirts"),VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE)+1.25,
IF(F2="CD",VLOOKUP(H2,'CD BIN'!$A$2:$B$52,2,TRUE),
IF(F2="LP",VLOOKUP(H2,'LP BIN'!$A$2:$B$88,2,TRUE),
"MANUAL")))))))

Perfect - Thanks for your assistance! I'll compare this to my original code and figure out the differences.

 

[Fluff]

Glad we could help & thanks for the feedback.