Getting the Cell ID

[Shaijuzacharia]

Hi,

I have got an Excel sheet in which text and numbers are there.
1) I want to get the ID of the cell with specified text. Is there any
function for doing that?
2) How can I find the last row with data in the same sheet.

Thanks,
Shaiju

 

[Roger Govier]

Hi

Assuming your column containing the text is column A , and the text you were
looking for is Roger, then
=ADDRESS(MATCH("Roger",A:A,0),1)
The 1 at the end refers to column A.
adjust for other columns.

Finding the last row with data, will depend upon whether tit is Numeric or
Text.
For Numeric
=LOOKUP(99^99,A:A)
For Text(
=LOOKUP(REPT("Z",255),A:A)

In each case you are choosing some arbitrary value that is unlikely to occur
in the column.
I have chosen 99 to the power of 99.
Some people recommend using
9.99999999999999E+307
which is the largest number that can be written in an Excel cell, but I find
that cumbersome to remember and type. 99^99 has always more than covered the
values for me.

 

[Shaijuzacharia]

Hi

Assuming your column containing the text is column A , and the text you were
looking for is Roger, then
=ADDRESS(MATCH("Roger",A:A,0),1)
The 1 at the end refers to column A.
adjust for other columns.

Finding the last row with data, will depend upon whether tit is Numeric or
Text.
For Numeric
=LOOKUP(99^99,A:A)
For Text(
=LOOKUP(REPT("Z",255),A:A)

In each case you are choosing some arbitrary value that is unlikely to occur
in the column.
I have chosen 99 to the power of 99.
Some people recommend using
9.99999999999999E+307
which is the largest number that can be written in an Excel cell, but I find
that cumbersome to remember and type. 99^99 has always more than covered the
values for me.
--
Regards
Roger Govier








- Show quoted text -

Hi Roger,

Thank you very much.

Shaiju.