find Row

[rocket0612]

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

[Stefi]

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
>
>

[Bob Phillips]

"Stefi" <Stefi@discussions.microsoft.com> wrote in message
news:621566DC-84F3-4B8D-A0F5-3153A9EF0B11@microsoft.com...
> 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).

[Aladin Akyurek]

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".