Index function with variable range reference

[dmcgoo]

Hi Guys

I have a sheet with data as shown in columns A:"H below. I also currently have a function as shown = index($d8:h11,match(b3,$b8:4B11,0)match($b2,D7:h7,0).
I now want to add in other Type Options B through D. How can I change the array $d8:$h11 so that it is based on whatever is selected in cell b4?

Many thanks

dmcgoo

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2	Personnel Type	B
3	Personnel ID	3
4	Type	B
5
6	Type			A					B					C					D
7	ID#			1	2	3	4	5	1	2	3	4	5	1	2	3	4	5	1	2	3	4	5
8	Transaction Ref	A		X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
9		B		X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
10		C		X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
11		D		X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X	X
Sheet1

 

[levy77]

Could you merge rows 6 and 7 together (will have to use the offset function) and base the index match off the merged rows?

 

[levy77]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD="width: 101"]Personnel Type[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]B
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD="width: 101"]Personnel ID[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD="width: 101"]Type[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD="width: 101"]Output[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]FORMULA HERE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD="width: 101"]Type[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD="width: 101"]ID#[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD="width: 101"]Type + ID#[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]A1[/TD]
[TD]A2[/TD]
[TD]A3[/TD]
[TD]A4[/TD]
[TD]A5[/TD]
[TD]B1[/TD]
[TD]B2[/TD]
[/TR]
[TR]
[TD][TABLE="width: 101"]
<tbody>[TR]
[TD="width: 101"]Transaction Ref[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


For formula to go in cells C7 onwards is =OFFSET(D5,,-D6+1)&D6.
Formula to go in cell B4 is =INDEX($D$8:$W$11,MATCH($B$1,$B$8:$B$11,0),MATCH($B$3&$B$2,D7:W7,0)).

Hope this helps.

 

[dmcgoo]

Thanks Levy77. Cheers.