Use of VLOOKUP versus more efficient alternative
Posted on 2011-09-14
I am putting together a Sales and Cost fo Sales forecasting model and am currently using lots (thousands!!) of VLOOKUP to calculate various values.
Tables of data:
Volume - 120+ rows by 68 columns, indexed by Product Code - Dynamic Defined Range "Volume"
Selling Price - same dimensions as Volume, dynamic range "Prices"
Cost of Sales - 24000 rows (all products) by 22 columns, indexed by product code. Defined Range - COS
Tables of calculations, dimensions - 120 Rows x 68 Columns:
Sales - Volume * Selling Price = VLOOKUP(ProdCode,Volume,COLUMN(),False)*VLOOKUP(ProdCode,Prices,COLUMN(),False)
Cost of Sales - Volume * Cost/Kg = VLOOKUP(ProdCode,Volume,COLUMN(),False)*VLOOKUP(ProdCode,COS,n,False)
Cost of Sales is repeated on 3 sheets for Meat, Labour & Packaging
The file is already at 12Mb and is taking a noticeable (20-30secs) time to calculate.
I am using VLOOKUP because its possible that the order of the sheets could change and extra rows could be added so don't want to link the sheets purely on row number.
I need to add the following further calculation tables which will be the same dimensions as the others (120 x 68) and am looking to see if there is a more efficient formula that I can use eg INDEX/MATCH before this becomes an immense beast for our limited laptops (Windows XP, Excel 2003 on Dell Latitude E5400 with 2526Mhz processor and 2Gb of RAM)
Total Cost of Sales - Addition of Meat, Labour & Packaging sheets
Total Margin - Sales less Total Cost of Sales
Summaries of each of the calculation sheets, probably pivot tables.
I realise the Cost of Sales data sheet with 24k rows will be contributing to the file size and doing lookups on this will be contributing to the calculation time. Added complication is that this sheet has the ProdCode as text so having to allow for this in the other sheets.
Any suggestions??? I don't have MS Access so can't transfer it to Access.
I do have Lotus 97 and Approach 97 if that helps but don't have much experience with either. My colleagues that would be looking at it have even less.