Finding information from a matrix

Dan190984

New Member
Joined
Mar 18, 2011
Messages
26
Hi all

I have a matrix table which looks as follows....

<TABLE style="WIDTH: 174pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=232><COLGROUP><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" span=6 width=28><TBODY><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 48pt; HEIGHT: 41.25pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl73 height=55 rowSpan=2 width=64>Away



Home
</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; WIDTH: 42pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl70 width=56 colSpan=2>Bournemouth</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; WIDTH: 42pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl71 width=56 colSpan=2>Brentford </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; WIDTH: 42pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl71 width=56 colSpan=2>Brighton</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; WIDTH: 21pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl70 height=21 width=28>H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d7e4bc; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=28>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl70 width=28>H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d7e4bc; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=28>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl70 width=28>H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d7e4bc; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=28>A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=64>Bournemouth</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77 width=28>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77 width=28>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=28></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl64 height=21 width=64>Brentford</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=28></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl64 height=21 width=64>Brighton</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 21pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 width=28></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 21pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=28></TD></TR></TBODY></TABLE>

and a seperate sheet which looks as follows.

<TABLE style="WIDTH: 225pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=297><COLGROUP><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" span=4 width=58><TBODY><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 49pt; HEIGHT: 25.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl72 height=34 width=65>H</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; WIDTH: 176pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl68 width=232 colSpan=4>Bournemouth</TD></TR><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 49pt; HEIGHT: 25.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl72 height=34 width=65>A</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; WIDTH: 176pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl68 width=232 colSpan=4>Brentford</TD></TR><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 49pt; HEIGHT: 25.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl71 height=34 width=65>Score (H/A)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 88pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl66 width=116 colSpan=2></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d7e4bc; WIDTH: 88pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl66 width=116 colSpan=2></TD></TR></TBODY></TABLE>

Basically what I want to happen is to be able to enter a different home and away team and then the Score (H/A) section autofill.

The score (H/A) has two cells although this does not show well in what I have copied

How would I do this?

Thanks
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,712
Hi,

I'm not sure whether it's the way your matrix has come out via the forum software, but it looks like you have some merged Cells here. Merged Cells are the devil's work in Excel. Consider 'Center Across Selection' instead, which can be found under Format Cells > Alignment > Horizontal.

Another issue to overcome is the fact that once the Cells are unmerged, your Away team will only occupy the Cell above 'H'. This isn't ideal, but we can get around it.

The following couple of formulas should do the trick:

<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 81px;"><col style="width: 81px;"><col style="width: 81px;"><col style="width: 81px;"><col style="width: 81px;"><col style="width: 81px;"><col style="width: 81px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Away</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Bournemouth</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">
</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Brentford</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">
</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Brighton</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Home</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">H</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">A</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">H</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">A</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">H</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">A</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Bournemouth</td><td style="background-color: rgb(0, 0, 0); font-family: Verdana;">
</td><td style="background-color: rgb(0, 0, 0); font-family: Verdana;">
</td><td style="font-family: Calibri; text-align: right;">3</td><td style="font-family: Calibri; text-align: right;">1</td><td style="font-family: Verdana;">
</td><td style="font-family: Verdana;">
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Brentford</td><td style="font-family: Verdana;">
</td><td style="font-family: Verdana;">
</td><td style="background-color: rgb(0, 0, 0); font-family: Verdana;">
</td><td style="background-color: rgb(0, 0, 0); font-family: Verdana;">
</td><td style="font-family: Verdana; text-align: right;">5</td><td style="font-family: Verdana; text-align: right;">4</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Brighton</td><td style="font-family: Verdana; text-align: right;">6</td><td style="font-family: Verdana; text-align: right;">4</td><td style="font-family: Verdana;">
</td><td style="font-family: Verdana;">
</td><td style="background-color: rgb(0, 0, 0); font-family: Verdana;">
</td><td style="background-color: rgb(0, 0, 0); font-family: Verdana;">
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">H</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Brighton</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">A</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Bournemouth</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td><td style="background-color: rgb(204, 255, 204); font-family: Calibri;">Score (H/A)</td><td style="background-color: rgb(204, 255, 204); font-family: Verdana; text-align: right;">6</td><td style="background-color: rgb(204, 255, 204); font-family: Verdana; text-align: right;">4</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B10</td><td>=INDEX(B3:G5,MATCH(B8,A3:A5,0),MATCH(B9,B1:G1,0))</td></tr><tr><td>C10</td><td>=INDEX(B3:G5,MATCH(B8,A3:A5,0),MATCH(B9,B1:G1,0)+1)</td></tr></tbody></table></td></tr></tbody></table>
Notice how the second MATCH in the formula in C10 has +1 added - this is to get around empty Cell problem.

Hope this helps.

Matty
 
Last edited:

Forum statistics

Threads
1,171,224
Messages
5,874,459
Members
433,053
Latest member
ahasan465231

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