Index/Match Array, Multiple Column Array

agutts6

New Member
Joined
Jun 19, 2011
Messages
19
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.



inningout-101
1
03.32.35.4
116.53.14.5
125.00.53.0
204.44.14.6
212.23.35.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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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

New Member
Joined
Jun 19, 2011
Messages
19
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.

InningOuts1B-3-2-10123crit#form
1000.30310.37860.48580.58980.70410.7590.835Innings10.443
1110.33240.4260.54160.6290.7340.8190.874Outs1
2010.4020.4430.56320.65660.7490.8010.8741B1
3210.3980.5340.6010.69570.7740.7970.87Runs-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

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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.

InningOuts1B-3-2-10123crit#form
1000.30310.37860.48580.58980.70410.7590.835Innings10.443
1110.33240.4260.54160.6290.7340.8190.874Outs1
2010.4020.4430.56320.65660.7490.8010.8741B1
3210.3980.5340.6010.69570.7740.7970.87Runs-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.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Inning</td><td style=";">Outs</td><td style=";">1B</td><td style="text-align: right;;">-3</td><td style="text-align: right;;">-2</td><td style="text-align: right;;">-1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style=";">crit</td><td style=";">#</td><td style=";">form</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.3031</td><td style="text-align: right;;">0.3786</td><td style="text-align: right;;">0.4858</td><td style="text-align: right;;">0.5898</td><td style="text-align: right;;">0.7041</td><td style="text-align: right;;">0.759</td><td style="text-align: right;;">0.835</td><td style=";">Innings</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0.426</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0.3324</td><td style="text-align: right;background-color: #FFFF00;;">0.426</td><td style="text-align: right;;">0.5416</td><td style="text-align: right;;">0.629</td><td style="text-align: right;;">0.734</td><td style="text-align: right;;">0.819</td><td style="text-align: right;;">0.874</td><td style=";">Outs</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0.402</td><td style="text-align: right;;">0.443</td><td style="text-align: right;;">0.5632</td><td style="text-align: right;;">0.6566</td><td style="text-align: right;;">0.749</td><td style="text-align: right;;">0.801</td><td style="text-align: right;;">0.874</td><td style=";">1B</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0.398</td><td style="text-align: right;;">0.534</td><td style="text-align: right;;">0.601</td><td style="text-align: right;;">0.6957</td><td style="text-align: right;;">0.774</td><td style="text-align: right;;">0.797</td><td style="text-align: right;;">0.87</td><td style=";">Runs</td><td style="text-align: right;;">-2</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">M2</th><td style="text-align:left">{=INDEX(<font color="Blue">D2:J5,MATCH(<font color="Red">1,IF(<font color="Green">A2:A100=L2,IF(<font color="Purple">B2:B100=L3,IF(<font color="Teal">C2:C100=L4,1</font>)</font>)</font>),0</font>),MATCH(<font color="Red">L5,D1:J1,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,119
Messages
5,640,218
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top