Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup/index/match - help!
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup/index/match - help!
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup/index/match - help!
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! In article , "Ellen G." wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup/index/match - help!
THANK YOU Domenic.... You have been extremely helpful! :)
"Domenic" wrote: 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! In article , "Ellen G." wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup/Index/Match HELP! | Excel Discussion (Misc queries) |