Difficult Lookup problem

[FrigidDigit]

I have a difficult (for me anyway) lookup problem which I hope someone can help me with.

I have a list of data in a worksheet in the following layout:

Company Name;File Name;Contract Number;Contract Modification #;Contract Date

I have another summary sheet that draws information from this list of data in this format

Company Original Contract Mod 01 Mod 02
XYZ 2004/06/01 2004/06/01 2004/06/01


I need a lookup function to lookup the company name in the list of date, lookup the ConMod number based on the column the formula is in and return the contract date.

Is this possible? I have spent most of last night experimenting with index,vlookup match etc but failed to find a solution. I would really appreciate any help.

Thanks

FD

 

[GlennUK]

A PivotTable might do it.

Have you tried doing a PivotTable? Here's one I did: ( did a Sum on date ... but as only one date per cell of the table will be in place, it's fine. Formatted the table by going into PivotTable field settings, and clicking on Number. )

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Company	Filename	Cont	Mod	Date		Sum of Date		Mod
2	Funlop	myf1	3124	0	01-Apr		Company	Cont	0	1	2
3	Funlop	myf1	3124	1	03-May		Funlop	3124	1-Apr	3-May	17-Jul
4	Funlop	myf1	3124	2	17-Jul		Marx	1475	24-Apr	5-Jun	7-Aug
5	Marx	myf2	1475	0	24-Apr		Firebone	824	1-Jan	23-Mar	30-Apr
6	Marx	myf2	1475	1	05-Jun		Cadmarys	3656	12-Feb	25-Mar	13-Sep
7	Marx	myf2	1475	2	07-Aug
8	Firebone	myf3	824	0	01-Jan
9	Firebone	myf3	824	1	23-Mar
10	Firebone	myf3	824	2	30-Apr
11	Cadmarys	myf4	3656	0	12-Feb
12	Cadmarys	myf4	3656	1	25-Mar
13	Cadmarys	myf4	3656	2	13-Sep
14
Sheet1

 

[fairwinds]

Hi,

Try:

=INDEX($E$2:$E$7,MATCH($G3&H$2,$A$2:$A$7&$D$2:$D$7,0))

Confirmed with Ctrl + shift + enter in H3 then dragged right / down.

Book2
	A	B	C	D	E	F	G	H	I	J
1	Company Name	File Name	Contract Number	Contract Modification #	Contract Date			Original Contract	Mod	Mod
2	XYZ	1	100	Original	2002-10-10		Company	Original	1	2
3	AAA	2	101	Original	2002-10-11		XYZ	2002-10-10	2002-10-12	2002-10-15
4	XYZ	3	102	1	2002-10-12		AAA	2002-10-11	2002-10-13	2002-10-14
5	AAA	4	103	1	2002-10-13
6	AAA	5	104	2	2002-10-14
7	XYZ	6	105	2	2002-10-15
Sheet1

 

[FrigidDigit]

Thanks for the response guys!

Appreciate the help. I won't be using a pivot table because of the strictly specified layout, I will be experimenting with FW's formula.

regards

FD