LNC

New Member
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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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%

** 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.

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

Thanks a lot!! MrKowz

Replies
5
Views
451
Replies
7
Views
345
Replies
1
Views
109
Replies
1
Views
82
Replies
5
Views
264

1,220,987
Messages
6,157,236
Members
451,407
Latest member
vdaesety

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.

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

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