Matching a data from two tables within a worksheet

**beni12** · 04-20-2011, 09:13 AM

Hi guys

I`ve got a "small" problem with comparing the colums in a worksheet. I`ve two tables with data in Excel. The first one consist of 4 columns: index, year, month and day. In the second tabel I`ve got three columns: the same index, the date (year, month, day) and a stock price. What I cannot do is to match the particular index in the first table simultaneously with the date and the price from the second one.

I would be extremely grateful to you if you can give an idea how I can do that. Thanks a loooot in advance!!
Beni

P.S: Here`s a preliminary worksheet so that you can see what exactly I`ve described you before that :-)

**rylo** · 04-20-2011, 08:54 PM

Hi

Is it a problem with your example file, or do you really have duplicates? If so, then how do you want to handle duplicate items?

rylo

**beni12** · 04-21-2011, 03:42 AM

Hello everyone,

I`ve actually made a mistake in the first attached file......so here`s the right one :-)
What I want to do is to match the particular date with the particular price for this day. It`s actually not a duplicate because I`ve the same date several times which in fact makes things harder. In the actual data I`ve got the firms (in the worksheet marked as INDEX) are 130 and in total the cells for the dates are about 65.000.....pretty much to be considered separately. Each of the firm has a different price at each day. What I exactly cannot do is to match the different prices from the second table simultaneously with the particular date and the particular firm.....I`ve no idea how to compare the two factors at once so that at the end the right price appears in a separate column.
I hope that someone of you could help me...because what I`ve tried until now didn`work out. Thanks in advance :-)

**rylo** · 04-22-2011, 06:24 PM

Hi

Tabelle1!E2: =INDEX(Tabelle2!C:C,SUMPRODUCT(--(Tabelle2!$A$2:$A$55=Tabelle1!A2),--(Tabelle2!$B$2:$B$55=DATEVALUE(D2&"/"&C2&"/"&B2)),ROW(Tabelle2!$B$2:$B$55)))

Copy down as required.

rylo