Index/Match in combination with looking up a date

Hechan

New Member
Joined
Jul 25, 2007
Messages
6
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
Joined
May 28, 2005
Messages
57,244
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top