vlookup - one to many relationship problem

[glorber]

Hi,
I have two large excel worksheets (A and B) each containing columns of 'ID' and 'date' and other columns that differ between the files. Each spreadsheet has multiple entries of a given ID and identicle matching dates. I would like to perform a search based on a given ID and date in file A and find the matching ID and date in file B. then extract a third value from B from the appropriate row.
I thought of using vlookup:

if(vlookup(IDInA,RangeInB,colOfDateInB,false)=DateinA,vlookup(...))

The problem is that vlookup stops searching after the first match (ID and Date) between the files. Any way around that?

I Know it sound copmlicated but I hope I managed to explain my problem.
Thanks.

 

[xenou]

If you have identical ID's and Dates then VLookup will not get past the first match. It seems to me that if you have more than one ID-Date combination that you need some other means of matching...first and foremost, if ID-Date combinations are being duplicated what makes one "right" and the other "wrong"? To Excel (and so far to me), they are the same.

Regards.

 

[Aladin Akyurek]

Hi,
I have two large excel worksheets (A and B) each containing columns of 'ID' and 'date' and other columns that differ between the files. Each spreadsheet has multiple entries of a given ID and identicle matching dates. I would like to perform a search based on a given ID and date in file A and find the matching ID and date in file B. then extract a third value from B from the appropriate row.
I thought of using vlookup:

if(vlookup(IDInA,RangeInB,colOfDateInB,false)=DateinA,vlookup(...))

The problem is that vlookup stops searching after the first match (ID and Date) between the files. Any way around that?

I Know it sound copmlicated but I hope I managed to explain my problem.
Thanks.

aaSmallIfAsLookup glorber.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	Date	Value								On sheet A
2	x-404	7/8/2007	6								x-404	x-909
3	x-404	7/8/2007	7								7/8/2007	8/13/2007
4	x-708	8/1/2007	29								3	2
5	x-909	8/13/2007	0								Value	Value
6	x-007	9/6/2007	6								6	0
7	x-909	8/13/2007	21								7	21
8	x-505	9/25/2007	12								12
9	x-404	7/8/2007	12
10
B

K4, copied across:

=SUMPRODUCT(--(B!$A$2:$A$9=K$2),--(B!$B$2:$B$9=K$3))

K6:

Control+shift+enter...

=IF(ROWS(K$6:K6)<=K$4,INDEX(B!$C$2:$C$9,SMALL(IF(B!$A$2:$A$9=K$2,IF(B!$B$2:$B$9=K$3,ROW($A$2:$A$9)-ROW($A$2)+1)),ROWS(K$6:K6))),"")

and copy across and down.

 

[xenou]

Wow. I appreciate this very much, Aladin. Regards.