asked on

I need some help re: table lookup best way in Excel

Hello Experts

I am in a bit of a quandary. I have to produce a spreadsheet with the following output:

Name | Details (the details can be unique to provide lookup ref) | Actual Cost | Cumulative | Budget Cost | Cumulative Budget |
The above is repeated in a pattern 4 week, 4 week, 5 week thus creating a quarterly result
It is then repeated through to the end of the year (ending 31.03.2011)

Because the source data will be store in two separate worksheets under ACTUAL and BUDGET, it is a onerous task developing the look-up references. Is it possible to do it any other way which will simply the Function.

I attach a spreadsheet blank for reference.
Thanks in advance

Tezza

101010-Actual-Budget-01.xls

patrickab

Tezza,

I believe that if the data in Actual and Budget worksheets had a row for each of weeks and periods the whole summarising calculations could be done very simply and without VLOOKUPs by using SUMPRODUCT().

If you would like to change those 2 worksheets and also show the layout that is needed for the result then I reckon it could be quick to product the results.

Patrick

tezza73

ASKER

Hi Patrick

Could you show me on the spreadsheet please Patrick?

Thanks in advance.

Tezza

ASKER CERTIFIED SOLUTION

patrickab

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tezza73

ASKER

I would like to explore more of the functions in EXCEL such as INDEX, TABLE Etc. Thanks for your help Patrick.