software

Suppose I have data in a range of B550. In col B (B5:B50) I have
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 cell
B52, formula in cell B53 should check it in range B5:B50 and return cell
name in which that value resides.
suppose I enter bb in B52 now cell B53 shoud return B2 (its cell name
in the range). Note that if values appears two times it should return
cell name of first value appeared in the range. (as in the case of bb)

thanking in anticipation of quick reply.--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=538333=ADDRESS(MATCH(B52,B1:B50,0),2,4)

HTH
--
AP

quot;starguyquot; gt; a écrit dans
le message de news: ...
gt;
gt; Suppose I have data in a range of B550. In col B (B5:B50) I have
gt; certain values.
gt; Like this:
gt;
gt; B1 tt
gt; B2 bb
gt; B3 gg
gt; B4 ss
gt; B5 pp
gt; B6 kk
gt; B7 bb
gt; B8 dd
gt; and so on... to B50
gt;
gt; I want that when I enter any value (which is present in B5:B50) in cell
gt; B52, formula in cell B53 should check it in range B5:B50 and return cell
gt; name in which that value resides.
gt; suppose I enter bb in B52 now cell B53 shoud return B2 (its cell name
gt; in the range). Note that if values appears two times it should return
gt; cell name of first value appeared in the range. (as in the case of bb)
gt;
gt; thanking in anticipation of quick reply.
gt;
gt;
gt; --
gt; starguy
gt; ------------------------------------------------------------------------
gt; starguy's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32434
gt; View this thread: www.excelforum.com/showthread...hreadid=538333
gt;

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

Ardus Petus Wrote:
gt; =ADDRESS(MATCH(B52,B1:B50,0),2,4)
gt;
gt; gt;[/color]--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=538333=ADDRESS(ROW(B1) MATCH(B52,B1:B50,0) 1,COLUMN(B1), 4)

HTH
--
AP

quot;starguyquot; gt; a écrit dans
le message de news: ...
gt;
gt; this formula do not work for any other range. MATCH returns row number
gt; counting from first cell of range, not original row number.
gt;
gt; Ardus Petus Wrote:
gt;gt; =ADDRESS(MATCH(B52,B1:B50,0),2,4)
gt;gt;
gt;gt; gt;
gt;
gt;
gt; --
gt; starguy
gt; ------------------------------------------------------------------------
gt; starguy's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32434
gt; View this thread: www.excelforum.com/showthread...hreadid=538333
gt;[/color]
Ooops: typo!
=ADDRESS(ROW(B1) MATCH(B52,B1:B50,0)-1,COLUMN(B1),4)

--
AP

quot;starguyquot; gt; a écrit dans
le message de news: ...
gt;
gt; this formula do not work for any other range. MATCH returns row number
gt; counting from first cell of range, not original row number.
gt;
gt; Ardus Petus Wrote:
gt;gt; =ADDRESS(MATCH(B52,B1:B50,0),2,4)
gt;gt;
gt;gt; gt;
gt;
gt;
gt; --
gt; starguy
gt; ------------------------------------------------------------------------
gt; starguy's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32434
gt; View this thread: www.excelforum.com/showthread...hreadid=538333
gt;[/color]

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.--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=538333=ADDRESS(ROW(B1) MATCH(B52,B1:B50,0),COLUMN(B1),4)

quot;starguyquot; gt; a écrit dans
le message de news: ...
gt;
gt; thank you, this works well.
gt; one thing more. I want to return the cell name next to to cell that
gt; contains specified value.
gt; e.g. if cell B20 contains the value, I want to return next cell name
gt; that is B21.
gt; how can I do this.
gt;
gt;
gt; --
gt; starguy
gt; ------------------------------------------------------------------------
gt; starguy's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32434
gt; View this thread: www.excelforum.com/showthread...hreadid=538333
gt;

okay I got it by removing -1--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=538333