Link to home
Create AccountLog in
Avatar of lizziesmalls23
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.
ASKER CERTIFIED SOLUTION
Avatar of Zack Barresse
Zack Barresse
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.

Brad
Avatar of lizziesmalls23
lizziesmalls23

ASKER

It's an additional way to find the answer, and can be a little more dynamic in a larger spreadsheet/data set.