Leafgreen
New Member
- Joined
- Jan 12, 2010
- Messages
- 39
Hello experts and TIA for your help. I'm telling you everything because giving only part might be more confusing. I want to add to a lookup formula that is making my head explode Here it is:
Below are the actual values in the ws "jos_vm_product".
<table style="border-collapse: collapse; width: 675pt;" border="0" cellpadding="0" cellspacing="0" width="902"><col style="width: 26pt;" width="141"> <col style="width: 63pt;" width="84"> <col style="width: 50pt;" span="9" width="67"> <col style="width: 56pt;" width="74"> <tbody><tr><td align="center" valign="top">
</td><td align="center" valign="top">A
</td><td align="center" valign="top">AG
</td><td align="center" valign="top">AH
</td><td align="center" valign="top">AI
</td><td align="center" valign="top">AJ
</td><td align="center" valign="top">AK
</td><td align="center" valign="top">AL
</td><td align="center" valign="top">AM
</td><td align="center" valign="top">AN
</td><td align="center" valign="top">AO
</td><td align="center" valign="top">AP
</td></tr><tr style="height: 15pt;" height="20"> <td valign="top">81
</td><td style="border-style: solid solid solid none; border-color: white white white -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 63pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="84">91
</td> <td style="border-style: solid solid solid none; border-color: white white white -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 50pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" width="67">2.45</td> <td style="border-style: solid solid solid none; border-color: white white white -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 50pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" width="67">3.75</td> <td style="border-style: solid solid solid none; border-color: white white white -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 50pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" width="67">4.25</td> <td style="border-style: solid solid solid none; border-color: white white white -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 50pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="67">
</td> <td style="border-style: solid solid solid none; border-color: white white white -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 50pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="67">
</td> <td style="border-style: solid solid solid none; border-color: white white white -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 50pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="67">
</td> <td style="border-style: solid none; border-color: white -moz-use-text-color; border-width: 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 50pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="67">
</td> <td style="width: 50pt;" align="right" width="67">5.99</td> <td style="width: 50pt;" align="right" width="67">7.99</td> <td style="width: 56pt;" align="right" width="74">8.98</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">82
</td><td style="border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">92
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right">1</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right">2</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right">3</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">
</td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color white; border-width: medium medium 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">
</td> <td align="right">4.95</td> <td align="right">5.95</td> <td align="right">7.94</td></tr></tbody></table>
In ws "commissions" I have two key needed values. Cell A181 = 1304. Cell E181 = 5.99.
Just like in the existing formula, we can use A181 to lookup the value in jos_vm_order_item column 5, and then use that value to find that row 81 above is the correct row in jos_vm_product.
Now, I would like to use commissions:E181 and lookup jos_vm_product:AN81 then get the value in jos_vm_product:AG81 (it's 2.45) and put it in commissions:F181.
For example, if commissions:E182 = 8.98 then F182 would be 4.25. And if commissions:E200 = 7.94, and A200 found that row 82 above is the correct row in jos_vm_product, then F200 would be 3
What's that formula? Can you integrate it into the formula above?
This formula is in the worksheet "commissions". I did it myself, and it's working fine now, but I'm having brainfreeze trying to add the following.=IF(LOOKUP(jos_vm_order_item!L257,{"C","P","S","X"},{"Paid","Unpaid","Shipped","Cancelled"})="Cancelled","Cancelled",IF(LOOKUP(jos_vm_order_item!L257,{"C","P","S","X"},{"Paid","Unpaid","Shipped","Cancelled"})="Unpaid","Unpaid",IF(VLOOKUP(VLOOKUP($A181,jos_vm_order_item!$1:$1048576,5,FALSE),jos_vm_product!$1:$1048576,32,FALSE)="x30",0.3*E181,VLOOKUP(VLOOKUP($A181,jos_vm_order_item!$1:$1048576,5,FALSE),jos_vm_product!$1:$1048576,32,FALSE)))*jos_vm_order_item!H257)
Below are the actual values in the ws "jos_vm_product".
<table style="border-collapse: collapse; width: 675pt;" border="0" cellpadding="0" cellspacing="0" width="902"><col style="width: 26pt;" width="141"> <col style="width: 63pt;" width="84"> <col style="width: 50pt;" span="9" width="67"> <col style="width: 56pt;" width="74"> <tbody><tr><td align="center" valign="top">
</td><td align="center" valign="top">A
</td><td align="center" valign="top">AG
</td><td align="center" valign="top">AH
</td><td align="center" valign="top">AI
</td><td align="center" valign="top">AJ
</td><td align="center" valign="top">AK
</td><td align="center" valign="top">AL
</td><td align="center" valign="top">AM
</td><td align="center" valign="top">AN
</td><td align="center" valign="top">AO
</td><td align="center" valign="top">AP
</td></tr><tr style="height: 15pt;" height="20"> <td valign="top">81
</td><td style="border-style: solid solid solid none; border-color: white white white -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 63pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="84">91
</td> <td style="border-style: solid solid solid none; border-color: white white white -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 50pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" width="67">2.45</td> <td style="border-style: solid solid solid none; border-color: white white white -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 50pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" width="67">3.75</td> <td style="border-style: solid solid solid none; border-color: white white white -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 50pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right" width="67">4.25</td> <td style="border-style: solid solid solid none; border-color: white white white -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 50pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="67">
</td> <td style="border-style: solid solid solid none; border-color: white white white -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 50pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="67">
</td> <td style="border-style: solid solid solid none; border-color: white white white -moz-use-text-color; border-width: 0.5pt 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 50pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="67">
</td> <td style="border-style: solid none; border-color: white -moz-use-text-color; border-width: 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; width: 50pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="67">
</td> <td style="width: 50pt;" align="right" width="67">5.99</td> <td style="width: 50pt;" align="right" width="67">7.99</td> <td style="width: 56pt;" align="right" width="74">8.98</td> </tr> <tr style="height: 15pt;" height="20"> <td valign="top">82
</td><td style="border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">92
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right">1</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right">2</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" align="right">3</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color white white -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">
</td> <td style="border-style: none none solid; border-color: -moz-use-text-color -moz-use-text-color white; border-width: medium medium 0.5pt; background: rgb(184, 204, 228) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;">
</td> <td align="right">4.95</td> <td align="right">5.95</td> <td align="right">7.94</td></tr></tbody></table>
In ws "commissions" I have two key needed values. Cell A181 = 1304. Cell E181 = 5.99.
Just like in the existing formula, we can use A181 to lookup the value in jos_vm_order_item column 5, and then use that value to find that row 81 above is the correct row in jos_vm_product.
Now, I would like to use commissions:E181 and lookup jos_vm_product:AN81 then get the value in jos_vm_product:AG81 (it's 2.45) and put it in commissions:F181.
For example, if commissions:E182 = 8.98 then F182 would be 4.25. And if commissions:E200 = 7.94, and A200 found that row 82 above is the correct row in jos_vm_product, then F200 would be 3
What's that formula? Can you integrate it into the formula above?
Last edited: