Locating Cell Number

[khawarameer]

Hello All,

Take a look at these columns below;

<table style="border-collapse: collapse; width: 134pt;" width="178" border="0" cellpadding="0" cellspacing="0"><col style="width: 67pt;" span="2" width="89"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt; width: 67pt;" width="89" align="right" height="17">.00</td> <td class="xl65" style="width: 67pt;" width="89" align="right">810.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">.00</td> <td class="xl65" align="right">1,729.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">.00</td> <td class="xl65" align="right">1,594.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">.00</td> <td class="xl65" align="right">1,599.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">.00</td> <td class="xl65" align="right">498.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">498.00</td> <td class="xl65" align="right">.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">.00</td> <td class="xl65" align="right">1,852.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">.00</td> <td class="xl65" align="right">454.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl65" style="height: 12.75pt;" align="right" height="17">.00</td> <td class="xl65" align="right">50.00</td> </tr> </tbody></table>
I want to find the cell reference number of any value of column A (which is greater than 0) in column B. I could only manage to find values with my formula i.e.

=IF(A1>0,VLOOKUP(A1,$B$1:$B$9,1,0),0)

Your help is required.

Regards

 

[GTO]

Hello All,

Take a look at these columns below;

<TABLE style="WIDTH: 134pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=178 border=0><COLGROUP><COL style="WIDTH: 67pt" span=2 width=89><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="WIDTH: 67pt; HEIGHT: 12.75pt" align=right width=89 height=17>.00</TD><TD class=xl65 style="WIDTH: 67pt" align=right width=89>810.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="HEIGHT: 12.75pt" align=right height=17>.00</TD><TD class=xl65 align=right>1,729.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="HEIGHT: 12.75pt" align=right height=17>.00</TD><TD class=xl65 align=right>1,594.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="HEIGHT: 12.75pt" align=right height=17>.00</TD><TD class=xl65 align=right>1,599.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="HEIGHT: 12.75pt" align=right height=17>.00</TD><TD class=xl65 align=right>498.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="HEIGHT: 12.75pt" align=right height=17>498.00</TD><TD class=xl65 align=right>.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="HEIGHT: 12.75pt" align=right height=17>.00</TD><TD class=xl65 align=right>1,852.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="HEIGHT: 12.75pt" align=right height=17>.00</TD><TD class=xl65 align=right>454.00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="HEIGHT: 12.75pt" align=right height=17>.00</TD><TD class=xl65 align=right>50.00</TD></TR></TBODY></TABLE>
I want to find the cell reference number of any value of column A (which is greater than 0) in column B. I could only manage to find values with my formula i.e.

=IF(A1>0,VLOOKUP(A1,$B$1:$B$9,1,0),0)

Your help is required.

Regards

Greetings,
By "cell reference number", do you mean what index the cell is within a specified range, or, a cell's address, or???

Mark

 

[khawarameer]

Just Cell's Address.

 

[ayazgreat]

You mean this

<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D1</TD><TD>=IF(A1>0,CELL("ROW",B1))</TD></TR><TR><TD>D2</TD><TD>=IF(A2>0,CELL("ROW",B2))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[khawarameer]

Lookup value is in Column A.
Lookup array is Column B.

Trying to match value of column A (greater than 0) in Column B and when a value matches cell address of that matched value is required.

Can't explain any better.

 

[Andrew Poulsom]

How about?

=IF(A1>0,ADDRESS(MATCH(A1,B:B,FALSE),2),0)

 

[khawarameer]

That worked Andrew,

Just one little problem, as you can see the lookup values are numbers, and there is a possibility of more than one matches. If this is the case i want the nearest cell reference to the lookedup value in column A.

Can you please provide me the solution.

Best regards

 

[bosco_yip]

Try, array formula….

{="B"&MATCH(MIN(IF(A1:A9>0,A1:A9)),A1:A9,0)}

Regards
Bosco

 

[khawarameer]

Hello Bosco,

The formula only worked for first cell i.e. A1

<TABLE style="WIDTH: 114pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=152><COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1322" width=31><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2773" width=65><COL style="WIDTH: 42pt" width=56><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 23pt; HEIGHT: 13.5pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 height=18 width=31>0


</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #e6e6e6; WIDTH: 49pt; BORDER-TOP: #3867a6 1pt solid; BORDER-RIGHT: #3867a6 1pt solid" id=td_post_2176789 class=xl65 width=65>810</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 width=56>B6

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 23pt; HEIGHT: 13.5pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 height=18 width=31>0</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #e6e6e6; WIDTH: 49pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 width=65>1,729.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>B5</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 23pt; HEIGHT: 13.5pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 height=18 width=31>0</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #e6e6e6; WIDTH: 49pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 width=65>1,594.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>B4

</TD></TR>

<TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 23pt; HEIGHT: 13.5pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 height=18 width=31>0</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #e6e6e6; WIDTH: 49pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 width=65>1,599.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>B3</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 23pt; HEIGHT: 13.5pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 height=18 width=31>0</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #e6e6e6; WIDTH: 49pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 width=65>498</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>B2

</TD></TR>

<TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 23pt; HEIGHT: 13.5pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 height=18 width=31>498</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #e6e6e6; WIDTH: 49pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 width=65>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>B1</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 23pt; HEIGHT: 13.5pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 height=18 width=31>0</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #e6e6e6; WIDTH: 49pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl66 width=65>1,852.00</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>B1

</TD></TR>

<TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 23pt; HEIGHT: 13.5pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 height=18 width=31>0</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #e6e6e6; WIDTH: 49pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 width=65>454</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>B1</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; BACKGROUND-COLOR: #e6e6e6; WIDTH: 23pt; HEIGHT: 13.5pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 height=18 width=31>0</TD><TD style="BORDER-BOTTOM: #3867a6 1pt solid; BORDER-LEFT: #3867a6; BACKGROUND-COLOR: #e6e6e6; WIDTH: 49pt; BORDER-TOP: #3867a6; BORDER-RIGHT: #3867a6 1pt solid" class=xl65 width=65>50


</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id=td_post_2176715 class=xl67>B1

</TD></TR></TBODY></TABLE>

Furthermore it also considers 0's and the data i pasted here is actually samply data. Actual data consist of 6500 rows approx. so an array formula will use greate deal of memory.

Regards

 

[khawarameer]

Any VBA Code may help.

Can any one please.