lookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

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)) ?
 
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!
 
<<"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)) ?
 
Domenic said:
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))
Click to expand...
....

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
 
Harlan Grove said:
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
Click to expand...

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

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