more then 64 nested if's!! please help

[ckcabs]

Hi

I am doing a spreadsheet to find a product from multiple tables and return the price. The problem Is there is 86 different product tables., over 64 ifs it says "the specified formula cannot be entered because it has more then 64 levels of nesting
how do I get a round this? I would appreciate your help

IFERROR(VLOOKUP($E15,PLAINWHITEtable,2,0),IFERROR(VLOOKUP($E15,WHITEGOLDtable,2,0),IFERROR(VLOOKUP($E15,ROYALWHITEtable,2,0),IFERROR(VLOOKUP($E15,WEDGEWOODtable,2,0),IFERROR(VLOOKUP($E15,LEEDSCREAMWAREtable,2,0),IFERROR(VLOOKUP($E15,OROtable,2,0),IFERROR(VLOOKUP($E15,CANAPÉGLASStable,2,0),IFERROR(VLOOKUP($E15,CHARGERPLATEStable,2,0),IFERROR(VLOOKUP($E15,SPECIALITYtable,2,0),IFERROR(VLOOKUP($E15,PLATINUMBANDtable,2,0),IFERROR(VLOOKUP($E15,SPENCERtable,2,0),IFERROR(VLOOKUP($E15,OSAKAtable,2,0),IFERROR(VLOOKUP($E15,CELADONCRACKLEGLAZEtable,2,0),IFERROR(VLOOKUP($E15,CANAPÉCHINAtable,2,0),IFERROR(VLOOKUP($E15,SUMItable,2,0),IFERROR(VLOOKUP($E15,ORIENTALtable,2,0),IFERROR(VLOOKUP($E15,NINACAMPBELLtable,2,0),IFERROR(VLOOKUP($E15,PEWTERtable,2,0),IFERROR(VLOOKUP($E15,SOLIStable,2,0),IFERROR(VLOOKUP($E15,VIRTUtable,2,0),IFERROR(VLOOKUP($E15,table,2,0),IFERROR(VLOOKUP($E15,OLDENGLISHEPNStable,2,0),IFERROR(VLOOKUP($E15,KINGSEPNStable,2,0),IFERROR(VLOOKUP($E15,Athenatable,2,0),IFERROR(VLOOKUP($E15,Larchtable,2,0),IFERROR(VLOOKUP($E15,Ebonytable,2,0),IFERROR(VLOOKUP($E15,PEWTERCUTLERYtable,2,0),IFERROR(VLOOKUP($E15,SIAMBURNTSTEELtable,2,0),IFERROR(VLOOKUP($E15,MOTHEROFPEARLtable,2,0),IFERROR(VLOOKUP($E15,ELIPSEWEDGEWOODtable,2,0),IFERROR(VLOOKUP($E15,Atlantisepnstable,2,0),IFERROR(VLOOKUP($E15,Canapespastrytable,2,0),IFERROR(VLOOKUP($E15,SAVOIEtable,2,0),IFERROR(VLOOKUP($E15,PRIMEURtable,2,0),IFERROR(VLOOKUP($E15,IMPERIALtable,2,0),IFERROR(VLOOKUP($E15,SOMMELIERtable,2,0),IFERROR(VLOOKUP($E15,MONDIALtable,2,0),IFERROR(VLOOKUP($E15,VENETIANTWISTtable,2,0),IFERROR(VLOOKUP($E15,CHEVRONtable,2,0),IFERROR(VLOOKUP($E15,FLORENCEtable,2,0),IFERROR(VLOOKUP($E15,Regencytable,2,0),IFERROR(VLOOKUP($E15,FLORAtable,2,0),IFERROR(VLOOKUP($E15,JOHNROCHAtable,2,0),IFERROR(VLOOKUP($E15,RAFAELtable,2,0),IFERROR(VLOOKUP($E15,LUCCAtable,2,0),IFERROR(VLOOKUP($E15,BUBBLECOTTAGEtable,2,0),IFERROR(VLOOKUP($E15,HARLEQUINJUGStable,2,0),IFERROR(VLOOKUP($E15,HIGHLANDCRYSTALtable,2,0),IFERROR(VLOOKUP($E15,JASPERCONRANICEtable,2,0),IFERROR(VLOOKUP($E15,JASPERCONRANAURAtable,2,0),IFERROR(VLOOKUP($E15,JASPERCONRANSTRATAtable,2,0),IFERROR(VLOOKUP($E15,FLORENCEtable,2,0),IFERROR(VLOOKUP($E15,BAROQUEPLATINUMtable,2,0),IFERROR(VLOOKUP($E15,BAROQUEGOLDtable,2,0),IFERROR(VLOOKUP($E15,Tudortable,2,0),IFERROR(VLOOKUP($E15,VisaColourstable,2,0),IFERROR(VLOOKUP($E15,Monettable,2,0),IFERROR(VLOOKUP($E15,WhiteLinentable,2,0),IFERROR(VLOOKUP($E15,Damasktable,2,0),IFERROR(VLOOKUP($E15,LIGHTWEIGHTTRESTLESx30hightable,2,0),IFERROR(VLOOKUP($E15,HEAVYWEIGHTTRESTLESx30hightable,2,0),IFERROR(VLOOKUP($E15,HEAVYWEIGHTHITOPx36hightable,2,0),IFERROR(VLOOKUP($E15,DENDTABLEStable,2,0),IFERROR(VLOOKUP($E15,FOYERtable,2,0),IFERROR(VLOOKUP($E15,CHAIRStable,2,0),IFERROR(VLOOKUP($E15,Seatpadstable,2,0),IFERROR(VLOOKUP($E15,ROUNDTABLEStable,2,0),IFERROR(VLOOKUP($E15,OTHERROUNDTABLEStable,2,0),IFERROR(VLOOKUP($E15,SQUARETABLES30hightable,2,0),IFERROR(VLOOKUP($E15,ELECTRICALtabletable,2,0),IFERROR(VLOOKUP($E15,GAStable,2,0),IFERROR(VLOOKUP($E15,OTHERtable,2,0),IFERROR(VLOOKUP($E15,CHAFINGDISHEStable,2,0),IFERROR(VLOOKUP($E15,SILVEREPNStable,2,0),IFERROR(VLOOKUP($E15,LEFineDiningtable,2,0),IFERROR(VLOOKUP($E15,LEBespoketable,2,0),IFERROR(VLOOKUP($E15,LECorinthianGoldCutlerytable,2,0),IFERROR(VLOOKUP($E15,LEKarriMirrorCutlerytable,2,0),IFERROR(VLOOKUP($E15,LEMulberryMirrorCutlerytable,2,0),IFERROR(VLOOKUP($E15,LERoyalOakCutlerytable,2,0),IFERROR(VLOOKUP($E15,LEKostaBodaMinetable,2,0),IFERROR(VLOOKUP($E15,LEOrangeryGlasswaretable,2,0),IFERROR(VLOOKUP($E15,LEMarcJacobsDavidtable,2,0),IFERROR(VLOOKUP($E15,LEPuretable,2,0),IFERROR(VLOOKUP($E15,LEFurnitureSeatPadstable,2,0) ,"0.00")))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

 

[Andrew Poulsom]

Why don't you merge your tables into one? If you must retain them you can try this syntax:

=IFERROR(VLOOKUP($E15,PLAINWHITEtable,2,0),0)+IFERROR(VLOOKUP($E15,WHITEGOLDtable,2,0),0)+IFERROR(VLOOKUP($E15,ROYALWHITEtable, 2,0),0)

 

[ckcabs]

I don't want a huge long list for the user to select, I will try adding the "+" and let you know
thanks

 

[ckcabs]

I don't want a huge long list for the user to select, I will try adding the "+" and let you know
thanks

sorry, that doesn't work. still saying the same error message

 

[Andrew Poulsom]

Does this work for you?

=IFERROR(VLOOKUP($E15,PLAINWHITEtable,2,0),0)+IFERROR(VLOOKUP($E15,WHITEGOLDtable,2,0),0)+IFERROR(VLOOKUP($E15,ROYALWHITEtable, 2,0),0)+IFERROR(VLOOKUP($E15,WEDGEWOODtable,2,0),0)+IFERROR(VLOOKUP($E15,LEEDSCREAMWAREtable,2,0),0)+IFERROR(VLOOKUP($E15,OROtabl e,2,0),0)+IFERROR(VLOOKUP($E15,CANAPÉGLASStable,2,0),0)+IFERROR(VLOOKUP($E15,CHARGERPLATEStable,2,0),0)+IFERROR(VLOOKUP($E15,SPECIALITYtable,2,0),0)+IFERROR(VLOOKUP($E15,PLATINUMBANDtable,2,0),0)+IFERROR(VLOOKUP($E15,SPENCERtable,2,0),0)+IFERROR(VLOOKUP($E15,OSAKAtable,2,0),0)+IFERROR(VLOOKUP($E15,CELADONCRACKLEGLAZEtable,2,0),0)+IFERROR(VLOOKUP($E15,CANAPÉCHINAtable,2,0),0)+IFERROR(VLOOKUP($E15,SUMItable,2,0),0)+IFERROR(VLOOKUP($E15,ORIENTALtable,2,0),0)+IFERROR(VLOOKUP($E15,NINACAMPBELLtable,2,0),0)+IFERROR(VLOOKUP($E15,PEWTERtable,2,0),0)+IFERROR(VLOOKUP($E15,SOLIStable,2,0),0)+IFERROR(VLOOKUP($E15,VIRTUtable,2,0),0)+IFERROR(VLOOKUP($E15,table,2,0),0)+IFERROR(VLOOKUP($E15,OLDENGLISHEPNStable,2,0),0)+IFERROR(VLOOKUP($E15,KINGSEPNStable,2,0),0)+IFERROR(VLOOKUP($E15,Athenatable,2,0),0)+IFERROR(VLOOKUP($E15,Larchtable,2,0),0)+IFERROR(VLOOKUP($E15,Ebonytable,2,0),0)+IFERROR(VLOOKUP($E15,PEWTERCUTLERYtable,2,0),0)+IFERROR(VLOOKUP($E15,SIAMBURNTSTEELtable,2,0),0)+IFERROR(VLOOKUP($E15,MOTHEROFPEARLtable,2,0),0)+IFERROR(VLOOKUP($E15,ELIPSEWEDGEWOODtable,2,0),0)+IFERROR(VLOOKUP($E15,Atlantisepnstable,2,0),0)+IFERROR(VLOOKUP($E15,Canapespastrytable,2,0),0)+IFERROR(VLOOKUP($E15,SAVOIEtable,2,0),0)+IFERROR(VLOOKUP($E15,PRIMEURtable,2,0),0)+IFERROR(VLOOKUP($E15,IMPERIALtable,2, 0),0)+IFERROR(VLOOKUP($E15,SOMMELIERtable,2,0),0)+IFERROR(VLOOKUP($E15,MONDIALtable,2,0),0)+IFERROR(VLOOKUP($E15,VENETIANTWISTtable,2,0),0)+IFERROR(VLOOKUP($E15,CHEVRONtable,2,0),0)+IFERROR(VLOOKUP($E15,FLORENCEtable,2,0),0)+IFERROR(VLOOKUP($E15,Regencytable,2,0),0)+IFERROR(VLOOKUP($E15,FLORAtable,2,0),0)+IFERROR(VLOOKUP($E15,JOHNROCHAtable,2,0),0)+IFERROR(VLOOKUP($E15,RAFAELtable,2,0 ),0)+IFERROR(VLOOKUP($E15,LUCCAtable,2,0),0)+IFERROR(VLOOKUP($E15,BUBBLECOTTAGEtable,2,0),0)+IFERROR(VLOOKUP($E15,HARLEQUINJUGSta ble,2,0),0)+IFERROR(VLOOKUP($E15,HIGHLANDCRYSTALtable,2,0),0)+IFERROR(VLOOKUP($E15,JASPERCONRANICEtable,2,0),0)+IFERROR(VLOOKUP($E15,JASPERCONRANAURAtable,2,0),0)+IFERROR(VLOOKUP($E15,JASPERCONRANSTRATAtable,2,0),0)+IFERROR(VLOOKUP($E15,FLORENCEtable,2,0),0)+IFERROR(VLOOKUP($E15,BAROQUEPLATINUMtable,2,0),0)+IFERROR(VLOOKUP($E15,BAROQUEGOLDtable,2,0),0)+IFERROR(VLOOKUP($E15,Tudortable,2,0),0)+IFERROR(VLOOKUP($E15,VisaColourstable,2,0),0)+IFERROR(VLOOKUP($E15,Monettable,2,0),0)+IFERROR(VLOOKUP($E15,WhiteLinentable,2,0),0)+IFERROR(VLOOKUP($E15,Damasktable,2,0),0)+IFERROR(VLOOKUP($E15,LIGHTWEIGHTTRESTLESx30hightable,2,0),0)+IFERROR(VLOOKUP($E15,HEAVYWEIGHTTRESTLESx30hightable,2,0),0)+IFERROR(VLOOKUP($E15,HEAVYWEIGHTHITOPx36hightable,2,0),0)+IFERROR(VLOOKUP($E15,DENDTABLEStable,2,0),0)+IFERROR(VLOOKUP($E15,FOYERtable,2,0),0)+IFERROR(VLOOKUP($E15,CHAIRStable,2,0),0)+IFERROR(VLOOKUP($E15,Seatpadstable,2,0),0)+IFERROR(VLOOKUP($E15,ROUNDTABLEStable,2,0),0)+IFERROR(VLOOKUP($E15,OTHERROUNDTABLEStable,2,0),0)+IFERROR(VLOOKUP($E15,SQUARETABLES30hightable,2,0),0)+IFERROR(VLOOKUP($E15,ELECTRICALtabletable,2,0),0)+IFERROR(VLOOKUP($E15,GAStable,2,0),0)+IFERROR(VLOOKUP($E15,OTHERtable,2,0),0)+IFERROR(VLOOKUP($E15,CHAFINGDISHEStable,2,0),0)+IFERROR(VLOOKUP($E15,SILVEREPNStable,2,0),0)+IFERROR(VLOOKUP($E15,LEFineDiningtable,2,0),0)+IFERROR(VLOOKUP($E15,LEBespoketable,2,0),0)+IFERROR(VLOOKUP($E15,LECorinthianGoldCutlerytable,2,0),0)+IFERROR(VLOOKUP($E15,LEKarriMirrorCutlerytable,2,0),0)+IFERROR(VLOOKUP($E15,LEMulberryMirrorCutlerytable,2,0),0)+IFERROR(VLOOKUP($E15,LERoyalOakCutlerytable,2,0),0)+IFERROR(VLOOKUP($E15,LEKostaBodaMinetable,2,0),0)+IFERROR(VLOOKUP($E15,LEOrangeryGlasswaretable,2,0),0)+IFERROR(VLOOKUP($E15,LEMarcJacobsDavidtable,2,0),0)+IFERROR(VLOOKUP($E15,LEPuretable,2,0),0)+IFERROR(VLOOKUP($E15,LEFurnitureSeatPadstable,2,0),0)

 

[Andrew Poulsom]

By the way, when you say "I don't want a huge long list for the user to select", doesn't that mean that you know which lookup table to use?

 

[ckcabs]

yes
I am using dependent drop down lists to first select the "category then the "type" then the "list of products of that type", then it is looking up all of the product tables for the product description and returns the price. let me know if you want me to forward the spreadsheet to you. thank you I n advanced

 

[Andrew Poulsom]

So what's in the Source box for the dependent dropdown?

 

[ckcabs]

C15 = Category - list
Dropdown (Glassware,Crockery,Etc ) from a table of these category

D15=Dependent list based on c15 and have data range named as each category e.g.Crockery
This display a list of type of crockery e.g.plainwhite,whitegold, royal white

E15 dependent list based on d15 and have data range named as each type e.g.Plainwhite, then there is a list of different plates,cups etc

 

[ckcabs]

using indirect(c15) in the source box