lookup

[Guest]

is there a formula that I can use to find the cell address of a lookupstatement?

If I have a sheet with "Category" in cell A300, What forumula can I use to find this then tell me what cell it is in. I have tried Match, and lookup, and it gives me the value, but I need the row number

=row(vlookup("Category",data,1,false)) ?

 

[Domenic]

Hi,

Assuming that your data in Column A starts at A2, and that D1 contains
the lookup value, try,

=ROW(OFFSET($A$1,MATCH(D1,$A$2:$A$100,0),0))

Hope this helps!

 

[RagDyeR]

<<"I have tried Match, and lookup, and it gives me the value, but I need the
row number">>

I don't understand how you say you used Match, and *didn't* obtain the row
number.
The main function of Match() is to find locations!

Try this:

=MATCH("category",A1:A100,0)

And you'll get the *first* occurrences row number.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



is there a formula that I can use to find the cell address of a
lookupstatement?

If I have a sheet with "Category" in cell A300, What forumula can I use to
find this then tell me what cell it is in. I have tried Match, and lookup,
and it gives me the value, but I need the row number

=row(vlookup("Category",data,1,false)) ?

 

[Harlan Grove]

Assuming that your data in Column A starts at A2, and that D1
contains the lookup value, try,

=ROW(OFFSET($A$1,MATCH(D1,$A$2:$A$100,0),0))

....

Or just (meaning fewer function calls and nesting levels)

=MATCH(D1,$A$2:$A$100,0)+CELL("Row",$A$2:$A$100)-1

or with hardcoding

=MATCH(D1,$A$2:$A$100,0)+1

 

[Domenic]

Or just (meaning fewer function calls and nesting levels)

=MATCH(D1,$A$2:$A$100,0)+CELL("Row",$A$2:$A$100)-1

or with hardcoding

=MATCH(D1,$A$2:$A$100,0)+1

Yep! I think I may have over-thought this one.

I'm with you. The fewer the function calls, the better.