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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
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:

Watch MrExcel Video

Forum statistics

Threads
1,108,817
Messages
5,525,070
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top