Sumproduct array results parsed to vlookup

B

Berlina

New Member
Joined
Jun 18, 2011
Messages
14
<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.
 
Sort by date Sort by votes
Aladin Akyurek said:
Are you saying that with this formula you have the solution you need?
Click to expand...

The result is correct in this formula 13. BUT, I cant use d2:d5 although they contain the cell data I need -- I need to obtain the cell data d2:d5 using a formula which includes c2:c5 ; for example, although it doesnt work for obvious reasons SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),vlookup(C2:C5,D2:D5,1,False))

Thankyou for your kind help.
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Berlina said:
The result is correct in this formula 13. BUT, I cant use d2:d5 although they contain the cell data I need -- I need to obtain the cell data d2:d5 using a formula which includes c2:c5 ; for example, although it doesnt work for obvious reasons SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),vlookup(C2:C5,D2:D5,1,False))

Thankyou for your kind help.
Click to expand...

Let's first agree on the result (thereby on my understanding)...

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Title</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>List</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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Infini</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Infini</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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Infini</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Fibre</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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Fibre</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Copper</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD></TR></TBODY></TABLE>

We need to calculate a total for A2:A5 for items that match B2:B3. This means calculation a total for:

Infini, Infini, and Fiber since these match B2:b3.

If we seek values for the first Infini in:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=64>Code</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>Rate</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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>inf-01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>inf-02</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=right>2</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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>fib-01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=right>10</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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>cop-01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=right>20</TD></TR></TBODY></TABLE>

we get: 1 and 2 ---> 3.

If we do the same for the second Infini, we gain get: 1 and 2 ---> 3.

If we do this also for Fiber, we get 10.

3+3+10=16

Is this reasoning/matching process not right?
 
Upvote 0
Than you Aladin. You are close - sorry about my poor explanation. I will try again using the table below.

Berlina said:
<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>
Click to expand...

Cell array B2:B3 (List) contains my criteria. First step is to check the List against array A2:A5, match the cells and deliver the results (in this example the sum) from across the A2:A5 row match to C2:C5. There would be no problem if C2:C5 were numerics like shown in =SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),D2:D5).

This why I tried to incorporate vlookup into the equation --- but vlookup doesnt work.


Help much appreciated.
 
Upvote 0
Aladin Akyurek said:
Let's first agree on the result (thereby on my understanding)...

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Title</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>List</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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Infini</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Infini</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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Infini</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Fibre</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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Fibre</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>Copper</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD></TR></TBODY></TABLE>

We need to calculate a total for A2:A5 for items that match B2:B3. This means calculation a total for:

Infini, Infini, and Fiber since these match B2:b3.

If we seek values for the first Infini in:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=64>Code</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>Rate</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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>inf-01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>inf-02</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=right>2</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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>fib-01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=right>10</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: #efefef; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>cop-01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64 align=right>20</TD></TR></TBODY></TABLE>

we get: 1 and 2 ---> 3.

If we do the same for the second Infini, we gain get: 1 and 2 ---> 3.

If we do this also for Fiber, we get 10.

3+3+10=16

Is this reasoning/matching process not right?
Click to expand...

My final answer is 13: matching,
Infini>>>1
Infini>>>2
Fibre>>>10
sum of these cells is therefore 13. My formula =SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),D2:D5) gives this result as a test -- just I need to resolve by using C2:C5 (ie vlookup or whatever)
 
Upvote 0
Berlina said:
Than you Aladin. You are close - sorry about my poor explanation. I will try again using the table below.



Cell array B2:B3 (List) contains my criteria. First step is to check the List against array A2:A5, match the cells and deliver the results (in this example the sum) from across the A2:A5 row match to C2:C5. There would be no problem if C2:C5 were numerics like shown in =SUMPRODUCT(--(ISNUMBER(MATCH(A2:A5,B2:B3,0))),D2:D5).

This why I tried to incorporate vlookup into the equation --- but vlookup doesnt work.


Help much appreciated.
Click to expand...

Would you please stop for a moment discussing any Excel formula?

Would you explain that 13 of yours how you arrive at without mantioning any Excel function or formula as I've done myself?
 
Upvote 0
...but how are you getting the values from the codes? wouldn't you need a two column table somewhere which would list the possible codes from column C with the values, if C2:D5 forms that table why can't you reference D2:D5 directly?

If you had a two column table e.g. in Y2:Z10 then you could get the answer you want with

=SUMPRODUCT(--ISNUMBER(MATCH(A2:A5,B2:B3,0)),SUMIF(Y2:Y10,C2:C5,Z2:Z10))

where the SUMIF part is acting like you expected the VLOOKUP to act
 
Upvote 0
barry houdini said:
...but how are you getting the values from the codes? wouldn't you need a two column table somewhere which would list the possible codes from column C with the values, if C2:D5 forms that table why can't you reference D2:D5 directly?

If you had a two column table e.g. in Y2:Z10 then you could get the answer you want with

=SUMPRODUCT(--ISNUMBER(MATCH(A2:A5,B2:B3,0)),SUMIF(Y2:Y10,C2:C5,Z2:Z10))

where the SUMIF part is acting like you expected the VLOOKUP to act
Click to expand...

Do you have a diffrent result in mind other than 16?
 
Upvote 0
Hello Aladin,

I'm assuming 13 because A2, A3 and A4 match B2:B3, so the result would be the sum of the corresponding cells in column D, namely D2, D3 and D4.

What I don't understand is how those values are looked up, if not directly from D2:D5
 
Upvote 0
barry houdini said:
Hello Aladin,

I'm assuming 13 because A2, A3 and A4 match B2:B3, so the result would be the sum of the corresponding cells in column D, namely D2, D3 and D4.

What I don't understand is how those values are looked up, if not directly from D2:D5
Click to expand...

You seem to think in terms of positions... However, where do C2:C5 come in?

I'm actually trying the OP stop forwarding or thinking in Excel formulas. Instead wording a procedure for the calculation would help.
 
Upvote 0
barry houdini said:
Hello Aladin,

I'm assuming 13 because A2, A3 and A4 match B2:B3, so the result would be the sum of the corresponding cells in column D, namely D2, D3 and D4.

What I don't understand is how those values are looked up, if not directly from D2:D5
Click to expand...

Correct A2,A3 and A4 match B2:B3. The result is in my nightmare sheet column C (ie C2,C3,C4 --- which are ID codes like shown). I want to within the formula lookup these ID codes C2,C3,C4 from another sheet (for the example shown I stuck the D column in). So ultimately C2=1,C3=2,C4=10 and the sum is 13.
 
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
617
smozgur
smozgur
M
  • Question Question
Replace vlookup using Array and dictionary
Replies
8
Views
108
JEC
JEC
H
  • Question Question
Formula is not working in 1 of the following senario
Replies
0
Views
526
Harmandeep
H
K
  • Solved
Formula to match two columns and get a result from third one
Replies
5
Views
344
Fluff
Fluff
S
  • Solved
Calculate annual sales per country
Replies
5
Views
156
AhoyNC
A

Forum statistics

Threads
1,224,522
Messages
6,179,297
Members
452,903
Latest member
Knuddeluff

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