How to return the sum of multiple matches

2

2kool4skool

New Member
Joined
Aug 18, 2009
Messages
21
HI, and thanks in advance to everyone who takes the time to reply this post.



I have been struggling with coming up with a system up update my store inventory for several months.
so I have decided to separate the most crucial aspect of my inventory system and give a simplistic example of the problem.

I would like to know how many of eack we can sell. as long as our supplier has it, we can sell it. here is our product database

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700;} .xl64 {border:.5pt solid windowtext;} --> </style> <table style="border-collapse: collapse;width:230pt" border="0" cellpadding="0" cellspacing="0" width="230"> <colgroup><col style="mso-width-source:userset;mso-width-alt:5290;width:124pt" width="124"> <col style="mso-width-source:userset;mso-width-alt:4522;width:106pt" width="106"> </colgroup><tbody><tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;width:124pt" height="15" width="124">OUR product database</td> <td class="xl64" style="border-left:none;width:106pt" width="106">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">product ID</td> <td class="xl64" style="border-top:none;border-left:none">qty available to sell</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Orange</td> <td class="xl64" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Apple</td> <td class="xl64" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry Double Pack</td> <td class="xl64" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry</td> <td class="xl64" style="border-top:none;border-left:none">
</td> </tr> </tbody></table>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700;} .xl64 {border-top:none; border-right:.5pt solid windowtext; border-bottom:none; border-left:none;} .xl65 {border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none;} .xl66 {border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:none; border-left:none;} .xl67 {font-weight:700; border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:none; border-left:.5pt solid windowtext;} .xl68 {border-top:.5pt solid windowtext; border-right:.5pt solid windowtext; border-bottom:none; border-left:.5pt solid windowtext;} .xl69 {border-top:none; border-right:.5pt solid windowtext; border-bottom:none; border-left:.5pt solid windowtext;} .xl70 {border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;} --</style>

here is our suppliers' inventory list

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700;} .xl64 {border:.5pt solid windowtext;} --> </style> <table style="border-collapse: collapse;width:244pt" border="0" cellpadding="0" cellspacing="0" width="244"> <colgroup><col style="mso-width-source:userset;mso-width-alt:3157;width:74pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:4394;width:103pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:2858;width:67pt" width="67"> </colgroup><tbody><tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;width:74pt" height="15" width="74">supplier's inventory list</td> <td class="xl64" style="border-left:none;width:103pt" width="103">
</td> <td class="xl64" style="border-left:none;width:67pt" width="67">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">supplier code</td> <td class="xl64" style="border-top:none;border-left:none">supplier product ID</td> <td class="xl64" style="border-top:none;border-left:none">qty on hand</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Valencia Sweet</td> <td class="xl64" style="border-top:none;border-left:none" align="right">24</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Red Delicious</td> <td class="xl64" style="border-top:none;border-left:none" align="right">12</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Berry Made</td> <td class="xl64" style="border-top:none;border-left:none" align="right">0</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Big Reds</td> <td class="xl64" style="border-top:none;border-left:none" align="right">80</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">XYZ</td> <td class="xl64" style="border-top:none;border-left:none">Florida best</td> <td class="xl64" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">XYZ</td> <td class="xl64" style="border-top:none;border-left:none">Washington Pride</td> <td class="xl64" style="border-top:none;border-left:none" align="right">600</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">XYZ</td> <td class="xl64" style="border-top:none;border-left:none">Carlsbad Select</td> <td class="xl64" style="border-top:none;border-left:none" align="right">29</td> </tr> </tbody></table>

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700;} .xl64 {border-top:none; border-right:none; border-bottom:none; border-left:.5pt solid windowtext;} .xl65 {border-top:none; border-right:.5pt solid windowtext; border-bottom:none; border-left:none;} .xl66 {border-top:none; border-right:none; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;} .xl67 {border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:none;} .xl68 {border-top:none; border-right:none; border-bottom:.5pt solid windowtext; border-left:none;} .xl69 {font-weight:700; border:.5pt solid windowtext;} --></style>

