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!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
736
Office Version
365
Platform
Windows
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.
 

Neuron

New Member
Joined
Sep 22, 2016
Messages
7
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...
 

Neuron

New Member
Joined
Sep 22, 2016
Messages
7
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)
 

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
736
Office Version
365
Platform
Windows
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?
 

Neuron

New Member
Joined
Sep 22, 2016
Messages
7
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
 

Neuron

New Member
Joined
Sep 22, 2016
Messages
7
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.
 

CyrusTheVirus

Well-known Member
Joined
Jan 28, 2015
Messages
736
Office Version
365
Platform
Windows
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:

Neuron

New Member
Joined
Sep 22, 2016
Messages
7
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,234
Messages
5,443,271
Members
405,222
Latest member
Summer01

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top