Double lookup

[kgkev]

I am trying to automatically calculate agents commisions.

i have one sheet with all my agents.

This is a list of Countries, The "brand" they sell and their account number./

The Brand is a number either, 21,31,41 or 51
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Angola</td><td style="text-align: right;;">51</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">benin</td><td style="text-align: right;;">21</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">benin</td><td style="text-align: right;;">31</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">benin</td><td style="text-align: right;;">41</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">benin</td><td style="text-align: right;;">51</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Bosnia</td><td style="text-align: right;;">41</td><td style=";">ZZ/REA</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Bulgar</td><td style="text-align: right;;">41</td><td style=";">ZZ/ELSY</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Burkin</td><td style="text-align: right;;">21</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Burkin</td><td style="text-align: right;;">31</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">Burkin</td><td style="text-align: right;;">41</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">Burkin</td><td style="text-align: right;;">51</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">Camero</td><td style="text-align: right;;">21</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">Camero</td><td style="text-align: right;;">31</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">Camero</td><td style="text-align: right;;">41</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">Camero</td><td style="text-align: right;;">51</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">Croati</td><td style="text-align: right;;">21</td><td style=";">XX/IDEF</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">Croati</td><td style="text-align: right;;">31</td><td style=";">XX/IDEF</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">Croati</td><td style="text-align: right;;">41</td><td style=";">XX/IDEF</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">Croati</td><td style="text-align: right;;">51</td><td style=";">XX/IDEF</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style=";">Czech</td><td style="text-align: right;;">21</td><td style=";">ZZ/ACE</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style=";">Czech</td><td style="text-align: right;;">31</td><td style=";">ZZ/ACE</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style=";">Czech</td><td style="text-align: right;;">41</td><td style=";">ZZ/ACE</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style=";">Czech</td><td style="text-align: right;;">51</td><td style=";">ZZ/ACE</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style=";">Egypt</td><td style="text-align: right;;">21</td><td style=";">ZZ/MID</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style=";">Egypt</td><td style="text-align: right;;">31</td><td style=";">ZZ/MID</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style=";">Egypt</td><td style="text-align: right;;">41</td><td style=";">ZZ/MID</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style=";">Egypt</td><td style="text-align: right;;">51</td><td style=";">ZZ/MID</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style=";">Gambia</td><td style="text-align: right;;">21</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style=";">Gambia</td><td style="text-align: right;;">31</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style=";">Gambia</td><td style="text-align: right;;">41</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style=";">Gambia</td><td style="text-align: right;;">51</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style=";">Ghana</td><td style="text-align: right;;">21</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style=";">Ghana</td><td style="text-align: right;;">31</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style=";">Ghana</td><td style="text-align: right;;">41</td><td style=";">XX/INTEC</td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style=";">Ghana</td><td style="text-align: right;;">51</td><td style=";">XX/INTEC</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 #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Agents</p><br /><br />


I then have a ledger table that shows all sales, here I also have the Country and brand.

How do I match them up so that a sale to Angola with brand "51" Brings back the agent XX/INTEC but a sale to Angola with brand "21" Doesn't?

Ledger page looks like this

With Country in "H" and Brand in "J"

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></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><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;;">journal_number</td><td style="font-weight: bold;;">journal_date</td><td style="font-weight: bold;;">journal_desc</td><td style="font-weight: bold;;">journal_amount</td><td style="font-weight: bold;;">order_no</td><td style="font-weight: bold;;">element1</td><td style="font-weight: bold;;">territory</td><td style="font-weight: bold;;">customer</td><td style="font-weight: bold;;">Brand</td><td style="font-weight: bold;;">Type</td><td style="font-weight: bold;;">Spares amount</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">OP/I042647</td><td style="text-align: right;;">30/04/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;">194.47</td><td style=";">NS26562</td><td style=";">21-200-110</td><td style=";">SWEDEN</td><td style="text-align: right;;">00221</td><td style="text-align: right;;">21</td><td style="text-align: right;;">110</td><td style="text-align: right;;">194.47</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">OP/I042651</td><td style="text-align: right;;">30/04/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;">66.98</td><td style=";">NS26560</td><td style=";">21-200-110</td><td style=";">POLAND</td><td style=";">PO-PFZ-1</td><td style="text-align: right;;">21</td><td style="text-align: right;;">110</td><td style="text-align: right;;">66.98</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">OP/I042663</td><td style="text-align: right;;">05/05/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;">441.98</td><td style=";">NS26555</td><td style=";">21-200-110</td><td style=";">GERMAN</td><td style=";">00082B</td><td style="text-align: right;;">21</td><td style="text-align: right;;">110</td><td style="text-align: right;;">441.98</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">OP/I042664</td><td style="text-align: right;;">05/05/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;">39.26</td><td style=";">NS26564</td><td style=";">21-200-110</td><td style=";">GERMAN</td><td style=";">00082B</td><td style="text-align: right;;">21</td><td style="text-align: right;;">110</td><td style="text-align: right;;">39.26</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">OP/I042667</td><td style="text-align: right;;">05/05/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;">19.96</td><td style=";">NS26565</td><td style=";">21-200-110</td><td style=";">UK</td><td style="text-align: right;;">00007</td><td style="text-align: right;;">21</td><td style="text-align: right;;">110</td><td style="text-align: right;;">19.96</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">OP/I042669</td><td style="text-align: right;;">06/05/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;">189.37</td><td style=";">NS26553</td><td style=";">21-200-110</td><td style=";">USA</td><td style="text-align: right;;">00081</td><td style="text-align: right;;">21</td><td style="text-align: right;;">110</td><td style="text-align: right;;">189.37</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">OP/I042676</td><td style="text-align: right;;">06/05/2011</td><td style="text-align: right;;"></td><td style="text-align: right;;">562.09</td><td style=";">NS26523</td><td style=";">21-200-110</td><td style=";">ROMANI</td><td style=";">00166A</td><td style="text-align: right;;">21</td><td style="text-align: right;;">110</td><td style="text-align: right;;">562.09</td></tr></tbody></table><p style="width:6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Commission</p><br /><br />

 

[jasonb75]

Use a helper column in Agents

Filled down =A9&B9

Then use =index(Agents!$C$9:$C$43,match(H7&J7,Agents!Helper,0))

 

[RoryA]

=LOOKUP(2,1/((Sheet2!$A$9:$A$43=H7)*(Sheet2!$B$9:$B$43=J7)),Sheet2!$C$9:$C$43)