and here is a table that I had to make which translates each of the supplier's names for each type of fruit into our product ID for it.
it is updated as new types of fruit become available.

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {font-weight:700;} .xl64 {border:.5pt solid windowtext;} --> </style> <table style="border-collapse: collapse;width:311pt" border="0" cellpadding="0" cellspacing="0" width="311"> <colgroup><col style="mso-width-source:userset;mso-width-alt:6058;width:142pt" width="142"> <col style="mso-width-source:userset;mso-width-alt:3200;width:75pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:4010;width:94pt" width="94"> </colgroup><tbody><tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;width:142pt" height="15" width="142">alternate product IDs table</td> <td class="xl64" style="border-left:none;width:75pt" width="75">
</td> <td class="xl64" style="border-left:none;width:94pt" width="94">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">OUR IDs</td> <td class="xl64" style="border-top:none;border-left:none">Supplier code</td> <td class="xl64" style="border-top:none;border-left:none">supplier SKU</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Orange</td> <td class="xl64" style="border-top:none;border-left:none">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Valencia Sweet</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Orange</td> <td class="xl64" style="border-top:none;border-left:none">XYZ</td> <td class="xl64" style="border-top:none;border-left:none">Florida best</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Apple</td> <td class="xl64" style="border-top:none;border-left:none">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Red Delicious</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Apple</td> <td class="xl64" style="border-top:none;border-left:none">XYZ</td> <td class="xl64" style="border-top:none;border-left:none">Washington Pride</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry</td> <td class="xl64" style="border-top:none;border-left:none">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Berry Made</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry</td> <td class="xl64" style="border-top:none;border-left:none">XYZ</td> <td class="xl64" style="border-top:none;border-left:none">Carlsbad Select</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry</td> <td class="xl64" style="border-top:none;border-left:none">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Big Reds</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry Double Pack</td> <td class="xl64" style="border-top:none;border-left:none">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Berry Made</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry Double Pack</td> <td class="xl64" style="border-top:none;border-left:none">XYZ</td> <td class="xl64" style="border-top:none;border-left:none">Carlsbad Select</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl64" style="height:15.0pt;border-top:none" height="15">Strawberry Double Pack</td> <td class="xl64" style="border-top:none;border-left:none">ABC</td> <td class="xl64" style="border-top:none;border-left:none">Big Reds</td> </tr> </tbody></table>


I know how to do Vlookups and that's about it. I've learned a lot from these forums, but not enough to figure this one out.
if I use a Vlookup it will only return the result for the first alternate ID match.

so for instance, on the suppliers' inventory table, I can try and enter a vlookup formula on the suppliers" inventory table
to match the supplier product ID to our ID from the alternate ID table, and then I could enter a vlookup formula on our
product table to lookup the inventory, but the vlookup on the supplier table can only return 1 result at a time,
so it would return the match of "Strawberry" for "Berry Made", but it wouldn't return "Strawberry Double Pack".

