Index/Match in combination with looking up a date

[Hechan]

Hi. Can anyone help with my little problem?

I have 2 worksheets as follows:
Sheet 1. A list of various units sold in various countries, with an invoice date;
Sheet 2. A list of prices for the various units in the various countries, with a date from which the price is effective.

Some prices have changed 3 or 4 times in the relevant period, so there are several entries in sheet 2 with different effective dates.

What I need to do is add a formula into Sheet 1 (column D below) which looks up the unit and the country, and picks whichever price was effective at the date of invoice, from Sheet 2.

For example, the prices in D3 and D4 should be 1000, but the price in D5 should be 1200. I can't figure out how to make the formula look at the dates correctly.

In my actual data there are >60,000 invoices and around 4,000 prices.

Any help is much appreciated....

Thanks, Heather

Mr Excel query.xls
	A	B	C	D	E	F	G	H	I	J	K
1	Sheet1							Sheet2
2	Country	Model	Invoicedate	CORRECTPRICE				Model	Country	Effectivedate	Price
3	England	ModelA	01/03/2007					ModelA	England	01/01/2007	1000
4	England	ModelA	15/03/2007					ModelA	England	01/04/2007	1200
5	England	ModelA	02/04/2007					ModelA	Ireland	01/01/2007	999
6	England	ModelB	23/07/2007					ModelA	Ireland	18/03/2007	1149
7	England	ModelA	02/01/2007					ModelA	Ireland	01/06/2007	1299
8	Ireland	ModelA	31/03/2007					ModelB	England	01/01/2007	2000
9	Ireland	ModelB	15/05/2007					ModelB	Ireland	01/01/2007	1599
10	Ireland	ModelA	27/03/2007					ModelB	England	01/04/2007	2200
11	Ireland	ModelA	01/07/2007					ModelB	Ireland	01/04/2007	2199
12	Ireland	ModelB	23/05/2007
13
Sheet1

 

[Peter_SSs]

Hechan

Welcome to the MrExcel board!

With such a large amount of data, this may cause the sheet to be quite slow, and there may be a simpler way, but see if this does what you want.

Excel Workbook
	A	B	C	D
1	Sheet2
2	Model	Country	Effectivedate	Price
3	ModelA	England	1/01/2007	1000
4	ModelA	England	1/04/2007	1200
5	ModelA	Ireland	1/01/2007	999
6	ModelA	Ireland	18/03/2007	1149
7	ModelA	Ireland	1/06/2007	1299
8	ModelB	England	1/01/2007	2000
9	ModelB	Ireland	1/01/2007	1599
10	ModelB	England	1/04/2007	2200
11	ModelB	Ireland	1/04/2007	2199
Sheet2

Excel Workbook
	A	B	C	D
1	Sheet1
2	Country	Model	Invoicedate	CORRECTPRICE
3	England	ModelA	1/03/2007	1000
4	England	ModelA	15/03/2007	1000
5	England	ModelA	2/04/2007	1200
6	England	ModelB	23/07/2007	2200
7	England	ModelA	2/01/2007	1000
8	Ireland	ModelA	31/03/2007	1149
9	Ireland	ModelB	15/05/2007	2199
10	Ireland	ModelA	27/03/2007	1149
11	Ireland	ModelA	1/07/2007	1299
12	Ireland	ModelB	23/05/2007	2199
Sheet1

 

[Hechan]

Works brilliantly, thanks very much for your help.