Using Index & Match?

B

be26268

New Member
Joined
Apr 28, 2010
Messages
27
Hi,

Please see below regarding the problem. Your time and help is much appreciated.

Kevin

<table border="0" cellpadding="0" cellspacing="0" width="783"><col style="mso-width-source:userset;mso-width-alt:5046;width:104pt" width="138"> <col style="mso-width-source:userset;mso-width-alt:950;width:20pt" width="26"> <col style="mso-width-source:userset;mso-width-alt:5229;width:107pt" width="143"> <col style="mso-width-source:userset;mso-width-alt:950;width:20pt" width="26"> <col style="mso-width-source:userset;mso-width-alt:3401;width:70pt" width="93"> <col style="mso-width-source:userset;mso-width-alt:950;width:20pt" width="26"> <col style="mso-width-source:userset;mso-width-alt:3401;width:70pt" width="93"> <col style="mso-width-source:userset;mso-width-alt:950;width:20pt" width="26"> <col style="mso-width-source:userset;mso-width-alt:3401;width:70pt" width="93"> <col style="mso-width-source:userset;mso-width-alt:950;width:20pt" width="26"> <col style="mso-width-source:userset;mso-width-alt:3401;width:70pt" width="93"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:104pt" width="138" height="20">Raw Data</td> <td class="xl65" style="width:20pt" width="26">
</td> <td class="xl65" style="width:107pt" width="143">
</td> <td class="xl65" style="width:20pt" width="26">
</td> <td class="xl65" style="width:70pt" width="93">
</td> <td class="xl65" style="width:20pt" width="26">
</td> <td class="xl65" style="width:70pt" width="93">
</td> <td class="xl65" style="width:20pt" width="26">
</td> <td class="xl65" style="width:70pt" width="93">
</td> <td class="xl65" style="width:20pt" width="26">
</td> <td class="xl65" style="width:70pt" width="93">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:104pt" width="138" height="20">Order #</td> <td class="xl65" style="width:20pt" width="26">
</td> <td class="xl66" style="width:107pt" width="143">Charge Type</td> <td class="xl66" style="width:20pt" width="26">
</td> <td class="xl66" style="width:70pt" width="93">$</td> <td class="xl66" style="width:20pt" width="26">
</td> <td class="xl65" style="width:70pt" width="93">
</td> <td class="xl65" style="width:20pt" width="26">
</td> <td class="xl65" style="width:70pt" width="93">
</td> <td class="xl65" style="width:20pt" width="26">
</td> <td class="xl65" style="width:70pt" width="93">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">5824</td> <td>
</td> <td class="xl67">Commission</td> <td class="xl67">
</td> <td class="xl67">7.88</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">5824</td> <td>
</td> <td class="xl67">Principal</td> <td class="xl67">
</td> <td class="xl67">-98.53</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">5824</td> <td>
</td> <td class="xl67">Shipping</td> <td class="xl67">
</td> <td class="xl67">-15.3</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">5824</td> <td>
</td> <td class="xl67">ShippingHB</td> <td class="xl67">
</td> <td class="xl67">1.22</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">8651</td> <td>
</td> <td class="xl67">Commission</td> <td class="xl67">
</td> <td class="xl67">0.75</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">8651</td> <td>
</td> <td class="xl67">Principal</td> <td class="xl67">
</td> <td class="xl67">-5</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">8254</td> <td>
</td> <td class="xl67">Commission</td> <td class="xl67">
</td> <td class="xl67">14.09</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">8254</td> <td>
</td> <td class="xl67">Principal</td> <td class="xl67">
</td> <td class="xl67">-93.9</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">6237</td> <td>
</td> <td class="xl67">Commission</td> <td class="xl67">
</td> <td class="xl67">13.2</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">6237</td> <td>
</td> <td class="xl67">Principal</td> <td class="xl67">
</td> <td class="xl67">-87.99</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">5451</td> <td>
</td> <td class="xl67">Commission</td> <td class="xl67">
</td> <td class="xl67">4.3</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">5451</td> <td>
</td> <td class="xl67">Principal</td> <td class="xl67">
</td> <td class="xl67">-53.69</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">5451</td> <td>
</td> <td class="xl67">RefundCommission</td> <td class="xl67">
</td> <td class="xl67">-1</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">5451</td> <td>
</td> <td class="xl67">Shipping</td> <td class="xl67">
</td> <td class="xl67">-8.5</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">5451</td> <td>
</td> <td class="xl67">ShippingHB</td> <td class="xl67">
</td> <td class="xl67">0.68</td> <td class="xl67">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Expected Result</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height:30.0pt" height="40"> <td class="xl65" style="height:30.0pt;width:104pt" width="138" height="40">Order #</td> <td class="xl65" style="width:20pt" width="26">
</td> <td class="xl68" style="width:107pt" width="143">Principal</td> <td class="xl68" style="width:20pt" width="26">
</td> <td class="xl68" style="width:70pt" width="93">Shipping</td> <td class="xl68" style="width:20pt" width="26">
</td> <td class="xl68" style="width:70pt" width="93">ShippingHB</td> <td class="xl68" style="width:20pt" width="26">
</td> <td class="xl68" style="width:70pt" width="93">Commission</td> <td class="xl68" style="width:20pt" width="26">
</td> <td class="xl68" style="width:70pt" width="93">RefundCommission</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">5824</td> <td>
</td> <td class="xl69">-98.53</td> <td class="xl69">
</td> <td class="xl67">-15.3</td> <td class="xl67">
</td> <td class="xl67">1.22</td> <td class="xl67">
</td> <td class="xl67">7.88</td> <td class="xl67">
</td> <td class="xl67">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">8651</td> <td>
</td> <td class="xl67">-5</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">0.75</td> <td class="xl67">
</td> <td class="xl67">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">8254</td> <td>
</td> <td class="xl67">-93.9</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">14.09</td> <td class="xl67">
</td> <td class="xl67">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">6237</td> <td>
</td> <td class="xl67">-87.99</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">13.2</td> <td class="xl67">
</td> <td class="xl67">
</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">5451</td> <td>
</td> <td class="xl67">-53.69</td> <td class="xl67">
</td> <td class="xl67">-8.5</td> <td class="xl67">
</td> <td class="xl67">0.68</td> <td class="xl67">
</td> <td class="xl67">4.3</td> <td class="xl67">
</td> <td class="xl67">-1</td> </tr> </tbody></table>
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
How about a pivot table? Or is index/match a necessity?

