| Home |
| Search |
| Today's Posts |
|
#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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup/Index/Match HELP! | Excel Discussion (Misc queries) | |||
Threaded Mode
