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

 

[ckcabs]

You could have the name eg Tudor70x144 and use:

=INDIRECT(D15&E15)

as the Data Validation Source.

can you explain some more
where do I put the formulas above, how does it look up thecolour

if I name each size of linen with the type and the size then in the report tab jones order it shows as whitelinen whitelinen 54 x54 as I have concatenated them. which works for everything else

 

[Andrew Poulsom]

Sorry, I thought you were just talking about Data Validation. Isn't the formula for the price working for Tudor 70x144? I don't see any prices for colours.

 

[ckcabs]

Sorry, I thought you were just talking about Data Validation. Isn't the formula for the price working for Tudor 70x144? I don't see any prices for colours.

price is the same for each colour.

just seeing if there is a cleaner way to select the range of colours for each type of cloth

 

[Andrew Poulsom]

Possibly, but there doesn't appear to be much in common among the Types, Descriptions and Colours.