Position of a cell in a range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

zangief - 27 Sep 2005 03:15 GMT

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.

Signature

zangief

Reply to this Message

BenjieLop - 27 Sep 2005 03:57 GMT

zangief Wrote:

> I need to return the position of a cell in a list. For example, I hav
> the list:
[quoted text clipped - 10 lines]
>
> 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

--
BenjieLo

Reply to this Message

Aladin Akyurek - 27 Sep 2005 05:56 GMT

> zangief Wrote:
>
[quoted text clipped - 18 lines]
>
> and see if this is what you need.

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

Reply to this Message

BenjieLop - 27 Sep 2005 15:07 GMT

<< 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.

Signature

BenjieLop

Reply to this Message

Aladin Akyurek - 27 Sep 2005 05:49 GMT

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

> I need to return the position of a cell in a list. For example, I have
> the list:
[quoted text clipped - 10 lines]
>
> Thanks.

Signature

[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.

Reply to this Message