lizziesmalls23
asked on
How to return cell location of dynamic value in static list?
Example
Static List:
Dates Cell Location
12/1/13 B2
12/8/13 B3
12/15/13 B4
If a user inputs 12/8/13 in Cell A1, I need a formula to tell me the value in A1 is located at B3 in the list. I need the formula to be dynamic so that if a user would input the value of 12/15/13 into cell A1, it would return a result of B3.
Static List:
Dates Cell Location
12/1/13 B2
12/8/13 B3
12/15/13 B4
If a user inputs 12/8/13 in Cell A1, I need a formula to tell me the value in A1 is located at B3 in the list. I need the formula to be dynamic so that if a user would input the value of 12/15/13 into cell A1, it would return a result of B3.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Given your other question on charting, you may be trying to define a range of cells to plot. A cell address, such as returned using the ADDRESS function, is merely text. It does not point to the actual cell. To use the result of ADDRESS in a formula, you need to put it inside the INDIRECT function.
If my guess is correct, you would be better off using the INDEX function, which returns a range reference to a cell in column B.
=INDEX($B$2:$B$11,MATCH($A
If you want to define a starting date in A1 and an ending date in A2, you could define the range of data as:
=INDEX($B$2:$B$11,MATCH($A
I made a point of making all cell references in the above formula use absolute addressing. You will want to do that if you are creating a dynamic named range. If you don't, the cells being referenced will change depending on the active cell where you use the formula.
Brad
If my guess is correct, you would be better off using the INDEX function, which returns a range reference to a cell in column B.
=INDEX($B$2:$B$11,MATCH($A
If you want to define a starting date in A1 and an ending date in A2, you could define the range of data as:
=INDEX($B$2:$B$11,MATCH($A
I made a point of making all cell references in the above formula use absolute addressing. You will want to do that if you are creating a dynamic named range. If you don't, the cells being referenced will change depending on the active cell where you use the formula.
Brad
ASKER
It's an additional way to find the answer, and can be a little more dynamic in a larger spreadsheet/data set.
Merry Christmas. :)
Zack