Find Cell Address

LNC

New Member
Joined
Mar 16, 2011
Messages
2
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Welcome to the forums!

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

Code:
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,109,330
Messages
5,528,043
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top