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.
Microsoft Excel
Last Comment
lizziesmalls23
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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