Need a formula suggestion

V

VanMoll

New Member
Joined
Sep 15, 2011
Messages
4
Excel 2007<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" rules="all" cellpadding="2.5px"><colgroup><col style="background-color: rgb(224, 224, 240);" width="25px"><col><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="text-align: center; border: 1px solid black; background-color: rgb(219, 229, 241);">ID</td><td style="text-align: center; border: 1px solid black; background-color: rgb(219, 229, 241);">Manuf.</td><td style="text-align: center; border: 1px solid black; background-color: rgb(219, 229, 241);">ID Prod</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="text-align: center; border: 1px solid black;">268</td><td style="text-align: center; border: 1px solid black;">Nokia</td><td style="text-align: center; border: 1px solid black;">42</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</td><td style="text-align: center; border: 1px solid black;">265</td><td style="text-align: center; border: 1px solid black;">Motorola</td><td style="text-align: center; border: 1px solid black;">42</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="text-align: center; border: 1px solid black;">269</td><td style="text-align: center; border: 1px solid black;">Motorola</td><td style="text-align: center; border: 1px solid black;">58</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="text-align: center; border: 1px solid black;">270</td><td style="text-align: center; border: 1px solid black;">Motorola</td><td style="text-align: center; border: 1px solid black;">59</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">6</td><td style="text-align: center; border: 1px solid black;">271</td><td style="text-align: center; border: 1px solid black;">NEC</td><td style="text-align: center; border: 1px solid black;">58</td></tr></tbody></table>
Table1
​

<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" rules="all" cellpadding="2.5px"><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">8</td><td style="text-align: center; border: 1px solid black; background-color: rgb(219, 229, 241);">ID Prod</td><td style="text-align: center; border: 1px solid black; background-color: rgb(219, 229, 241);">Product</td><td style="text-align: center; border: 1px solid black; background-color: rgb(219, 229, 241);">ID Categ.</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">9</td><td style="text-align: center; border: 1px solid black;">42</td><td style="text-align: center; border: 1px solid black;">Nokia</td><td style="text-align: center; border: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">10</td><td style="text-align: center; border: 1px solid black;">42</td><td style="text-align: center; border: 1px solid black;">Motorola</td><td style="text-align: center; border: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">11</td><td style="text-align: center; border: 1px solid black;">58</td><td style="text-align: center; border: 1px solid black;">Motorola</td><td style="text-align: center; border: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">12</td><td style="text-align: center; border: 1px solid black;">59</td><td style="text-align: center; border: 1px solid black;">Motorola</td><td style="text-align: center; border: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">13</td><td style="text-align: center; border: 1px solid black;">58</td><td style="text-align: center; border: 1px solid black;">Motorola</td><td style="text-align: center; border: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">14</td><td style="text-align: center; border: 1px solid black;">58</td><td style="text-align: center; border: 1px solid black;">Motorola</td><td style="text-align: center; border: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">15</td><td style="text-align: center; border: 1px solid black;">58</td><td style="text-align: center; border: 1px solid black;">Motorola</td><td style="text-align: center; border: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">16</td><td style="text-align: center; border: 1px solid black;">58</td><td style="text-align: center; border: 1px solid black;">NEC</td><td style="text-align: center; border: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">17</td><td style="text-align: center; border: 1px solid black;">58</td><td style="text-align: center; border: 1px solid black;">NEC</td><td style="text-align: center; border: 1px solid black;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">18</td><td style="text-align: center; border: 1px solid black;">58</td><td style="text-align: center; border: 1px solid black;">Nokia</td><td style="text-align: center; border: 1px solid black;">
</td></tr></tbody></table>
Table2​

I need help to fill the ID Categ. column. Basically ID Categ will contain number of ID from Table 1.
ID Categ will look the Product and ID Product. If they find a match combination (for example Nokia and 42) it will contain 268 and so on.

I've think about using IF but i don't know how to set the condition and stuff. Any suggestion ?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
In your table 1 in an adjacent column, concatenate the ID Prod & Manuf .

