Would like to Obtain a Cell Address from a vlookup function

[CR_2004_04_08]

Is there a way to get this value?

my vlookup function returns a value from a row that has
additional detail. I would like to to retrieve the cell
address associated with the returned value from vlookup.
Is this possible?

 

[Peo Sjoblom]

You can use a formula and index/match
Assume your vlookup would look like this

=VLOOKUP(A1,MyTable,2,0)

the equivalent would be

=INDEX(MyTable,MATCH(A1,INDEX(MyTable,,1),0),2)

where 2 is the index column responding to the 2 in the vlookup and 0 = FALSE

then this would return the address

=CELL("address",INDEX(MyTable,MATCH(A1,INDEX(MyTable,,1),0),2))

 

[Frank Kabel]

Hi
one way to get the row number would be using MATCH:
=MATCH(lookup_value,$A$1:$A$1000,0)
will return the row number in the range A1:A1000 of your match.

or tu return the address you may use
=CELL("address",INDIRECT("A" & MATCH(lookup_value,$A$1:$A$1000,0)))

 

[ross]

Hi,
the natch is a good way, but here jus another for fun!

insert a blank col in the lookup array, then fill it with the
corrsponding row number, then re turn this as the look up vale, and if
you like co0cTONATE IWTH WITH the col letterthus
A B C
1 vlookup 23 1
2 " 243 2
3 "..etc
4

="C"&(vlookup(23,B2:c4,2,1)) ... somthing like this,i havn't put it
thjough excel...
this might be good if you have loads of look ups runnning down a
colum, it might save memory by reducing the formula size... but the
match method is a lot smarter!

good luck
ross