CODEWITHSUNDEEP
excelexcel-formulakeyvlookup
makeMonday
makeMonday

Reputation: 2415

SUM of multiple VLOOKUP

It seems like a simple problem, but I do not manage to solve it. I have the following tables:

Values

| Key | Value |
|-----|-------|
|  A  |   1   |
|  B  |   2   |
|  C  |   3   |

Results

| Foo | Bar |
|-----|-----|
|  A  |  B  |
|  C  |  B  |
|  A  |  A  |
|  B  |  C  |
| ... | ... |

What I am looking for is a final row in the Results table that looks for the key in the Values table, takes its value and sums all the keys in a column (i.e. FOO and BAR). The final result would be:

| Foo | Bar |
|-----|-----|
|  A  |  B  |
|  C  |  B  |
|  A  |  A  |
|  B  |  C  |
|-----|-----|
|  7  |  8  |

I have been trying with different VLOOKUP, INDEX and MATCH functions, but still I am not able. Any ideas?

Upvotes: 0

Views: 7180

Answers (2)

MikeD
MikeD

Reputation: 8941

I asume you want a solution without extra columns. Then you are into Array formulas (a.k.a CSE or ControlShiftEnter functions).

Combination of {=SUM(VLOOKUP(...))} doesn't work, but combination of {=SUM(SUMIF(...))} does:

in A12 enter =SUM(SUMIF($A$1:$A$3;A7:A10;$B$1:$B$3)) and save with Ctrl+Shift+Enter. You then can copy this to B12.

enter image description here

Problem is you will need to change the Array function every time you add values to the list A7:B10 (or you initially make the range sufficiently large) ... this would speak more for extra =VLOOKUP() columns as suggested by CustomX.

Upvotes: 2

CustomX
CustomX

Reputation: 10113

I'm not sure of other solutions, but you could solve this by using an extra 2 columns, E and F for example.

Enter this in column E: =VLOOKUP(C2;$A$1:$B$3;2;0)

Enter this in column F: =VLOOKUP(D2;$A$1:$B$3;2;0)

Pull the formulas down and add a SUM at the bottom of column C and D to calculate columns E and F.

Extra: These are the columns I used for your examples.

  • Key = column A
  • Value = column B
  • Foo = column C
  • Bar = column D

Upvotes: 1

Related Questions