Then in table2 use vlookup()
 
Upvote 0
Hi,

Alternatively you can use the SUMPRODUCT function:

=SUMPRODUCT(($B$2:$B$6=$B9)*($C$2:$C$6=$A9)*($A$2:$A$6))

where the first 2 arrays check for the conditions, and the final array is the results array.
 
Upvote 0
VanMoll said:
Excel 2007<TABLE style="BORDER-RIGHT: rgb(166,170,182) 1px solid; BORDER-TOP: rgb(166,170,182) 1px solid; BORDER-LEFT: rgb(166,170,182) 1px solid; BORDER-BOTTOM: rgb(166,170,182) 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: rgb(255,255,255)" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: rgb(224,224,240)" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: rgb(22,17,32); BACKGROUND-COLOR: rgb(224,224,240); TEXT-ALIGN: center"><TH>

</TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: rgb(219,229,241); TEXT-ALIGN: center">ID</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: rgb(219,229,241); TEXT-ALIGN: center">Manuf.</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: rgb(219,229,241); TEXT-ALIGN: center">ID Prod</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">268</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Nokia</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">42</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">3</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">265</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Motorola</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">42</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">269</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Motorola</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">58</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">5</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">270</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Motorola</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">59</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">6</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">271</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">NEC</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">58</TD></TR></TBODY></TABLE>
Table1​



<TABLE style="BORDER-RIGHT: rgb(166,170,182) 1px solid; BORDER-TOP: rgb(166,170,182) 1px solid; BORDER-LEFT: rgb(166,170,182) 1px solid; BORDER-BOTTOM: rgb(166,170,182) 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: rgb(255,255,255)" cellPadding=2 rules=all><THEAD><TR style="COLOR: rgb(22,17,32); BACKGROUND-COLOR: rgb(224,224,240); TEXT-ALIGN: center"><TH>

</TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">8</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: rgb(219,229,241); TEXT-ALIGN: center">ID Prod</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: rgb(219,229,241); TEXT-ALIGN: center">Product</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: rgb(219,229,241); TEXT-ALIGN: center">ID Categ.</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">9</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">42</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Nokia</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">10</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">42</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Motorola</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">11</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">58</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Motorola</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">12</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">59</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Motorola</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">13</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">58</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Motorola</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">14</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">58</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Motorola</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">15</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">58</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Motorola</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">16</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">58</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">NEC</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">17</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">58</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">NEC</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">

</TD></TR><TR><TD style="COLOR: rgb(22,17,32); TEXT-ALIGN: center">18</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">58</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Nokia</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">
​
</TD></TR></TBODY></TABLE>Table2

I need help to fill the ID Categ. column. Basically ID Categ will contain number of ID from Table 1.
ID Categ will look the Product and ID Product. If they find a match combination (for example Nokia and 42) it will contain 268 and so on.

I've think about using IF but i don't know how to set the condition and stuff. Any suggestion ?
Click to expand...
Try this...

Array entered**:

