find Row
 

I currently use VLOOKUP to find the address for a company when their
name is input. It returns the address but I would also like it to
return the row it gets the details from, is this possible?

thanks


--
rocket0612
------------------------------------------------------------------------
rocket0612's Profile: http://www.excelforum.com/member.php...o&userid=19492
View this thread: http://www.excelforum.com/showthread...hreadid=476191

MATCH function returns row number!
Regards,
Stefi


€žrocket0612€� ezt Ã*rta:


I currently use VLOOKUP to find the address for a company when their
name is input. It returns the address but I would also like it to
return the row it gets the details from, is this possible?

thanks


--
rocket0612
------------------------------------------------------------------------
rocket0612's Profile: http://www.excelforum.com/member.php...o&userid=19492
View this thread: http://www.excelforum.com/showthread...hreadid=476191

"Stefi" wrote in message
...
MATCH function returns row number!

No, it returns the index into the data. This could be the row number IF it
is vertical data, but may not (MATCH("123",A100:A110,0 will never be between
100 and 110).

find Row
 

rocket0612 wrote:
I currently use VLOOKUP to find the address for a company when their
name is input. It returns the address but I would also like it to
return the row it gets the details from, is this possible?

thanks



Given in A2:B4:

x,7
y,6
z,9

the formula:

=VLOOKUP("y",$A$2:$B$4,2,0)

would return 6.

=MATCH("y",$A$2:$A$4,0)

would return the position of the lookup value "y", therefore also of the
value associated with it.

=MATCH("y",$A$2:$A$4,0)+ROW($A$2)-1

would return the native row number at which "y" is.

=CELL("Address",INDEX($B$2:$B$4,MATCH("y",$A$2:$A$ 4,0)))

would return the cell reference of the value associated with "y".