Sumproduct or better quicker formula

[dariushou]

Hello there,

I'm trying to figure out the best (the quickest for calculations) formula to use for my data set. My data is laid out in the following way:

I have about 30 sections in one worksheet. Each section has 6 scenarios. For example, the first section in the below example is "Average Balance Factor" and it has 6 scenarios accross columns B thru G. The data goes down about 600 rows--each row represents a month so "1" would equal month 1 and so on.

I have a model in another worksheet within the same workbook that looksup the relevant period and scenario that i'm using and uses that data in a calculation. I have a macro that goes through a loop of different loans and each loan could use a different scenario (depending on what scenario i've given to such loan).

I know that i can use the following two formulas (for the "Average Balance Factor as an example")

SUMPRODUCT((Data!$B$1:$G$1=Model!$B$13)*(Data!$A$6:$A$15=Model!$A26),Data!$B$6:$G$15)

With B13 being the scenario for that loan (B13 is populated by the macro)
A26 is just the current period

CSE formula array works as well
SUM(IF((Data!$B$1:$G$1=Model!$B$13)*(Data!$A$6:$A$15=Model!$A26),Data!$B$6:$G$15))

Because the data goes out 600 rows and is now over 180 columns (30 sections with 6 scenarios each) is there a better way than the sumproduct approach. I'm looking for speed here. Thanks,

For your help.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Scenario	1	2	3	4	5	6	1	2	3	4	5	6	1	2	3	4	5	6
2
3
4	Period	Average Balance Factor						Penetration Percentage						Premium
5
6	1	100.00%	100.00%	100.00%	100.00%	100.00%	100.00%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
7	2	99.98%	99.98%	99.98%	99.98%	99.98%	99.98%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
8	3	99.60%	99.60%	99.60%	99.60%	99.60%	99.60%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
9	4	99.55%	99.55%	99.55%	99.55%	99.55%	99.55%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
10	5	99.35%	99.35%	99.35%	99.35%	99.35%	99.35%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
11	6	99.03%	99.03%	99.03%	99.03%	99.03%	99.03%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
12	7	98.83%	98.83%	98.83%	98.83%	98.83%	98.83%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
13	8	98.50%	98.50%	98.50%	98.50%	98.50%	98.50%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
14	9	97.89%	97.89%	97.89%	97.89%	97.89%	97.89%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
15	10	97.43%	97.43%	97.43%	97.43%	97.43%	97.43%	12.00%	12.00%	12.00%	12.00%	12.00%	12.00%	0.15%	0.15%	0.15%	0.15%	0.15%	0.15%
Data

 

[Fazza]

HiI,

If I understand correctly, this is a two way lookup. The Excel function for this is INDEX. So, something like below - untested. HTH, Fazza

=index(match(Model!$B$13,Data!$B$1:$G$1,0),match(Model!$A26,Data!$A$6:$A$15,0))

 

[dariushou]

Yes, two conditional lookup.

I couldn't get the index/match formula to work, but i did end up coming up with the below vlookup which is much faster than sumproduct. I just hate vlookups as if someone inserts a column then it throws everything off. Not that anyone should be inserting columns--i just like to make my spreadsheets dummy proof.

So for the 3rd section of data ("Premium"), this formula would do the lookup.

VLOOKUP(Model!$A26,Data!$A$6:$S$2380,Model!B$13+1+6*2,FALSE)

This works, but does anyone know of a formula as fast or close to it that doesn't use vlookup. Otherwise, i'll go with the vlookup.

Thanks Again!

 

[Fazza]

In the fresh of the morning it is obvious why the (untested) formula I posted above didn't work - I omitted the range reference! Should be

=index(Data!$B$6:$G$15,match(Model!$B$13,Data!$B$1:$G$1,0),match(Model!$A26,Data!$A$6:$A$15,0))

 

[dariushou]

I still can't get it to work. If i try to look up a value in the first section "Average Balance" under Scenario 1, i get "100%" for periods 1-6 and then "#Ref!" for each period thereafter. I think i'm entering in the formula right, but I'm not to familar with index/match formulas.

 

[Fazza]

Hi. Please post the formula from your worksheet. Thanks, Fazza

 

[pgc01]

Hi dariushou, Fazza

I believe you just have to switch the row and the column:

=index(Data!$B$6:$G$15,match(Model!$A26,Data!$A$6:$A$15,0),match(Model!$B$13,Data!$B$1:$G$1,0))

 

[dariushou]

=INDEX(Data!$B$6:$G$2380,MATCH(Model!$C$2,Data!$B$1:$G$1,0),MATCH(Model!A10,Data!$A$6:$A$2380,0))

I moved the reference:

C2 is the scenario and is equal to "1"
A10 is the period in the model and is equal to 7

The model screen has the period in column A with period one in cell A4 and the scenario is in cell C2

So the formula is in column B and i copied it down from cell B4 all the way to B629. For B4 thru B9 i get "100%" and then from B7 to the end i get "#REF!".

Thanks for your help.

 

[Fazza]

Sounds like a good idea, pgc!! I got that really wrong. Thanks

 

[dariushou]

Great. Thanks you two. That was the issue. I need to learn the index/match.