Find Cell Address

[LNC]

Hi All,

How to get the column and row number "C3" of the target data?

Target data: 150%


Column A B C D E
Row
1 1% 1% 1% 1% 1%
2 1% 1% 1% 1% 1%
3 1% 1% 150% 1% 1%
4 1% 1% 1% 1% 1%
5 1% 1% 1% 1% 1%

is it possible or not if so can anybody provide me an example?

Thanks,

LN

 

[T. Valko]

Hi All,

How to get the column and row number "C3" of the target data?

Target data: 150%


Column A B C D E
Row
1 1% 1% 1% 1% 1%
2 1% 1% 1% 1% 1%
3 1% 1% 150% 1% 1%
4 1% 1% 1% 1% 1%
5 1% 1% 1% 1% 1%

is it possible or not if so can anybody provide me an example?

Thanks,

LN

As long as there is only one instance of the lookup value...

Array entered**:

H1 = the lookup value 150%

=ADDRESS(MAX(IF(A1:E5=H1,ROW(A1:E5))),MAX(IF(A1:E5=H1,COLUMN(A1:E5))),4)

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[MrKowz]

Welcome to the forums!

Another possibility that will work if there are multiple entries, and you want to find a list of all entries:

Public Function RowCol(ByVal rng As Range, val As Variant)
Dim rRng As Range
For Each rRng In rng
    If rRng.Value = val Then
        RowCol = RowCol & rRng.Address(False, False) & ", "
    End If
Next rRng
If Len(RowCol) = 0 Then
    RowCol = "Value Not Found"
Else
    RowCol = Left(RowCol, Len(RowCol) - 2)
End If
End Function

Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><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></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</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">1%</TD><TD style="TEXT-ALIGN: right">1%</TD><TD style="TEXT-ALIGN: right"></TD><TD>Value to Find</TD><TD style="TEXT-ALIGN: right">150%</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</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">1%</TD><TD style="TEXT-ALIGN: right">1%</TD><TD style="TEXT-ALIGN: right"></TD><TD>Address</TD><TD>D3, B4</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">150%</TD><TD style="TEXT-ALIGN: right">1%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">1%</TD><TD style="TEXT-ALIGN: right">150%</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"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</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">1%</TD><TD style="TEXT-ALIGN: right">1%</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet3</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>H2</TH><TD style="TEXT-ALIGN: left">=RowCol(A1:E5,H1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[LNC]

Thanks a lot!! MrKowz