Solved

How to return cell location of dynamic value in static list?

Posted on 2013-12-24
5
272 Views
Last Modified: 2013-12-29
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.
0
Comment
Question by:lizziesmalls23
  • 2
  • 2
5 Comments
 
LVL 14

Accepted Solution

by:
Zack Barresse earned 200 total points
ID: 39738470
Hi there,

Assuming the dates will be whole values and not containing time or partial values, and there are no duplicates, you could use...

=ADDRESS(MATCH(A1,B:B,0),2)

Open in new window


If you wanted it relative (no $ signs), you could use this variation...

=ADDRESS(MATCH(A1,B:B,0),2,4)

Open in new window

Regards,
Zack Barresse
0
 

Assisted Solution

by:lizziesmalls23
lizziesmalls23 earned 0 total points
ID: 39738486
Thank you -- I actually found that

=ADDRESS(MATCH(A1,B2:B11,0)+ROW(B2)-1,COLUMN(B2),4)

works as well (sorry, have been fussing with this for a while!) so both work out.
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 39738489
Good to hear.

Merry Christmas. :)

Zack
0
 
LVL 81

Expert Comment

by:byundt
ID: 39738626
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
0
 

Author Closing Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question