Constructing a complex lookup formula

Resolved · Medium Priority · Version 2010

Wed 31 Jan 2018, 08:56 Delegate Samantha said...

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.

Wed 31 Jan 2018, 10:33 Delegate Samantha said...

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.

Thu 1 Feb 2018, 21:04 Edited on Thu 1 Feb 2018, 21:05 Trainer Jens said...

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Missing Field handle

If your field handle goes missing all you need to do is go to tools > options > edit tab and then make sure that the check boxes for paste and insert buttons are checked.

View all Excel hints and tips