=index(range,match,match) problems

[jiminchina]

Mr Excel members,

I'm stuck. Let me explain my problem. I want to create a production schedule spreadsheet with the amounts needed per month. See below:

Part Number Jan-11 Feb-11 Mar-11 Apr-11
Part 100 500 485 438 505
Part 101 175 170 153 177
Part 102 350 340 306 354
Part 103 890 863 779 899

For you info: Part Number = A3
899 = E7

I want to write a formula in a box that can look up a part number of my choice and also look up a month I choose and return the relevant production quantity.

In cell B10 I will write the part number I want to look up.
In cell B11 I will write the date I want.
In cell B12 the relevant quantity should be returned (where the formula should go).

So far I have tried this and it kind of works but not for the part 100 or for Jan-11:

=INDEX(A3:E7,MATCH(B10,A3:A7),MATCH(B11,A3:E3))

This is an example from Excel 2010 for Dummies. I can't work out what is going wrong. Any suggestions?

Cheers,

Jim

 

[Haseeb Avarakkan]

Try;
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Part Number</td><td style="text-align: right;;">11-Jan</td><td style="text-align: right;;">11-Feb</td><td style="text-align: right;;">11-Mar</td><td style="text-align: right;;">11-Apr</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Part 100</td><td style="text-align: right;;">500</td><td style="text-align: right;;">485</td><td style="text-align: right;;">438</td><td style="text-align: right;;">505</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Part 101</td><td style="text-align: right;;">175</td><td style="text-align: right;;">170</td><td style="text-align: right;;">153</td><td style="text-align: right;;">177</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Part 102</td><td style="text-align: right;;">350</td><td style="text-align: right;;">340</td><td style="text-align: right;;">306</td><td style="text-align: right;;">354</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Part 103</td><td style="text-align: right;;">890</td><td style="text-align: right;;">863</td><td style="text-align: right;;">779</td><td style="text-align: right;;">899</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Part 100</td><td style="text-align: right;;"></td><td style=";">Part 103</td><td style="text-align: right;;"></td><td style="text-align: right;;">102</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Jan</td><td style="text-align: right;;"></td><td style=";">Apr</td><td style="text-align: right;;"></td><td style=";">Mar</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">500</td><td style="text-align: right;;"></td><td style="text-align: right;;">899</td><td style="text-align: right;;"></td><td style="text-align: right;;">306</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A11</th><td style="text-align:left">=INDEX(<font color="Blue">B4:E7,MATCH(<font color="Red">A9,A4:A7,0</font>),MONTH(<font color="Red">1&A10</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C11</th><td style="text-align:left">=VLOOKUP(<font color="Blue">C9,A4:E7,MONTH(<font color="Red">1&C10</font>)+1,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E11</th><td style="text-align:left">=VLOOKUP(<font color="Blue">"*"&E9,A4:E7,MONTH(<font color="Red">1&E10</font>)+1,0</font>)</td></tr></tbody></table></td></tr></table><br />
Note: A10, C10 & E10 are not dates, just months entered as text

 

[Dryver14]

you are almost there with your formula.

=index(B3:E7),match(B10,A3:A7,0),match(B11,A2:A5,0))

The index should only be the return values match the row against answer row and the column against the dates only

 

[jiminchina]

Haseeb,

Thanks for you help. You helped me figure it out. I think the textbook has a problem as it told me to include the headings and rows in the fields. Studying you formulas led me to try it without the row headings or the column headings. This formula works:

=INDEX(B4:E7,MATCH(B10,A4:A7),MATCH(B11,B3:E3))

Thanks for your help with this. Really appreciate it.

Regards,

Jim

 

[Dryver14]

I'm sure I typed B3:E3 !!!! maybe these 12 hr shifts are too much

=(12hrshift+excessstress)*(fedup)/Wrong answer