lookup/index/match - help!

[Guest]

In the following I need it to return the quote/price that falls into the
appropriate date range for a specific part number.

I can't seem to get my formula attempts to work and I'd prefer to break-up
the data into multiple worksheets.

SHEET1
colmn 1 colmn2 colmn3 colmn4
part no. Recv date qty PRICE
0410101 1/17/04 36 ??
0184-99 1/02/06 38 ??
0225-99 12/01/05 68 ??
0184-99 11/01/05 65 ??
0184-99 10/01/05 38 ??
0225-99 09/01/05 56 ??
0184-99 08/01/05 65 ??
0189-99 07/01/05 53 ??

SHEET2
colmn1 colmn2 colmn3
part No. Eff date. price
0410101 09/1/04 .028
0184-99 11/02/05 .138
0225-99 12/16/05 .0681
0184-99 11/28/05 .065
0184-99 1/02/06 .198
0225-99 09/01/05 56
0184-99 08/01/05 65
0184-99 07/01/05 53

 

[Guest]

Hi Ellen,

In order to retrieve date based values you should first of all set up a
single field that will act as your "index code" in order for Vlookup to work.
Something like
040110120060217, consisting of your product code concatenated with an ANSI
date (YYYYMMDD). Then sort your pricelist data on your index key. That way
you can test when either your code or your date changes.

I have found it useful to set the latest (current?) value with a 20991231
date, which can never be less than what you search for.

HTH

 

[Domenic]

Assumptions:

Sheet2!A2:C9 contains your source data

Sheet1!A2 contains the 'part number' of interest

Sheet1!B2 contains the 'received date' of interest

Formula:

Sheet1!D2, copied down:

=INDEX(Sheet2!$C$2:$C$9,MATCH(1,(Sheet2!$A$2:$A$9=A2)*(Sheet2!$B$2:$B$9=M
AX(IF(Sheet2!$A$2:$A$9=A2,IF(Sheet2!$B$2:$B$9<=B2,Sheet2!$B$2:$B$9)))),0)
)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Guest]

THANK YOU Domenic.... You have been extremely helpful!