find Row

R

rocket0612

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

thank
 
S

Stefi

MATCH function returns row number!
Regards,
Stefi


„rocket0612†ezt írta:
 
B

Bob Phillips

Stefi said:
MATCH function returns row number!
Click to expand...

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).
 
A

Aladin Akyurek

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

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