Using VLOOKUP and SUM to calculate data from different sheets

Neuron

New Member
Joined
Sep 22, 2016
Messages
7
Hi all,

I am trying to find a way to calculate a value using parameters stored in different sheets/tables. I have been trying to do this with sum and vlookup without success. The input data looks like this:

TypePower
A5
A9
B0
B1
C6
C2
C3
D7
D0
D0

<tbody>
</tbody>

and

TypeBase
A3
B3
C4
D5

<tbody>
</tbody>

For each line in the first table I would like to calculate

=IF(Power>0;VLOOKUP(Table1!Type;Table2;2;FALSE)*2^Table1!Power;0)

I am able to calculate this for each line, but I need to calculate the sum of this value for all the lines in a single cell.

All my attempts with VLOOKUP have so far met with failure. Any help is greatly appreciated!!

Thanks in advance!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi Neuron,

Welcome to the forums.

Which table above is Table 1 and which is Table 2?

What exactly are you trying to do? In other words, what is your expected answer?

And could you please clarify what you mean by the below?

I am able to calculate this for each line, but I need to calculate the sum of this value for all the lines in a single cell.
 
Upvote 0
Sorry I'll try to explain better.


Type (A)Power (B)CalcValue (C)(D)Type (E)Base (F)
A53A3
A93B6
B00C5
B16D4
C65
C20
C30
D70
D04
D04

<tbody>
</tbody>

The calculation formula for C2 is : =IF(B2>0;VLOOKUP(A2;$E$2:$F$5;2;FALSE);0)

Since I have many columns like B, each of which has a different set of Powers for the "Types" in A column, I would like to calculate the sum of the C column in one formula in one cell (fx. in "B12"), and not have to add the C column to my sheet.

I hope that explains it...
 
Upvote 0
Sorry wrong formula .. this should be correct:

Type (A)Power (B)CalcValue (C)(D)Type (E)Base (F)
A524A3
A912B6
B00C5
B112D4
C680
C20
C30
D70
D0256
D01024

<tbody>
</tbody>


The calculation formula for C2 is : =IF(B2>0;VLOOKUP(A2;$E$2:$F$5;2;FALSE)*2^B2;0)
 
Upvote 0
Can you please explain how 3*2^5 = 24 ?

And how is C11 = 1,024? I thought you wanted C11 to be 0 because your IF statement says if B11 > 0 then 0. But, then why is it 1,024?
 
Upvote 0
I guess it shows how tired I was when I posted... numbers in column B should be as follows:

3,2,0,1,4,0,0,0,6,8
 
Upvote 0
I am deeply sorry for the messed up posting above and appreciate your interest and help. I have worked on this a little bit more ..

Type (A)Power (B)CalcValue (C)(D)Type (E)Base (F)
A324A3
A212B6
B00C5
B112D4
C480
C00
C00
D00
D6256
D81024

<tbody>
</tbody>

The single-cell array formula for C2:C11 is: {
=IF(B2:B11>0;VLOOKUP(A2:A11;$E$2:$F$5;2;FALSE)*2^B2:B11;0)}.

The sum of the values in C2:C11 is 1408.

The multi-cell array formula: {=SUM(IF(B2:B11>0;VLOOKUP(A2:A11;$E$2:$F$5;2;FALSE)*2^B2:B11;0))} and returns 1050.

I would have expected the multi-cell array formula to return 1408.
 
Upvote 0
Hi Neuron,

You were almost there. Please keep in mind that the lookup_value argument in the VLOOKUP function cannot handle an array of values, but the criteria argument of the SUMIF/SUMIFS functions can handle an array of values.

Try out the below. Enter the array formula in F2 with Ctrl+Shift+Enter, not just Enter.

Excel Workbook
ABCDEF
1TypePower**Array1408
2A3****
3A2**TypeBase
4B0**A3
5B1**B6
6C4**C5
7C0**D4
8C0****
9D0****
10D6****
11D8****
Sheet6
 
Last edited:
Upvote 0
You sir, are a genius!

Thank you so very much for the help, it will enable me to get rid of some very heavy vba custom functions.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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