Index, Match with Dates

[Don]

I have a spreadsheet that is a vertical list of data, in ascending date
order. There are multiple entries for each date. I wish to return the data in
column B based on a Index,Match lookup I'm using for the date.
This is my formula in cell A2:
=INDEX(B10:B18,MATCH($A$1,$A$10:$A$20,0))

What is happening is when I drag this down to cell A6 and beyond, it returns
data from date 1/27 even though I feel the formula should only be looking at
data from 1/26.

A B
1 1/26/2009
2 1
3 2
4 2
5 3
6 2
7 4
8
9
10 1/26/2009 1
11 1/26/2009 2
12 1/26/2009 2
13 1/26/2009 3
14 1/27/2009 2
15 1/27/2009 4
16 1/27/2009 5
17 1/27/2009 4
18 1/27/2009 2

I want to set up a template so the output would be correct no matter what my
lookup data is. The number of rows and entries per day will vary.

Thank you in advance for your help!

 

[Shane Devenshire]

Hi,

Try adding absolute reference to the index range:

=INDEX(B$10:B$18,MATCH($A$1,$A$10:$A$20,0))

and if you are only copying down you don't need them for the column
references.

 

[Max]

It appears that multiple returns are involved. You can't use a "normal"
index/match since that'll return only the 1st matched result.

Try this simple set-up which will deliver the multiple results functionality
Source data is in cols A and B from row1 down
Assume the input for the date of interest will be in D1, eg: 3-Jan-2009
In E1: =IF(D$1="","",IF(A1=D$1,ROW(),""))
In F1: =IF(ROW()>COUNT(E:E),"",INDEX(B:B,SMALL(E:E,ROW())))
Copy E1:F1 down to cover the max expected extent of data in col A, eg down
to F100. Minimize col E. Col F will return the required results from col B
for the input date in D1, all neatly packed at the top.

If the above helps, pl click the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Don]

Max,
Getting closer. Here's my dilemma now:

My template has dates across the top. I would like to use your formula to
return the value of Pounds, then Formula from my data sheet. Inserting your
formula into cell A3, A4, A5 etc. Then do the same and look up all the
relative values from the data sheet for 1/27/09 into cells C3, C4, C5 etc.
And so on for the next day to make a weekly schedule.

The formula I have in cell A3 is:
=IF($B$1="","",IF($B$1=Data!G1,ROW(),"")-2)
My data is on another tab named "Data", and I discovered that because I have
2 header rows here, I needed to insert a "-2" to line it up.
The formula I have in cell B3 is
=IF(ROW()>COUNT(A:A),"",INDEX(Data!D,SMALL(A:A,ROW())-2))


When I do this, Excel will only allow me to copy down 12 rows for the Count
value, and 3 for the Pounds value.

A B C D E

1 1/26/09 1/27/09

2 Pounds Formula Pounds Formula

3 3 270000
4 4 60000
5 5 90000
6 6
7 7
8 8


What am I missing?

thanks,
Don

 

[Max]

Here's my dilemma now ..

I'm afraid its a whole new ball game that you're now brought into the
picture. I'm not sure whether its possible. You could start a new thread in
..worksheet.functions which is a more appropriate and better attended group.
Good luck.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---