Solved

Use of VLOOKUP versus more efficient alternative

Posted on 2011-09-14
5
383 Views
Last Modified: 2013-11-05
All,

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.

Thanks
Rob H
0
Comment
Question by:Rob Henson
  • 3
  • 2
5 Comments
 
LVL 11

Expert Comment

by:jkpieterse
ID: 36536492
If the lookup table is sorted on the lookup index, then you can use something like this:

=IF(INDEX(ListOfProdCodes,MATCH(ProdCode,ListOfProdCodes,1))=ProdCode,INDEX(COS,MATCH(ProdCode,ListOfProdCodes,1),ColumnNumberFromCOS),NA())

The trick being that you use the non-exact match function (last argument = 1), which is very fast, and then check whether it does return an exact match.
0
 
LVL 31

Author Comment

by:Rob Henson
ID: 36536520
Would just LOOKUP be more efficient as its not having to look at the whole array?

This would only look at one column and return the value from another column, the layout of all the sheets is the same. In case you were wondering the 68 columns are 18 months worth of weekly forecasting.

Thanks
Rob H
0
 
LVL 11

Accepted Solution

by:
jkpieterse earned 500 total points
ID: 36536625
Hmm, dunno really.
If there are lots of lookups, I'd have just one column using MATCH (using a similar -non-exact-match technique as I show above) to get the proper index from the lookup table and have all subsequent columns use the INDEX function which points to that one MATCH column for its index numbers.
0
 
LVL 31

Author Closing Comment

by:Rob Henson
ID: 36537097
jkpieterse

Combination of INDEX and MATCH works wonderfully!

Each sheet now has a couple of MATCH formulae on each row pulling the row number from the sheets where its needs to get its data from. Each individual calculation now uses an INDEX formula on named ranges for each sheet with the row number from the MATCH formulas.

File size still 13.4Mb but calculation time minimal!

Result!!
0
 
LVL 11

Expert Comment

by:jkpieterse
ID: 36538051
Great!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now