Index, Match

[Don]

I have a spreadsheet that has a vertical list of data that is driven by
dates. The same date will have many entries of different formulas and
weights. The formulas or weights could be the same for multiple days.

A B C
1/26/09 F123 270000
1/26/09 F852 60000
1/26/09 F331 90000
1/27/09 F456 150000
1/27/09 F331 45000
1/27/09 F876 60000


I would like to create a spreadsheet so the user can download the data above
into a worksheet and based on the date selection, a formaula will return the
days value of Pounds, then Formula.

This is what I would like to see:

A B C D

1 1/26/09 1/27/09

2 Pounds Formula Pounds Formula

3 270000 F123 150000 F456
4 60000 F852 45000 F331
5 90000 F331 60000 F876
7
8

I've tried various VLOOKUPs, and now struggling with INDEX MATCH. In each
case, when I place a formula into cell A3, A4, A5 etc, I can pull in the
desired data for the date in question. But when I try the same formula in
C3, C4, C5 to refer to the second date, the LOOKUP still wants to begin at
the begining of the selected range.

The current iteration I'm trying is

=IF(ISNA(INDEX((C2:C100),MATCH(1,($A$1=A2:A100)*(B2=B2:B100),0))),"",(INDEX((C20:C100),MATCH(1,($A$1=A2:A100)*(B2=B2:B100),0))))


Is what I want to do even possible in Excel? Do not want to go to the "dark
side", and use Access.
What am I missing?

 

[Max]

One formulae set up to deliver as desired:

Illustrated in this sample:
http://freefilehosting.net/download/44f0m
Index n match multiple results.xls

Construct:
Source data as posted assumed in Sheet1, in row 2 down

In another sheet,
Input date of interest will be in B1
In A3:
=IF(Sheet1!$A2="","",IF(Sheet1!$A2=B$1,ROW(),""))
Leave A1:A2 empty

In B3:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(Sheet1!$C:$C,SMALL(A:A,ROWS($1:1))-1))

In C3:
=IF(ROWS($1:1)>COUNT(A:A),"",INDEX(Sheet1!$B:$B,SMALL(A:A,ROWS($1:1))-1))
Copy A3:C3 down to cover the max expected extent of source data in Sheet1.
Minimize col A. Cols B and C returns results sought for the input date in B1.
Then just copy entire cols A to C when its all dressed up/completed, paste
into adjacent cols E to G, I to K, etc to "replicate" as many other similar
"enquiry" ranges as desired.

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