Inserting a VLOOKUP Formula as Nested If into a Cell 2

[shah25]

Hello,

I am having problems with using a formula

=IF(C30 ="Tobacco","NotApplicable",IF(C30="Cri","Not Applicable",IF(C30="Peanuts","Not Applicable",'=VLOOKUP(C30,tblProductTypeCode,3))))

Which checks the products and if not Tobacco or Cri or Peanuts then Iwant to insert the vlookup formula into the Cell.


I don't know whether I have got the wrong number of brackets or indeed whether this is possible to do in a Nested If statement.

Please can anybody help

Thanks

 

[bluedragon2]

Lets shorten this and solve it

=IF(OR(C30="Tobacco",C30="Cri",C30="Peanuts&quot,"Not Applicable",VLOOKUP(C30,tblProductTypCode,3))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[Zathras]

blue, good response, but incomplete.

shah, two points:

1. You should specify a fourth argument for the VLOOKUP function as either 0 or FALSE. Otherwise you will get false hits (if the table is sequenced) or fail to get hits (if the table is random).

2. Why not just add three more lines to the table fo handle the three special cases with the text "Not Applicable" in the third column? Then all you need is a simple VLOOKUP formula without the nested IF.

 

[KenWright]

Agree with Zathras in that I would simply add those to the table. Just in case it is useful later though, it could have been shortened slightly further by losing all the C30 references bar one, and using with an array constant, eg:-

=IF(OR(C30={"Tobacco","Cri","Peanuts"}),"Not Applicable",VLOOKUP(C30,tblProductTypCode,3,0))

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[bluedragon2]

All good points...

Hey Ken, I broke down and tried to write my first FAQ and I covered SUMPRODUCT...Take a look:

faq68-4725



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...