Cell address in a range

  • Thread starter Thread starter starguy
  • Start date Start date
S

starguy

Suppose I have data in a range of B5:D50. 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
 
this formula do not work for any other range. MATCH returns row number
counting from first cell of range, not original row number.

Ardus said:
=ADDRESS(MATCH(B52,B1:B50,0),2,4)
Click to expand...
[/QUOTE]
 
=ADDRESS(ROW(B1)+MATCH(B52,B1:B50,0)+1,COLUMN(B1),4)

HTH
--
AP

starguy said:
this formula do not work for any other range. MATCH returns row number
counting from first cell of range, not original row number.
Click to expand...
[/QUOTE]
 
Ooops: typo!
=ADDRESS(ROW(B1)+MATCH(B52,B1:B50,0)-1,COLUMN(B1),4)

--
AP

starguy said:
this formula do not work for any other range. MATCH returns row number
counting from first cell of range, not original row number.
Click to expand...
[/QUOTE]
 
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.
 
Back
Top