Array formulas making document huge. Another option?

[Jasesair]

I have a spreadsheet that features the below formula, and because there are so many array formulas, the workbook size is really blowing out. The formula works properly - it's just made the workbook really slow to do anything.

It's an index match formula, matching two criteria, as per below:
{=INDEX('Weekly Entry'!$C:$C,MATCH(1,($A4='Weekly Entry'!$B:$B)*(B$2='Weekly Entry'!$A:$A),0))}

If anyone can provide a fix for the formula so it doesn't have to be an array, I'd really appreciate it!

 

[hnsd24_CN]

You should try to avoid using A: A. you can use OFFSET to define dynamic regions

test.xlsm
	A	B	C	D
1			13	Array Formula
2		3	13	Array Formula
3			13	Normal Formula
4	C
Sheet2

Cell Formulas
Range	Formula
C1	C1	=INDEX(CC,MATCH(1,($A4=AA)*(B$2=BB),))
C2	C2	=INDEX(CC,MATCH($A4&B$2,AA&BB,))
C3	C3	=VLOOKUP($A4&B$2,IF({1,0},AA&BB,CC),2,)
Press CTRL+SHIFT+ENTER to enter array formulas.

sheet Weekly Entry

test.xlsm
	A	B	C
1	A	1	11
2	B	2	12
3	C	3	13
4	D	4	14
5	E	5	15
6	F	6	16
7	H	7	17
8	I	8	18
9	J	9	19
10	K	10	20
Weekly Entry

 

[hnsd24_CN]

I almost forgot that I used a Define Name to make the formula look shorter
AA

=OFFSET('Weekly Entry'!A1,,,COUNTA('Weekly Entry'!A:A))

BB

=OFFSET('Weekly Entry'!B1,,,COUNTA('Weekly Entry'!A:A))

CC

=OFFSET('Weekly Entry'!C1,,,COUNTA('Weekly Entry'!A:A))

 

[Fluff]

Another option

=INDEX('Weekly Entry'!$C2:$C1000,AGGREGATE(15,6,(ROW('Weekly Entry'!$C2:$C1000)-ROW('Weekly Entry'!$C2)+1)/($A4='Weekly Entry'!$B2:$B1000)/(B$2='Weekly Entry'!$A2:$A1000),1))

The biggest problem with your formula is that you are using whole column references. If you limit the range the formula looks at you will see a marked improvement in speed.

 

[RoryA]

Can you add a key column to the Weekly Entry sheet that concatenates A&B for each row?

 

[pgc01]

Is there only one match or can there be more?
In that case do you need the first, last?