software

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 53Hi Ellen,

In order to retrieve date based values you should first of all set up a
single field that will act as your quot;index codequot; 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

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$9lt;=B2, Sheet2!$B$2:$B$9)))),0)
)

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

Hope this helps!

In article gt;,
quot;Ellen G.quot; gt; wrote:

gt; In the following I need it to return the quote/price that falls into the
gt; appropriate date range for a specific part number.
gt;
gt; I can't seem to get my formula attempts to work and I'd prefer to break-up
gt; the data into multiple worksheets.
gt;
gt; SHEET1
gt; colmn 1 colmn2 colmn3 colmn4
gt; part no. Recv date qty PRICE
gt; 0410101 1/17/04 36 ??
gt; 0184-99 1/02/06 38 ??
gt; 0225-99 12/01/05 68 ??
gt; 0184-99 11/01/05 65??
gt; 0184-99 10/01/05 38??
gt; 0225-99 09/01/05 56??
gt; 0184-99 08/01/05 65??
gt; 0189-99 07/01/05 53??
gt;
gt; SHEET2
gt; colmn1 colmn2 colmn3
gt; part No. Eff date. price
gt; 0410101 09/1/04 .028
gt; 0184-99 11/02/05 .138
gt; 0225-99 12/16/05 .0681
gt; 0184-99 11/28/05 .065
gt; 0184-99 1/02/06 .198
gt; 0225-99 09/01/05 56
gt; 0184-99 08/01/05 65
gt; 0184-99 07/01/05 53

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

quot;Domenicquot; wrote:

gt; Assumptions:
gt;
gt; Sheet2!A2:C9 contains your source data
gt;
gt; Sheet1!A2 contains the 'part number' of interest
gt;
gt; Sheet1!B2 contains the 'received date' of interest
gt;
gt; Formula:
gt;
gt; Sheet1!D2, copied down:
gt;
gt; =INDEX(Sheet2!$C$2:$C$9,MATCH(1,(Sheet2!$A$2:$A$9= A2)*(Sheet2!$B$2:$B$9=M
gt; AX(IF(Sheet2!$A$2:$A$9=A2,IF(Sheet2!$B$2:$B$9lt;=B2, Sheet2!$B$2:$B$9)))),0)
gt; )
gt;
gt; ....confirmed with CONTROL SHIFT ENTER, not just ENTER.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; quot;Ellen G.quot; gt; wrote:
gt;
gt; gt; In the following I need it to return the quote/price that falls into the
gt; gt; appropriate date range for a specific part number.
gt; gt;
gt; gt; I can't seem to get my formula attempts to work and I'd prefer to break-up
gt; gt; the data into multiple worksheets.
gt; gt;
gt; gt; SHEET1
gt; gt; colmn 1 colmn2 colmn3 colmn4
gt; gt; part no. Recv date qty PRICE
gt; gt; 0410101 1/17/04 36 ??
gt; gt; 0184-99 1/02/06 38 ??
gt; gt; 0225-99 12/01/05 68 ??
gt; gt; 0184-99 11/01/05 65??
gt; gt; 0184-99 10/01/05 38??
gt; gt; 0225-99 09/01/05 56??
gt; gt; 0184-99 08/01/05 65??
gt; gt; 0189-99 07/01/05 53??
gt; gt;
gt; gt; SHEET2
gt; gt; colmn1 colmn2 colmn3
gt; gt; part No. Eff date. price
gt; gt; 0410101 09/1/04 .028
gt; gt; 0184-99 11/02/05 .138
gt; gt; 0225-99 12/16/05 .0681
gt; gt; 0184-99 11/28/05 .065
gt; gt; 0184-99 1/02/06 .198
gt; gt; 0225-99 09/01/05 56
gt; gt; 0184-99 08/01/05 65
gt; gt; 0184-99 07/01/05 53
gt;