Index/Match Array, Multiple Column Array

[agutts6]

Hey guys,

As a moderate noob where arrays are concerned, I could use some guidance here. Pretty sure this would fall under the index/match function, but if there's a simpler way to arrive at a solution, I'm of course all ears.

inning	out		-1	0	1
1	0		3.3	2.3	5.4
1	1		6.5	3.1	4.5
1	2		5.0	0.5	3.0
2	0		4.4	4.1	4.6
2	1		2.2	3.3	5.5

<tbody>
</tbody>

Let's say the column headings "-1, 0, 1" refer to a game score.

I know how to do an array function using the inning and out columns to return a value from the center (0) column. For example, if inning = 2 and out = 0, use array index/match to return column 0 value of 4.1.

But can I also use the columns as a criteria? As in, index columns -1 through 1, and match based on say, inning = 2 outs = 0 game score = -1?

I hope I'm explaining this well enough. Please let me know.

 

[Aladin Akyurek]

Let F2 equal 2 (an inning value), G2 0 (an out value), H2 1 (a column heading)...

I2, control+shift+enter, not just enter:

=INDEX(C2:E6,MATCH(1,IF(A2:A6=F2,IF(B2:B6=G2,1)),0),MATCH(H2,C1:E1,0))

 

[agutts6]

Thanks Aladdin.

Works the way you showed me. But I was demo-ing an abridged version there for simplicity. Here's my full version. It's sort of working, but it's off.

Inning	Outs	1B	-3	-2	-1	0	1	2	3	crit	#	form
1	0	0	0.3031	0.3786	0.4858	0.5898	0.7041	0.759	0.835	Innings	1	0.443
1	1	1	0.3324	0.426	0.5416	0.629	0.734	0.819	0.874	Outs	1
2	0	1	0.402	0.443	0.5632	0.6566	0.749	0.801	0.874	1B	1
3	2	1	0.398	0.534	0.601	0.6957	0.774	0.797	0.87	Runs	-2

<colgroup><col width="64" span="3" style="width:48pt"> <col width="64" style="width:48pt"> <col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>

Formula is =INDEX(D2:J5,MATCH(1,IF(A:A=L2,IF(B:B=L3,IF(C:C=L4,1))),0),MATCH(L5,D1:J1,0)) . That's returning .443, which is a little off. If I turn the inputs a little... innings 2, outs 0, 1b 1, runs -2.... same problem, returns .534.

Sorry for leaving out some details in the original, but I thought what worked for one wouldve worked for the other.

 

[Robert Mika]

Thanks Aladdin.

Works the way you showed me. But I was demo-ing an abridged version there for simplicity. Here's my full version. It's sort of working, but it's off.

Inning	Outs	1B	-3	-2	-1	0	1	2	3	crit	#	form
1	0	0	0.3031	0.3786	0.4858	0.5898	0.7041	0.759	0.835	Innings	1	0.443
1	1	1	0.3324	0.426	0.5416	0.629	0.734	0.819	0.874	Outs	1
2	0	1	0.402	0.443	0.5632	0.6566	0.749	0.801	0.874	1B	1
3	2	1	0.398	0.534	0.601	0.6957	0.774	0.797	0.87	Runs	-2

<tbody>
</tbody>

Formula is =INDEX(D2:J5,MATCH(1,IF(A:A=L2,IF(B:B=L3,IF(C:C=L4,1))),0),MATCH(L5,D1:J1,0)) . That's returning .443, which is a little off. If I turn the inputs a little... innings 2, outs 0, 1b 1, runs -2.... same problem, returns .534.

Sorry for leaving out some details in the original, but I thought what worked for one wouldve worked for the other.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Inning	Outs	1B	-3	-2	-1	0	1	2	3	crit	#	form
2	1	0	0	0.3031	0.3786	0.4858	0.5898	0.7041	0.759	0.835	Innings	1	0.426
3	1	1	1	0.3324	0.426	0.5416	0.629	0.734	0.819	0.874	Outs	1
4	2	0	1	0.402	0.443	0.5632	0.6566	0.749	0.801	0.874	1B	1
5	3	2	1	0.398	0.534	0.601	0.6957	0.774	0.797	0.87	Runs	-2
Sheet2

Cell Formulas
Range	Formula
M2	{=INDEX(D2:J5,MATCH(1,IF(A2:A100=L2,IF(B2:B100=L3,IF(C2:C100=L4,1))),0),MATCH(L5,D1:J1,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Your formulas IF's functions should have started in row 2.
BTW do not use whole column range unless absolutely necessary as this will slow down your calculation.
Maybe not seen at 1 formula level but will definitely be seen if you had few of them.

 

[agutts6]

Allahu Ahkbar.

 

[Aladin Akyurek]

Allahu Ahkbar.

I propose a modification which makes the formula a tad faster (this would also hold for the shorter/initial version)...

=INDEX(D2:J5,MATCH(1,IF(A2:A100=L2,IF(B2:B100=L3,IF(C2:C100=L4,1))),0),MATCH(L5,D1:J1,1))

Confirming with control+shift+enter is still required.