Referance Row and Column from cell location within range

[NamthCub]

Search and combining row and column data
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have been working on this with one other guy for the majority of the day and wondering if anyone has any insight.

<TABLE style="WIDTH: 319pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=427 x:str><COLGROUP><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" span=5 width=47><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; WIDTH: 35pt; HEIGHT: 13.5pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl34 height=18 width=47></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; WIDTH: 35pt; BORDER-RIGHT: windowtext 0.5pt solid" class=xl38 width=47>A</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; WIDTH: 35pt; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=47>B</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; WIDTH: 35pt; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=47>C</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; WIDTH: 35pt; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=47>D</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; WIDTH: 48pt; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=64>E</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; WIDTH: 48pt; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 width=64>F</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; WIDTH: 48pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: windowtext" class=xl33 width=64></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl35 height=17 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl39></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 x:num>30</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30>COM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: windowtext" class=xl31></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl36 height=17 x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: windowtext" class=xl26></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl36 height=17 x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num>40</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: windowtext" class=xl26></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl36 height=17 x:num>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num>50</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: windowtext" class=xl26></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl36 height=17 x:num>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num>30</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num>20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 x:num>32</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: windowtext" class=xl26></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl37 height=17></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-LEFT-COLOR: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28></TD><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: windowtext; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: windowtext" class=xl29></TD></TR></TBODY></TABLE>

We need to search B3:D5 for the value in E1 and return:
<o></o>
“COM414” in one cell
<o></o>
COM equals cell F1
<o></o>
4 equals the value of the COLUMN which E1 happens to fall in.
<o></o>
14 equals the value of the ROW which E1 happens to fall in.
<o></o>
any answers please note that the result can come in any cell, there may also be mutiple vaules, example: the value 30 could show up in cell B5 and D3.
<o></o>
Any help is welcome. Thank you very much.

 

[texasalynn]

duplicate - see
http://www.mrexcel.com/forum/showthread.php?t=516552

 

[MrKowz]

Here is a small UDF that should work for you:

Option Explicit
Public Function ValList(lookup_value As Variant, lookup_range As Range, prefix_val As String) As String
Dim rng     As Range, _
    delim   As String
 
delim = ", "
 
For Each rng In lookup_range
   If rng.Value = lookup_value Then
      ValList = ValList & prefix_val & rng.Column & rng.Row & delim
   End If
Next rng
 
If Len(ValList) > 2 Then
   ValList = Left(ValList, Len(ValList) - 2)
End If
 
End Function

Excel Workbook
	A	B	C	D	E	F
1					30	COM
2		4	6	8
3	10	6	30	3
4	12	10	50	30		COM33, COM44, COM25
5	14	30	20	32
Sheet1

Excel 2003

Cell Formulas
Range	Formula
F4	=vallist(E1,B3:D5,F1)

 

[Joe4]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted:
http://www.mrexcel.com/forum/showthread.php?t=127080