IF Statement Assistance

[TheCobbler]

Hi, I have been working on this statement. Each of these line works individually. When I add them together the first three work, and only in this order. When the fourth is added I don't get the desired result. Is there anything obviously wrong? Is there a better way to achieve the same effect? Thanks as always, Cobb

=
IF(OR(AND(ISNUMBER(SEARCH("UNI",K2,1)),F2="DVDs FP10/09",ISNUMBER(XMATCH(M2,Universal!$F:$F,0))),AND(ISNUMBER(SEARCH("UNI",K2,1)),F2="BLU-RAY")),G2*0.8,
IF(AND(ISNUMBER(SEARCH("UNI",K2,1)),F2="CDs FP10/09",ISNUMBER(XMATCH(M2,Universal!$F:$F,0))),VLOOKUP(G2,Universal!$C$3:$D$19,2,FALSE),
IF(AND(ISNUMBER(SEARCH("UNI",K2,1)),F2="NEW09 Vinyl",VLOOKUP(G2,Universal!$E$2:$E$11,1,FALSE)),G2,
IF(ISNUMBER(SEARCH("UNI",K2,1)),G2*0.85,
"MANUAL"
))))

 

[Fluff]

Why have you got this part VLOOKUP(G2,Universal!$E$2:$E$11,1,FALSE)) inside the AND, what is it meant to return?

 

[TheCobbler]

It was meant to check that a value existed in Range E2:E11. I guess a similar thing would be this:

=
IF(OR(AND(ISNUMBER(SEARCH("UNI",K2,1)),F2="DVDs FP10/09",ISNUMBER(XMATCH(M2,Universal!$F:$F,0))),AND(ISNUMBER(SEARCH("UNI",K2,1)),F2="BLU-RAY")),G2*0.8,
IF(AND(ISNUMBER(SEARCH("UNI",K2,1)),F2="CDs FP10/09",ISNUMBER(XMATCH(M2,Universal!$F:$F,0))),VLOOKUP(G2,Universal!$C$3:$D$19,2,FALSE),
IF(AND(ISNUMBER(SEARCH("UNI",K2,1)),F2="NEW09 Vinyl",ISNUMBER(XMATCH(G2,Universal!$E:$E,0))),G2,
IF(ISNUMBER(SEARCH("UNI",K2,1)),G2*0.85,
"MANUAL"
))))

 

[Fluff]

Does that work?

 

[TheCobbler]

It's close. I'm getting an #N/A Error for this line (I think):
IF(AND(ISNUMBER(SEARCH("UNI",K2,1)),F2="CDs FP10/09",ISNUMBER(MATCH(M2,Universal!$F:$F,0))),VLOOKUP(G2,Universal!$C$3:$D$19,2,FALSE),

I would expect if there is no match for M2 then it would return G2*0.85 as it should match the fourth line.

 

[Fluff]

If you are getting #N/A the the value in G2 was not found in Universal!$C$3:$C$19

 

[TheCobbler]

Ah! I see. That means it stops at that line rather than looking up that cell in another line. I've decided to handle the error... Thank you so much!

= IF(OR(AND(ISNUMBER(SEARCH("UNI",K2,1)),F2="DVDs FP10/09",ISNUMBER(MATCH(M2,Universal!$F:$F,0))),AND(ISNUMBER(SEARCH("UNI",K2,1)),F2="BLU-RAY")),G2*0.8, IF(AND(ISNUMBER(SEARCH("UNI",K2,1)),F2="CDs FP10/09",ISNUMBER(MATCH(M2,Universal!$F:$F,0))),IFNA(VLOOKUP(G2,Universal!$C$3:$D$19,2,FALSE),G2*0.85), IF(AND(ISNUMBER(SEARCH("UNI",K2,1)),F2="NEW09 Vinyl",ISNUMBER(MATCH(G2,Universal!$E:$E,0))),G2, IF(ISNUMBER(SEARCH("UNI",K2,1)),G2*0.85, "MANUAL" ))))

 

[Fluff]

Glad to help & thanks for the feedback.