Order number as rows, charge type as columns and Sum of $ as data.
 
Upvote 0
try this
<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 /><col /><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><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Order #</td><td style=";">Charge Type</td><td style=";">$</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Order #</td><td style=";">Principal</td><td style=";">Shipping</td><td style=";">ShippingHB</td><td style=";">Commission</td><td style=";">RefundCommission</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">5824</td><td style=";">Commission</td><td style="text-align: right;;">7.88</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5824</td><td style="text-align: right;;">-98.53</td><td style="text-align: right;;">-15.3</td><td style="text-align: right;;">1.22</td><td style="text-align: right;;">7.88</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">5824</td><td style=";">Principal</td><td style="text-align: right;;">-98.53</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8651</td><td style="text-align: right;;">-5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0.75</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">5824</td><td style=";">Shipping</td><td style="text-align: right;;">-15.3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">8254</td><td style="text-align: right;;">-93.9</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">14.09</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">5824</td><td style=";">ShippingHB</td><td style="text-align: right;;">1.22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">6237</td><td style="text-align: right;;">-87.99</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">13.2</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">8651</td><td style=";">Commission</td><td style="text-align: right;;">0.75</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5451</td><td style="text-align: right;;">-53.69</td><td style="text-align: right;;">-8.5</td><td style="text-align: right;;">0.68</td><td style="text-align: right;;">4.3</td><td style="text-align: right;;">-1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">8651</td><td style=";">Principal</td><td style="text-align: right;;">-5</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><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;">8</td><td style="text-align: right;;">8254</td><td style=";">Commission</td><td style="text-align: right;;">14.09</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><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="text-align: right;;">8254</td><td style=";">Principal</td><td style="text-align: right;;">-93.9</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><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;">10</td><td style="text-align: right;;">6237</td><td style=";">Commission</td><td style="text-align: right;;">13.2</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><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;">11</td><td style="text-align: right;;">6237</td><td style=";">Principal</td><td style="text-align: right;;">-87.99</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><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;">12</td><td style="text-align: right;;">5451</td><td style=";">Commission</td><td style="text-align: right;;">4.3</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><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;">13</td><td style="text-align: right;;">5451</td><td style=";">Principal</td><td style="text-align: right;;">-53.69</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><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;">14</td><td style="text-align: right;;">5451</td><td style=";">RefundCommission</td><td style="text-align: right;;">-1</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><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;">15</td><td style="text-align: right;;">5451</td><td style=";">Shipping</td><td style="text-align: right;;">-8.5</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><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;">16</td><td style="text-align: right;;">5451</td><td style=";">ShippingHB</td><td style="text-align: right;;">0.68</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><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></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">Sheet6</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>Array 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">G2</th><td style="text-align:left">{=SUM(<font color="Blue">IF(<font color="Red">$A$2:$A$16=$F2,IF(<font color="Green">$B$2:$B$16=G$1,$C$2:$C$16</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Upvote 0
Hi Yahya,

I couldn't make the formula work (most likely due to my poor Excel skills). I will try to use the pivot table. Thanks for your help.

Kevin
 
Upvote 0
be26268 said:
Hi Yahya,

I couldn't make the formula work (most likely due to my poor Excel skills). I will try to use the pivot table. Thanks for your help.

Kevin
Click to expand...
why
did you forget to press Ctrl+Shift+Enter not just Enter
 
Upvote 0
Like I said it was due to my poor Excel skills. Yes it worked out fine.
Thanks again Yahya.

Kevin
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
R
  • Solved
Failed in order to derive Row number or Value in cell of particular Row Using Vlookup and Index... Match
2
Replies
10
Views
646
RapchikM
R
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
920
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M

Forum statistics

Threads
1,224,522
Messages
6,179,297
Members
452,903
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top