| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF ARRAY LOOKUP ??? Help Please
I need to determine the price of a specific part during a specific period -
help please! The part# and the invoice date drives the price. RECEIPTS Invoice Part# Date QTY AA 12/01/05 2 AA 11/15/05 12 BB 02/01/06 6 CC 11/15/05 3 PRICING Part No. Price price chng date AA .15 10/01/05 AA .18 10/28/05 AA .22 12/22/05 AA .31 02/05/06 BB .16 10/20/05 BB .28 12/18/05 BB .78 01/22/06 CC .38 07/12/05 CC .91 11/16/05 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF ARRAY LOOKUP ??? Help Please
=INDEX(Sheet2!B2:B200,MATCH(A2&B2,Sheet2!A2:A200&S heet2!C2:C200,0))
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ellen G." <Ellen wrote in message ... I need to determine the price of a specific part during a specific period - help please! The part# and the invoice date drives the price. RECEIPTS Invoice Part# Date QTY AA 12/01/05 2 AA 11/15/05 12 BB 02/01/06 6 CC 11/15/05 3 PRICING Part No. Price price chng date AA .15 10/01/05 AA .18 10/28/05 AA .22 12/22/05 AA .31 02/05/06 BB .16 10/20/05 BB .28 12/18/05 BB .78 01/22/06 CC .38 07/12/05 CC .91 11/16/05 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF ARRAY LOOKUP ??? Help Please
Thanks Bob! :)
"Bob Phillips" wrote: =INDEX(Sheet2!B2:B200,MATCH(A2&B2,Sheet2!A2:A200&S heet2!C2:C200,0)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ellen G." <Ellen wrote in message ... I need to determine the price of a specific part during a specific period - help please! The part# and the invoice date drives the price. RECEIPTS Invoice Part# Date QTY AA 12/01/05 2 AA 11/15/05 12 BB 02/01/06 6 CC 11/15/05 3 PRICING Part No. Price price chng date AA .15 10/01/05 AA .18 10/28/05 AA .22 12/22/05 AA .31 02/05/06 BB .16 10/20/05 BB .28 12/18/05 BB .78 01/22/06 CC .38 07/12/05 CC .91 11/16/05 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF ARRAY LOOKUP ??? Help Please
I TRIED & FAILED, COULD U HELP (COPY RECEIPTS AT A1)
=INDEX(G2:G11,MATCH(A3&B3,E2:E11&F2:F11,0)) RECEIPTS PRICING A B C D F G PART# DATE QTY PRICE PART# DATE RATE AA 01-12-2005 2 #VALUE! AA 01-10-2005 0.15 AA 15-11-2005 12 #VALUE! AA 28-10-2005 0.18 BB 01-02-2006 6 #VALUE! AA 22-12-2005 0.22 CC 15-11-2005 13 #VALUE! AA 05-02-2006 0.31 BB 20-10-2005 0.16 BB 18-12-2005 0.28 BB 22-01-2006 0.78 CC 07-12-2005 0.38 CC 16-11-2005 0.91 "Bob Phillips" wrote: =INDEX(Sheet2!B2:B200,MATCH(A2&B2,Sheet2!A2:A200&S heet2!C2:C200,0)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ellen G." <Ellen wrote in message ... I need to determine the price of a specific part during a specific period - help please! The part# and the invoice date drives the price. RECEIPTS Invoice Part# Date QTY AA 12/01/05 2 AA 11/15/05 12 BB 02/01/06 6 CC 11/15/05 3 PRICING Part No. Price price chng date AA .15 10/01/05 AA .18 10/28/05 AA .22 12/22/05 AA .31 02/05/06 BB .16 10/20/05 BB .28 12/18/05 BB .78 01/22/06 CC .38 07/12/05 CC .91 11/16/05 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF ARRAY LOOKUP ??? Help Please
Thanks Bob.
sorry I couldn't say that immediately. "Bob Phillips" wrote: I forgot to mention that it is an array formula, so it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Eddy Stan" <Eddy wrote in message ... I TRIED & FAILED, COULD U HELP (COPY RECEIPTS AT A1) =INDEX(G2:G11,MATCH(A3&B3,E2:E11&F2:F11,0)) RECEIPTS PRICING A B C D F G PART# DATE QTY PRICE PART# DATE RATE AA 01-12-2005 2 #VALUE! AA 01-10-2005 0.15 AA 15-11-2005 12 #VALUE! AA 28-10-2005 0.18 BB 01-02-2006 6 #VALUE! AA 22-12-2005 0.22 CC 15-11-2005 13 #VALUE! AA 05-02-2006 0.31 BB 20-10-2005 0.16 BB 18-12-2005 0.28 BB 22-01-2006 0.78 CC 07-12-2005 0.38 CC 16-11-2005 0.91 "Bob Phillips" wrote: =INDEX(Sheet2!B2:B200,MATCH(A2&B2,Sheet2!A2:A200&S heet2!C2:C200,0)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ellen G." <Ellen wrote in message ... I need to determine the price of a specific part during a specific period - help please! The part# and the invoice date drives the price. RECEIPTS Invoice Part# Date QTY AA 12/01/05 2 AA 11/15/05 12 BB 02/01/06 6 CC 11/15/05 3 PRICING Part No. Price price chng date AA .15 10/01/05 AA .18 10/28/05 AA .22 12/22/05 AA .31 02/05/06 BB .16 10/20/05 BB .28 12/18/05 BB .78 01/22/06 CC .38 07/12/05 CC .91 11/16/05 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF ARRAY LOOKUP ??? Help Please
Not a problem, it is just nice to get thanks.
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Eddy Stan" wrote in message ... Thanks Bob. sorry I couldn't say that immediately. "Bob Phillips" wrote: I forgot to mention that it is an array formula, so it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Eddy Stan" <Eddy wrote in message ... I TRIED & FAILED, COULD U HELP (COPY RECEIPTS AT A1) =INDEX(G2:G11,MATCH(A3&B3,E2:E11&F2:F11,0)) RECEIPTS PRICING A B C D F G PART# DATE QTY PRICE PART# DATE RATE AA 01-12-2005 2 #VALUE! AA 01-10-2005 0.15 AA 15-11-2005 12 #VALUE! AA 28-10-2005 0.18 BB 01-02-2006 6 #VALUE! AA 22-12-2005 0.22 CC 15-11-2005 13 #VALUE! AA 05-02-2006 0.31 BB 20-10-2005 0.16 BB 18-12-2005 0.28 BB 22-01-2006 0.78 CC 07-12-2005 0.38 CC 16-11-2005 0.91 "Bob Phillips" wrote: =INDEX(Sheet2!B2:B200,MATCH(A2&B2,Sheet2!A2:A200&S heet2!C2:C200,0)) -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Ellen G." <Ellen wrote in message ... I need to determine the price of a specific part during a specific period - help please! The part# and the invoice date drives the price. RECEIPTS Invoice Part# Date QTY AA 12/01/05 2 AA 11/15/05 12 BB 02/01/06 6 CC 11/15/05 3 PRICING Part No. Price price chng date AA .15 10/01/05 AA .18 10/28/05 AA .22 12/22/05 AA .31 02/05/06 BB .16 10/20/05 BB .28 12/18/05 BB .78 01/22/06 CC .38 07/12/05 CC .91 11/16/05 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array formula lookup | Excel Worksheet Functions | |||
| Lookup array? | Excel Worksheet Functions | |||
| Weighted avg of numbers not in contiguous row-ie array w lookup? | Excel Worksheet Functions | |||
| How do I lookup a value in a array that is not in ascending order | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
