Sumproduct array results parsed to vlookup

[Berlina]

<table width="256" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" width="64" span="4"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl70" style="height:15.0pt;width:48pt" width="64" height="20">Title</td> <td class="xl70" style="width:48pt" width="64">List</td> <td class="xl70" style="width:48pt" width="64">Code</td> <td class="xl70" style="width:48pt" width="64">Rate</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Infini</td> <td>Infini</td> <td>inf-01</td> <td class="xl70">1</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Infini</td> <td>Fibre</td> <td>inf-02</td> <td class="xl70">2</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Fibre</td> <td>
</td> <td>fib-01</td> <td class="xl70">10</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Copper</td> <td>
</td> <td>cop-01</td> <td class="xl70">20</td> </tr> </tbody></table>
I trying use sumproduct to: lookup ColA for items matching ColB and sum the corresponding values in ColC referenced to values in ColD.
For example the formula I am using is:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),D2:D5) which answers 13 (Correct).

The Formula I need to use is:
=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),C2:C5) where C2:C5 are resolved/matched/vlookup D2:D5

I tried =SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),vlookup(C2:C5,D2:D5,1,False)) but get errors.

Help much appreciated.

 

[Nalani]

Not to override you formula Guru's, but to help out the OP in maybe a little better Screen shot of what was presented.

I read through this thread a couple of times and noticed a few comments about another Sheet with Column C & D info. Posts #20, 21, & 23.

So for our OP, using what Barry suggested, does this work for you?

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 29px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Title</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">List</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Code</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Infini</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Infini</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">inf-01</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD><TD style="TEXT-ALIGN: right; FONT-STYLE: italic; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">13</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Infini</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Fibre</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">inf-02</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Fibre</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">fib-01</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Copper</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">cop-01</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F2</TD><TD>=SUMPRODUCT(--ISNUMBER(MATCH(A2:A5,B2:B3,0)),SUMIF(Sheet2!A2:A10,C2:C5,Sheet2!B2:B10))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Code</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Rate</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">inf-01</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">1</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">inf-02</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">2</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">fib-01</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">10</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">cop-01</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">20</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


If I understood correctly, your Code & Rate table resides on another sheet (Sheet 2). Sheet 1 Column C is a drop down of you Code List.

If that is the case, Barry's suggestion in Cell F2 above will work. Of course you can adjust the ranges to suit.