I could try entering a vlookup formula on the alternate ID table and return the inventory value from the supliers' inventory
table, and then enter another vlookup on our product ID table to return the inventory from the alternate ID table but same
problem, it would only return the inventory of the first match (it would give me the value for "Valencia Sweet" but not "Florida Best".

I don't know enough to proceed. I have heard of pivot tables but I don't even know enough to know whether or not that would help me.
I'm really stuck here and I would appreciate it if someone knew how to solve this problem.
thanks,
Steve
 
Last edited by a moderator:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Consider...

A1:B6...

<TABLE style="WIDTH: 232pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=310><COLGROUP><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 6172" width=174><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4835" width=136><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 130pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=174>Product DB</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 102pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=136></TD></TR>
<TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>product ID</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>qty available to sell</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Orange</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>25</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Apple</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>612</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Strawberry Double Pack</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>109</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Strawberry</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>109</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR>
<TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2695106 class=xl65 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR>

</TBODY></TABLE>

D1:F9...
<TABLE style="WIDTH: 283pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=378><COLGROUP><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 5546" width=156><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4664" width=131><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3242" width=91><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 117pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=156>supplier's inventory list</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=131></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=91></TD></TR>
<TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>supplier code</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>supplier product ID</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>qty on hand</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>ABC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Valencia Sweet</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>24</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>ABC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Red Delicious</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>12</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>ABC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Berry Made</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>ABC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Big Reds</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>80</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>XYZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Florida best</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>XYZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Washington Pride</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>600</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>XYZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Carlsbad Select</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>29</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR>
</TBODY></TABLE>

H1:J12...
<TABLE style="WIDTH: 324pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=433><COLGROUP><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7452" width=210><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3612" width=102><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4295" width=121><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 157pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=210>alternate product IDs table</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=102></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 91pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=121></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>OUR IDs</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Supplier code</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>supplier SKU</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Orange</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>ABC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Valencia Sweet</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Orange</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>XYZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Florida best</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Apple</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>ABC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Red Delicious</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Apple</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>XYZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Washington Pride</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Strawberry</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>ABC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Berry Made</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Strawberry</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>XYZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Carlsbad Select</TD></TR>
<TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2695106 class=xl65 height=19>Strawberry</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>ABC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Big Reds</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Strawberry Double Pack</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>ABC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Berry Made</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Strawberry Double Pack</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>XYZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Carlsbad Select</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>Strawberry Double Pack</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>ABC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Big Reds</TD></TR>
</TBODY></TABLE>

B3, control+shift+enter, not just enter, and copy down:
Code: 
=SUM(IF(ISNUMBER(MATCH($D$3:$D$9&"|"&$E$3:$E$9,
   IF($H$3:$H$12=A3,$I$3:$I$12&"|"&$J$3:$J$12,"#"),0)),$F$3:$F$9))
 
Upvote 0
this is brilliant, thank you so much, I thought I would have to have vlookup formulas on multiple sheets/tables or
something even more complicated.
However, I overlooked something when framing my example in the original post though.

what if the suppliers' inventory was on two separate tables and looked like this

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --> </style> <table style="border-collapse: collapse;width:288pt" border="0" cellpadding="0" cellspacing="0" width="288"> <colgroup><col style="mso-width-source:userset;mso-width-alt:5162;width:121pt" width="121"> <col style="mso-width-source:userset;mso-width-alt:4309;width:101pt" width="101"> <col style="mso-width-source:userset;mso-width-alt:2816;width:66pt" width="66"> </colgroup><tbody><tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;width:121pt" height="15" width="121">supplier ABC's inventory list</td> <td class="xl63" style="border-left:none;width:101pt" width="101">
</td> <td class="xl63" style="border-left:none;width:66pt" width="66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">supplier code</td> <td class="xl63" style="border-top:none;border-left:none">supplier product ID</td> <td class="xl63" style="border-top:none;border-left:none">qty on hand</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">ABC</td> <td class="xl63" style="border-top:none;border-left:none">Valencia Sweet</td> <td class="xl63" style="border-top:none;border-left:none" align="right">24</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">ABC</td> <td class="xl63" style="border-top:none;border-left:none">Red Delicious</td> <td class="xl63" style="border-top:none;border-left:none" align="right">12</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">ABC</td> <td class="xl63" style="border-top:none;border-left:none">Berry Made</td> <td class="xl63" style="border-top:none;border-left:none" align="right">0</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">ABC</td> <td class="xl63" style="border-top:none;border-left:none">Big Reds</td> <td class="xl63" style="border-top:none;border-left:none" align="right">80</td> </tr> </tbody></table>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --> </style> <table style="border-collapse: collapse;width:288pt" border="0" cellpadding="0" cellspacing="0" width="288"> <colgroup><col style="mso-width-source:userset;mso-width-alt:5162;width:121pt" width="121"> <col style="mso-width-source:userset;mso-width-alt:4309;width:101pt" width="101"> <col style="mso-width-source:userset;mso-width-alt:2816;width:66pt" width="66"> </colgroup><tbody><tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;width:121pt" height="15" width="121">supplier XYZ's inventory list</td> <td style="width:101pt" width="101">
</td> <td style="width:66pt" width="66">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">XYZ</td> <td class="xl63" style="border-left:none">Florida best</td> <td class="xl63" style="border-left:none" align="right">1</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">XYZ</td> <td class="xl63" style="border-top:none;border-left:none">Washington Pride</td> <td class="xl63" style="border-top:none;border-left:none" align="right">600</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">XYZ</td> <td class="xl63" style="border-top:none;border-left:none">Carlsbad Select</td> <td class="xl63" style="border-top:none;border-left:none" align="right">29</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">XYZ</td> <td class="xl63" style="border-top:none;border-left:none">Red Delicious</td> <td class="xl63" style="border-top:none;border-left:none" align="right">44</td> </tr> </tbody></table>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --></style>and the Alternate product ID table looked like this:
(notice that in the Alternate table, supplier ABC's "red Delicious" is an "Apple" whilst supplier XYZ's "Red Delicious" is a "Strawberry")

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --> </style> <table style="border-collapse: collapse;width:307pt" border="0" cellpadding="0" cellspacing="0" width="307"> <colgroup><col style="mso-width-source:userset;mso-width-alt:5930;width:139pt" width="139"> <col style="mso-width-source:userset;mso-width-alt:3157;width:74pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:4010;width:94pt" width="94"> </colgroup><tbody><tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;width:139pt" height="15" width="139">alternate product IDs table</td> <td class="xl63" style="border-left:none;width:74pt" width="74">
</td> <td class="xl63" style="border-left:none;width:94pt" width="94">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">OUR IDs</td> <td class="xl63" style="border-top:none;border-left:none">Supplier code</td> <td class="xl63" style="border-top:none;border-left:none">supplier SKU</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">Orange</td> <td class="xl63" style="border-top:none;border-left:none">ABC</td> <td class="xl63" style="border-top:none;border-left:none">Valencia Sweet</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">Orange</td> <td class="xl63" style="border-top:none;border-left:none">XYZ</td> <td class="xl63" style="border-top:none;border-left:none">Florida best</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">Apple</td> <td class="xl63" style="border-top:none;border-left:none">ABC</td> <td class="xl63" style="border-top:none;border-left:none">Red Delicious</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">Apple</td> <td class="xl63" style="border-top:none;border-left:none">XYZ</td> <td class="xl63" style="border-top:none;border-left:none">Washington Pride</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">Strawberry</td> <td class="xl63" style="border-top:none;border-left:none">ABC</td> <td class="xl63" style="border-top:none;border-left:none">Berry Made</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">Strawberry</td> <td class="xl63" style="border-top:none;border-left:none">XYZ</td> <td class="xl63" style="border-top:none;border-left:none">Carlsbad Select</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">Strawberry</td> <td class="xl63" style="border-top:none;border-left:none">ABC</td> <td class="xl63" style="border-top:none;border-left:none">Big Reds</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">Strawberry Double Pack</td> <td class="xl63" style="border-top:none;border-left:none">ABC</td> <td class="xl63" style="border-top:none;border-left:none">Berry Made</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">Strawberry Double Pack</td> <td class="xl63" style="border-top:none;border-left:none">XYZ</td> <td class="xl63" style="border-top:none;border-left:none">Carlsbad Select</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">Strawberry Double Pack</td> <td class="xl63" style="border-top:none;border-left:none">ABC</td> <td class="xl63" style="border-top:none;border-left:none">Big Reds</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">Strawberry</td> <td class="xl63" style="border-top:none;border-left:none">XYZ</td> <td class="xl63" style="border-top:none;border-left:none">Red Delicious</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">Strawberry Double Pack</td> <td class="xl63" style="border-top:none;border-left:none">XYZ</td> <td class="xl63" style="border-top:none;border-left:none">Red Delicious</td> </tr> </tbody></table>
also, will the formula work with named ranges?
thank you so much
Steve
 
Last edited by a moderator:
Upvote 0
2kool4skool said:
this is brilliant, thank you so much, I thought
I would have to have vlookup formulas on multiple sheets/tables or
something even more complicated.
Click to expand...

You are welcome. Thanks for providing feedback.

However, I overlooked something when
framing my example in the original post though.

what if the suppliers' inventory was on two separate tables and
looked like this

<STYLE> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --> </STYLE><TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=288><COLGROUP><COL style="WIDTH: 121pt; mso-width-source: userset; mso-width-alt: 5162" width=121><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4309" width=101><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 2816" width=66></COLGROUP><TBODY><TR style="HEIGHT: 15pt" height=15><TD style="WIDTH: 121pt; HEIGHT: 15pt" class=xl63 height=15 width=121>supplier ABC's inventory list</TD><TD style="BORDER-LEFT: medium none; WIDTH: 101pt" class=xl63 width=101>

</TD><TD style="BORDER-LEFT: medium none; WIDTH: 66pt" class=xl63 width=66>

</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>supplier code</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>supplier product ID</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>qty on hand</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>ABC</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Valencia Sweet</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>24</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>ABC</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Red Delicious</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>12</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>ABC</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Berry Made</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>ABC</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Big Reds</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>80</TD></TR></TBODY></TABLE>
<STYLE> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --> </STYLE><TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=288><COLGROUP><COL style="WIDTH: 121pt; mso-width-source: userset; mso-width-alt: 5162" width=121><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4309" width=101><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 2816" width=66></COLGROUP><TBODY><TR style="HEIGHT: 15pt" height=15><TD style="WIDTH: 121pt; HEIGHT: 15pt" class=xl63 height=15 width=121>supplier XYZ's inventory list</TD><TD style="WIDTH: 101pt" width=101>

</TD><TD style="WIDTH: 66pt" width=66>

</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>XYZ</TD><TD style="BORDER-LEFT: medium none" class=xl63>Florida best</TD><TD style="BORDER-LEFT: medium none" class=xl63 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>XYZ</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Washington Pride</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>600</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>XYZ</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Carlsbad Select</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>29</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>XYZ</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Red Delicious</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>44</TD></TR></TBODY></TABLE>
Click to expand...

Better to keem them all together.
<STYLE> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --></STYLE>
and the Alternate product ID table looked like this:
(notice that in the Alternate table, supplier ABC's "red Delicious" is an
"Apple" whilst supplier XYZ's "Red Delicious" is a "Strawberry")

<STYLE> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --> </STYLE><TABLE style="WIDTH: 307pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=307><COLGROUP><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 5930" width=139><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3157" width=74><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4010" width=94></COLGROUP><TBODY><TR style="HEIGHT: 15pt" height=15><TD style="WIDTH: 139pt; HEIGHT: 15pt" class=xl63 height=15 width=139>alternate product IDs table</TD><TD style="BORDER-LEFT: medium none; WIDTH: 74pt" class=xl63 width=74>

</TD><TD style="BORDER-LEFT: medium none; WIDTH: 94pt" class=xl63 width=94>

</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>OUR IDs</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Supplier code</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>supplier SKU</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>Orange</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>ABC</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Valencia Sweet</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>Orange</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>XYZ</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Florida best</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>Apple</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>ABC</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Red Delicious</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>Apple</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>XYZ</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Washington Pride</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>Strawberry</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>ABC</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Berry Made</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>Strawberry</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>XYZ</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Carlsbad Select</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>Strawberry</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>ABC</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Big Reds</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>Strawberry Double Pack</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>ABC</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Berry Made</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>Strawberry Double Pack</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>XYZ</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Carlsbad Select</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>Strawberry Double Pack</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>ABC</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Big Reds</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>Strawberry</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>XYZ</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Red Delicious</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>Strawberry Double Pack</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>XYZ</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>Red Delicious</TD></TR></TBODY></TABLE>
Click to expand...

This shouldn't be an issue...

also, will the formula work with named ranges?
thank you so much
Steve
Click to expand...

Yes, it would.
 
Upvote 0
thank you! this is great. I will figure out a way to have the supplier info all in one table then.
my actual data set consists of dozens of suppliers with anywhere from hundreds to tens of thousands
of products per supplier (and it is not fruit :). but I think this will give me what I need to complete
the inventory management tool. thank you again.
Steve
 
Last edited by a moderator:
Upvote 0
Steve

For future posts please consider one of the 3 screen shot metods in my signature block. As you can see,
whatever you are using to generate the HTML for your tables is messing up the page formatting,
making the thread/posts hard to read.
 
Last edited:
Upvote 0
2kool4skool said:
thank you! this is great.
Click to expand...

You are welcome. Thanks for providing feedback

I will figure out a way to have the supplier info all in one table then. my
actual data set consists of dozens of suppliers with anywhere from hundreds
to tens of thousands of products per supplier (and it is not fruit :). but I
think this will give me what I need to complete the inventory management
tool. thank you again.
Steve
Click to expand...

Hmm. Such large numbers might affect performance.
 
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
619
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA code clean up
Replies
9
Views
574
mumps
mumps
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,548
Messages
6,179,445
Members
452,915
Latest member
hannnahheileen

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