Samantha has attended:
Excel VBA Introduction course
Constructing a complex lookup formula
Hi,
I have a workbook with 2 spreadsheets. One (sheet1) records itemised monthly expenses in a verticle table. Each month has 5 columns containing factors describing the expense: amount, category, var3, var4, var5. I have set up the spreadsheet so that row 2 = month (because there are 5 variables, A2-E2 = "Jan", F2-J2 = "Feb" etc.), row 3 = variable and then rows 4+ are the expenses recorded vertically.
The second spreadsheet (sheet2) is supposed to be a summary sheet, set up as a 2-way table. All the 'category' headings are listed in column A. Going across row 1 are the months. What I want to do is set up a formula that looks up the month in sheet2.row 1 and the category in sheet2.column A and sums all the cells in the 'amount' column for the relevant month and category.
I know I could do it just by giving a row number, or by using a different formula for each month in the summary sheet, but as I anticipate the workbook growing it would be far better if it was based on a lookup approach. Is a lookup of this structure possible?
Thanks.
RE: Constructing a complex lookup formula
Update: I have managed to make it work using the formula ={=IF(INDEX(Expenses!$A$1:$BM$50,3,MATCH(1,(Expenses!$A$1:$BM$1=Budget!F$2)*(Expenses!$A$2:$BM$2="Amount"),0))="","",SUMIF(INDEX(Expenses!$A$1:$BM$50,0,MATCH(1,(Expenses!$A$1:$BM$1=Budget!F$2)*(Expenses!$A$2:$BM$2="Item"),0)),$A19,(INDEX(Expenses!$A$1:$BM$50,0,MATCH(1,(Expenses!$A$1:$BM$1=Budget!F$2)*(Expenses!$A$2:$BM$2="Amount"),0)))))}
Where Expenses = sheet1 and Budget = sheet2
However, out of interest, is there a better way to do this? This seems very long winded and complex...
Thank you.
RE: Constructing a complex lookup formula
Hi Samantha,
Thank you for the forum question.
You give yourself a lot of unnecessary work. Excel likes flat lists. If your sheet1 was created as a flat list you can do your summary in few seconds (PivotTables). All tools in Excel is based on flat lists. You cannot filter sheet1, you cannot work with subtotals and look up information in sheet1 is very complicated as you have realised.
Flat lists can be created as a dynamic table which will save you a lot of time in the future. You will never need to update any tools in Excel if you have a flat list in sheet1, and click the Insert tab and in the Tables group click table.
Your idea is very impressive. Turning formulas and functions into array formulas is very powerful but also heavy for Excel to handle. Excel will have to calculate one row at the time. If you have large data sets and to many array formulas you will start getting problems.
Without analysing in depth what you want to achieve I am pretty sure the Sumproduct function can handle the job. SumProduct can do everything the Sumifs can do, it is an array function. You can work with both And & Or criteria. It multiply the correct ranges and it can do much more.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