=INDEX(A$2:A$6,MATCH(1,IF(B$2:B$6=B9,IF(C$2:C$6=A9,1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0
Ok, here's the result. With Index the result #N/A and using Sumproduct only show 0 (zero) on the cell.
Using vlookup only works on sample table but didn't worked on actual data.
The table above is only a sample table.

I've included the original data and the final result is on ID Categ.

Book1.xlsx
 
Upvote 0
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">ID</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">Manuf.</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">Parent</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">Parent</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">Product</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #EAF1DD;;">ID Categ.</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;;">100</td><td style="text-align: center;border-top: 1px solid black;;">Acer</td><td style="text-align: center;border-top: 1px solid black;;">69</td><td style="text-align: right;;"></td><td style="text-align: center;border-top: 1px solid black;;">42</td><td style="text-align: center;border-top: 1px solid black;;">Nokia</td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">101</td><td style="text-align: center;;">Apple</td><td style="text-align: center;;">36</td><td style="text-align: right;;"></td><td style="text-align: center;;">42</td><td style="text-align: center;;">Motorola</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">102</td><td style="text-align: center;;">Apple</td><td style="text-align: center;;">44</td><td style="text-align: right;;"></td><td style="text-align: center;;">58</td><td style="text-align: center;;">Motorola</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">103</td><td style="text-align: center;;">Apple</td><td style="text-align: center;;">65</td><td style="text-align: right;;"></td><td style="text-align: center;;">58</td><td style="text-align: center;;">Motorola</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">104</td><td style="text-align: center;;">Apple</td><td style="text-align: center;;">69</td><td style="text-align: right;;"></td><td style="text-align: center;;">58</td><td style="text-align: center;;">Motorola</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">105</td><td style="text-align: center;;">AT&T</td><td style="text-align: center;;">66</td><td style="text-align: right;;"></td><td style="text-align: center;;">58</td><td style="text-align: center;;">Motorola</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">106</td><td style="text-align: center;;">Audiovox</td><td style="text-align: center;;">44</td><td style="text-align: right;;"></td><td style="text-align: center;;">58</td><td style="text-align: center;;">Motorola</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">107</td><td style="text-align: center;;">Audiovox</td><td style="text-align: center;;">58</td><td style="text-align: right;;"></td><td style="text-align: center;;">58</td><td style="text-align: center;;">NEC</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">108</td><td style="text-align: center;;">Audiovox</td><td style="text-align: center;;">63</td><td style="text-align: right;;"></td><td style="text-align: center;;">58</td><td style="text-align: center;;">NEC</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">109</td><td style="text-align: center;;">Audiovox</td><td style="text-align: center;;">46</td><td style="text-align: right;;"></td><td style="text-align: center;;">58</td><td style="text-align: center;;">Nokia</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">110</td><td style="text-align: center;;">Audiovox</td><td style="text-align: center;;">65</td><td style="text-align: right;;"></td><td style="text-align: center;;">58</td><td style="text-align: center;;">Nokia</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">111</td><td style="text-align: center;;">Audiovox</td><td style="text-align: center;;">45</td><td style="text-align: right;;"></td><td style="text-align: center;;">58</td><td style="text-align: center;;">Nokia</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

This is an actual data sheet. And i still have a difficulties to fill ID. Categ
The process will go like this : ID. Categ will look the Product (F) and Parent (E). If they find combination between E and F, they will find the match on the left table (B and C) it automatically fill the ID Categ. with ID (A)

Example :
The Nokia product with Parent category 42 will give a result 268, a way down on left table.
Same as for Acer product with parent ID 69 will mark with ID Categ. 100 and so on.

Please ask if I'm not clear enough to explain

Thank you
 
Upvote 0
see if this gets you started :


Excel Workbook
ABCDEFG
1IDManuf.Parent*ParentProductID Categ.
2100Acer69*42Nokia268
3101Apple36*42Motorola224
4102Apple44*58Motorola233
5103Apple65*58Motorola233
6104Apple69*58Motorola233
7105AT&T66*58Motorola233
8106Audiovox44*58Motorola233
Sheet1
 
Upvote 0
Thank you so much snoopyhr. I really don't understand your formula but i will study it.
Thanks again
 
Upvote 0
VanMoll said:
Ok, here's the result. With Index the result #N/A and using Sumproduct only show 0 (zero) on the cell.
Using vlookup only works on sample table but didn't worked on actual data.
The table above is only a sample table.

I've included the original data and the final result is on ID Categ.

Book1.xlsx
Click to expand...
In your file, the numbers in column E are not true numbers, they're TEXT numbers.

The numbers in column C are true numbers so comparing a TEXT number from column E to a numeric number in column C causes the problem.

To fix the problem...

Select the range of numbers in column E
Goto the Data tab
Select Text to Columns
Just click Finish
 
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
624
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
90
Dazzybeeguy
D
M
  • Solved
Need help with Dates
Replies
4
Views
226
Alex Blakenburg
A

Forum statistics

Threads
1,224,616
Messages
6,179,908
Members
452,949
Latest member
beartooth91

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