Position of a cell in a range
 

I need to return the position of a cell in a list. For example, I have
the list:

A
B
C
D
E
F

I need a funciton that will tell me if D is the first cell, second,
third, etc...

Thanks.


--
zangief
------------------------------------------------------------------------
zangief's Profile: http://www.excelforum.com/member.php...o&userid=27560
View this thread: http://www.excelforum.com/showthread...hreadid=470999

zangief Wrote:
I need to return the position of a cell in a list. For example, I have
the list:

A
B
C
D
E
F

I need a funciton that will tell me if D is the first cell, second,
third, etc...

Thanks.

ASSUMING your entries are in Column A (A1:A6), try this formula


=\"A\"&SUMPRODUCT(($A$1:$A$6=\"D\")*(ROW($A$1:$A$6 )))

and see if this is what you need.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=470999

=MATCH("D",A2:A7,0)

zangief wrote:
I need to return the position of a cell in a list. For example, I have
the list:

A
B
C
D
E
F

I need a funciton that will tell me if D is the first cell, second,
third, etc...

Thanks.



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

BenjieLop wrote:
zangief Wrote:

I need to return the position of a cell in a list. For example, I have
the list:

A
B
C
D
E
F

I need a funciton that will tell me if D is the first cell, second,
third, etc...

Thanks.


ASSUMING your entries are in Column A (A1:A6), try this formula


=\"A\"&SUMPRODUCT(($A$1:$A$6=\"D\")*(ROW($A$1:$A$6 )))

and see if this is what you need.


What happens if you have more than one D in the target range?

<< What happens if you have more than one D in the target range?

Unfortunately, the formula I suggested only works for unique entries in
a column. Thank you for pointing it out.

Regards.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=470999