nested if statement problem

[monsierexcel]

Hello all, i cant find why this doesnt work

=IF(VLOOKUP(C2,'cost prices'!$B:$J,9,0),VLOOKUP(C2,'cost prices'!$B:$J,9,0),IF(VLOOKUP(C2,'cost prices'!$B:$I,8,0),VLOOKUP(C2,'cost prices'!$B:$I,8,0),IF(VLOOKUP(C2,'cost prices'!$B:$H,7,0),VLOOKUP(C2,'cost prices'!$B:$H,7,0),IF(VLOOKUP(C2,'Kit Products Cost Prices'!$B:$J,9,0),VLOOKUP(C2,'Kit Products Cost Prices'!$B:$J,9,0),IF(VLOOKUP(C2,'Kit Products Cost Prices'!$B:$I,8,0),VLOOKUP(C2,'Kit Products Cost Prices'!$B:$I,8,0),IF(VLOOKUP(C2,'Kit Products Cost Prices'!$B:$H,7,0),VLOOKUP(C2,'Kit Products Cost Prices'!$B:$H,7,0),VLOOKUP(C2,'Kit Pricing not in AP'!$A:$B,2,0)))))))

when i look at function arguments for the line:

IF(VLOOKUP(C2,'Kit Products Cost Prices'!$B:$H,7,0),VLOOKUP(C2,'Kit Products Cost Prices'!$B:$H,7,0),VLOOKUP(C2,'Kit Pricing not in AP'!$A:$B,2,0)

which shows its finding the correct value yet it still showing an NA error on return i cant figure this when the condition is showing the right value at 154.08!

I know you can have upto 7 nested IF statements but i only have 6 here.

thank you,

 

[Yongle]

If you are running a recent version of Excel function ISNA is available instead of ISERROR (use exactly the same syntax)

thanks for the feedback

 

[steve the fish]

Heres a way to do that:

=IFERROR(LOOKUP(2,1/(INDEX('cost prices'!H:J,MATCH(C2,'cost prices'!B:B,0),0)<>""),INDEX('cost prices'!H:J,MATCH(C2,'cost prices'!B:B,0),0)),IFERROR(LOOKUP(2,1/(INDEX('Kit Products Cost Prices'!H:J,MATCH(C2,'Kit Products Cost Prices'!B:B,0),0)<>""),INDEX('Kit Products Cost Prices'!H:J,MATCH(C2,'Kit Products Cost Prices'!B:B,0),0)),IFERROR(IF(VLOOKUP(C2,'Kit Pricing not in AP'!$A:$B,2,0)="","error",VLOOKUP(C2,'Kit Pricing not in AP'!$A:$B,2,0)),"error")))