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,

 

[steve the fish]

That will produce NA error if whatever is in C2 is not found in column B of cost prices, if it is found in cost prices and they are all blank or 0 then it will NA error if not found in Kit Products Cost Prices etc.

 

[Yongle]

Is this what you are wanting to achieve

=IFERROR(VLOOKUP(C2,'Kit Products Cost Prices'!$B:$H,7,0),IFERROR(VLOOKUP(C2,'Kit Pricing not in AP'!$A:$B,2,0),"NOT FOUND"))

 

[GR00007]

Where is the boolian argument in the if statement? VLOOKUP will return what it finds: 154.08 which is not true/false.

 

[steve the fish]

154.08 would produce the value if true.

 

[monsierexcel]

if i produce this section of the formula as a seperate formula....

=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))

it produces the correct value of 154.08
so i am unsure why its showing N/a as all vlookup statements are correct??

When i nest this statement in here:
=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)))))))

it fails?

 

[steve the fish]

Then its failing before that statement is reached. It may be better to say what you are attempting to do.

 

[monsierexcel]

i am trying to retrieve information from 3 possible columns first in column J, then if no value look in column I, then if no value look in column H in "cost prices", however if the value is not found i in those 3 columns, then must look in column J of "Kit products Cost price", if its not found in there it then needs to look in column B of "kit pricing not in AP"

 

[Yongle]

Have a look at post#3
The method should work for you

Start with
=IFERROR(VLOOKUP(C2,'Kit Pricing not in AP'!$A:$B,2,0),"NOT FOUND")
then
IFERROR(VLOOKUP(...),IFERROR(VLOOKUP(C2,'Kit Pricing not in AP'!$A:$B,2,0),"NOT FOUND"))
then
IFERROR(VLOOKUP(...),IFERROR(VLOOKUP(...),IFERROR(VLOOKUP(C2,'Kit Pricing not in AP'!$A:$B,2,0),"NOT FOUND")))
etc

 

[monsierexcel]

thank you, thats spot on!!