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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

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>
 

Forum statistics

Threads
1,141,139
Messages
5,704,515
Members
421,353
Latest member
jekoxien15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top