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