Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
=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. |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
<< 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP - Date Range In 1 Cell Calculation | Excel Worksheet Functions | |||
How do I change a cell range with a reference cell? | Excel Discussion (Misc queries) | |||
How can I assign a range starting cell based on a variable locati. | Excel Worksheet Functions | |||
Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions | |||
Adding contents of one cell to a range of cells. | Excel Worksheet Functions |