# 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.
Zack Barresse

membership
Create an account to see this answer
Signing up is free. No credit card required.
SOLUTION

membership
Create an account to see this answer
Signing up is free. No credit card required.
Good to hear.

Merry Christmas. :)

Zack
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\$1,\$B\$2:\$B\$11,0))

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\$1,\$B\$2:\$B\$11,0)):INDEX(\$B\$2:\$B\$11,MATCH(\$A\$2,\$B\$2:\$B\$11,0))

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.