Lookup in a matrix

[infasys]

I have the Following table

<TABLE style="WIDTH: 377pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=503><COLGROUP><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 41pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20 width=55></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: yellow; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=64>7</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=21>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND: #948a54; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-pattern: black none" class=xl65>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>7</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>10</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>13</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>16</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>19</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND: #92d050; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-pattern: black none" class=xl65>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>8</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>14</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>17</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=20>C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND: #00b0f0; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-pattern: black none" class=xl65>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>9</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>15</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>18</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>21</TD></TR></TBODY></TABLE>


I would like to be able to lookup for example 11 and return 4 and B. 11 could be anywhere in the matrix. I was thinking of Setting a RangeName and then Looking it up to return the Number and Letter..

 

[infasys]

Hi Mick,

Late Night typo.

Your supplied Code runs but returns the same as the original. It gives me the Cell Reference... I need it to return the Column and Row References from the Tabel Range. Any Thoughts.

Cheers

Morty

 

[MickG]

From Selecting "F3" below, returns "B/5", what do you want returned ??.

<TABLE style="WIDTH: 487pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=649 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 5010" width=137><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl34 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: yellow" width=64 height=18> </TD><TD class=xl25 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: yellow" align=right width=64 x:num>1</TD><TD class=xl25 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: yellow" align=right width=64 x:num>2</TD><TD class=xl25 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: yellow" align=right width=64 x:num>3</TD><TD class=xl25 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: yellow" align=right width=64 x:num>4</TD><TD class=xl25 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: yellow" align=right width=64 x:num>5</TD><TD class=xl25 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: yellow" align=right width=64 x:num>6</TD><TD class=xl26 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: yellow" align=right width=64 x:num>7</TD><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 103pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffcc" width=137 x:str="'=arrayfind(F3,MyRng)">=arrayfind(F3,MyRng)</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl35 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: yellow" width=64 height=18>A</TD><TD class=xl27 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>1</TD><TD class=xl28 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>4</TD><TD class=xl28 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>7</TD><TD class=xl28 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>10</TD><TD class=xl28 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>13</TD><TD class=xl28 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>16</TD><TD class=xl29 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>19</TD><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: #ccffcc">B/5</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl35 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: yellow" width=64 height=18>B</TD><TD class=xl27 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>2</TD><TD class=xl28 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>5</TD><TD class=xl28 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>8</TD><TD class=xl28 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>11</TD><TD class=xl33 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: #ccffcc" align=right width=64 x:num>14</TD><TD class=xl28 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>17</TD><TD class=xl29 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: white 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>20</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl36 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: yellow" width=64 height=18>C</TD><TD class=xl30 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>3</TD><TD class=xl31 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>6</TD><TD class=xl31 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>9</TD><TD class=xl31 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>12</TD><TD class=xl31 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>15</TD><TD class=xl31 style="BORDER-RIGHT: white 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>18</TD><TD class=xl32 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: white; BORDER-LEFT: white; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: #ccffff" align=right width=64 x:num>21</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

Mick