I want to determine the address of a value...

For Example: My goal to realize that the 'd' that corresponds to '14'
is in 'B3' (and then post that 'B3')

a2 10 b2 d
a3 14 b3 d
a4 39 b4 f

c5 14 d5 = vlookup(c5,a2:b4,2,false) ...result 'd'

Now I want to be able to determine the cell was 'b3'

If I use match the result with the original range, I'll end up with the
answer 'b2' not 'b3' ... the result I need...

Thanks!!!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Hef,

Why not use MATCH with the extended range B1:B4 rather than B2:B4?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hef" wrote in message
...

I want to determine the address of a value...

For Example: My goal to realize that the 'd' that corresponds to '14'
is in 'B3' (and then post that 'B3')

a2 10 b2 d
a3 14 b3 d
a4 39 b4 f

c5 14 d5 = vlookup(c5,a2:b4,2,false) ...result 'd'

Now I want to be able to determine the cell was 'b3'

If I use match the result with the original range, I'll end up with the
answer 'b2' not 'b3' ... the result I need...

Thanks!!!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Based on your example, try this formula in D5:

=CELL("address",OFFSET(A1,MATCH(C5,A2:A4,FALSE),1) )

I'm sure there is an easier or more elegant solution, but this seems
to return the cell address B3 as you requested.

Regards,
Steve Hieb