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]

would it be quicker sends you the spreadsheet?

 

[Andrew Poulsom]

Does this formula work for you?

=VLOOKUP($E15,INDIRECT(C15),2,0)

 

[ckcabs]

how does that lookup all the tables?

 

[ckcabs]

returns #n/a

 

[Andrew Poulsom]

Do you want to put your workbook on a share like Box.com and post the URL?

 

[ckcabs]

yes please

https://app.box.com/s/7rvwwzevurek6vt5gzwe

 

[Andrew Poulsom]

In J15:

=VLOOKUP($E15,INDIRECT(D15&"table"),2,0)

 

[ckcabs]

That is amazing!!!!!!!! Thank you so much for your help. Appreciate all your time

 

[ckcabs]

one more thing I'm trying to do if add a colour to types of linen see product list in "jones" tab in coloumn AI

so they can select the type of linen in jones order
cateorgy = linen
type = tudor
desc =70x144 ( which is the size
colour this will drop down the list of colours available for that type of linen

the problem is for each type of linen the sizes are the same, so I have tried to add another character eg. in so it is different for each one so it can find the correct price. then I have named each range as that size to return the colours.

it is a little messy. is there an easier way to do this
thanks

 

[Andrew Poulsom]

You could have the name eg Tudor70x144 and use:

=INDIRECT(D15&E15)

as the Data Validation Source.