Index match to replace dynamic named range

[jcooooper]

Hi all,

I'm trying to replace a named range in my SUMPRODUCT calculation, in order to speed up my sheet.

I'm thinking it's some sort of combination of INDEX, MATCH and COUNTIF, but can't crack the code

If the below range is located in A1:B7, and I'm trying to return the in range in column A, if the text in column B is Equities, i.e A3:A6, what's the best way?


[TABLE="width: 258"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]GB9217[/TD]
[TD]Alternatives[/TD]
[/TR]
[TR]
[TD]LP8811[/TD]
[TD]Alternatives[/TD]
[/TR]
[TR]
[TD]FT_U0PX[/TD]
[TD]Equities[/TD]
[/TR]
[TR]
[TD]6005[/TD]
[TD]Equities[/TD]
[/TR]
[TR]
[TD]GB926[/TD]
[TD]Equities[/TD]
[/TR]
[TR]
[TD]36009[/TD]
[TD]Equities[/TD]
[/TR]
[TR]
[TD]134315[/TD]
[TD]Bonds
[/TD]
[/TR]
</tbody>[/TABLE]

Many thanks!

 

[Snakehips]

Not sure if this has potential to speed things up for you or not....

If confirmed Ctrl + Shift + Enter then E1 will return the range values.
Otherwise, G1 will return the range address for use with INDIRECT function

Excel 2010
	A	B	C	D	E	F	G
1	GB9217	Alternatives			FT_U0PX		A3:A7
2	LP8811	Alternatives
3	FT_U0PX	Equities
4	6005	Equities
5	GB926	Equities
6	36009	Equities
7	134315	Bonds
INFO

Cell Formulas
Range	Formula
G1	="A" & MATCH("Equities",B1:B7,0) & ":A" & MATCH("Equities",B1:B7,0) + COUNTIF(B1:B7,"Equities")
E1	{=INDEX(A1:A7,MATCH("Equities",B1:B7,0)):INDEX(A1:A7,MATCH("Equities",B1:B7,0)+COUNTIF(B1:B7,"Equities"))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[DanteAmor]

The sumproduct formula uses arrays as the array formulas. That makes the sheets slow.
If you are already having speed problems on your sheet, it may be best to change the formulas to macros.

 

[jcooooper]

Great, thanks both!

 

[DanteAmor]

Great, thanks both!

Youre welcome.