# Index/Match in combination with looking up a date

#### Hechan

##### New Member
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
ABCDEFGHIJK
1Sheet1Sheet2
2CountryModelInvoicedateCORRECTPRICEModelCountryEffectivedatePrice
3EnglandModelA01/03/2007ModelAEngland01/01/20071000
4EnglandModelA15/03/2007ModelAEngland01/04/20071200
5EnglandModelA02/04/2007ModelAIreland01/01/2007999
6EnglandModelB23/07/2007ModelAIreland18/03/20071149
7EnglandModelA02/01/2007ModelAIreland01/06/20071299
8IrelandModelA31/03/2007ModelBEngland01/01/20072000
9IrelandModelB15/05/2007ModelBIreland01/01/20071599
10IrelandModelA27/03/2007ModelBEngland01/04/20072200
11IrelandModelA01/07/2007ModelBIreland01/04/20072199
12IrelandModelB23/05/2007
13
Sheet1

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Peter_SSs

##### MrExcel MVP, Moderator
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
ABCD
1Sheet2
2ModelCountryEffectivedatePrice
3ModelAEngland1/01/20071000
4ModelAEngland1/04/20071200
5ModelAIreland1/01/2007999
6ModelAIreland18/03/20071149
7ModelAIreland1/06/20071299
8ModelBEngland1/01/20072000
9ModelBIreland1/01/20071599
10ModelBEngland1/04/20072200
11ModelBIreland1/04/20072199
Sheet2

Excel Workbook
ABCD
1Sheet1
2CountryModelInvoicedateCORRECTPRICE
3EnglandModelA1/03/20071000
4EnglandModelA15/03/20071000
5EnglandModelA2/04/20071200
6EnglandModelB23/07/20072200
7EnglandModelA2/01/20071000
8IrelandModelA31/03/20071149
9IrelandModelB15/05/20072199
10IrelandModelA27/03/20071149
11IrelandModelA1/07/20071299
12IrelandModelB23/05/20072199
Sheet1

#### Hechan

##### New Member
Works brilliantly, thanks very much for your help.

Replies
5
Views
403
Replies
12
Views
654
Replies
5
Views
191
Replies
3
Views
147
Replies
3
Views
88

1,181,054
Messages
5,927,855
Members
436,573
Latest member
CMR237

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back