Cell address in a range

[starguy]

Suppose I have data in a range of B550. In col B (B5:B50) I hav
certain values.
Like this:

B1 tt
B2 bb
B3 gg
B4 ss
B5 pp
B6 kk
B7 bb
B8 dd
and so on... to B50

I want that when I enter any value (which is present in B5:B50) in cel
B52, formula in cell B53 should check it in range B5:B50 and return cel
name in which that value resides.
suppose I enter bb in B52 now cell B53 shoud return B2 (its cell nam
in the range). Note that if values appears two times it should retur
cell name of first value appeared in the range. (as in the case of bb)

thanking in anticipation of quick reply

 

[Ardus Petus]

=ADDRESS(MATCH(B52,B1:B50,0),2,4)

HTH

 

[starguy]

this formula do not work for any other range. MATCH returns row number
counting from first cell of range, not original row number.

=ADDRESS(MATCH(B52,B1:B50,0),2,4)

[/QUOTE]

 

[Ardus Petus]

=ADDRESS(ROW(B1)+MATCH(B52,B1:B50,0)+1,COLUMN(B1),4)

HTH
--
AP

this formula do not work for any other range. MATCH returns row number
counting from first cell of range, not original row number.

[/QUOTE]

 

[Ardus Petus]

Ooops: typo!
=ADDRESS(ROW(B1)+MATCH(B52,B1:B50,0)-1,COLUMN(B1),4)

--
AP

this formula do not work for any other range. MATCH returns row number
counting from first cell of range, not original row number.

[/QUOTE]

 

[starguy]

thank you, this works well.
one thing more. I want to return the cell name next to to cell that
contains specified value.
e.g. if cell B20 contains the value, I want to return next cell name
that is B21.
how can I do this.

 

[Ardus Petus]

=ADDRESS(ROW(B1)+MATCH(B52,B1:B50,0),COLUMN(B1),4)

 

[starguy]

okay I got it by removing -