VLOOKUP + SUM or SUMIF?

[jod227]

I have 3 lists each with 2 columns. Within the 2nd column of each are values I need to be added together. The keys within each lists' left column are mostly matching, however each list has its own variation (ex. on might have a unique key within their left column, different than the others).

[TABLE="width: 100"]
<tbody>[TR]
[TD]1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 100"]
<tbody>[TR]
[TD]1[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]32[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 100"]
<tbody>[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]42[/TD]
[/TR]
</tbody>[/TABLE]

I need to obtain and match the sum with each key on the left. I've tried summing each vlookup but it doesn't seem to work. Any guidance would greatly help on this.

 

[jod227]

BTW the lists are next to each other in the spreadsheet, not within the same columns as they are here. Just couldn't figure out how to make that happen in the post

 

[Marcílio_Lobão]

Jod227, Good afternoon.

I did this little example for you.
11-07-2014_Suggestion_to_JOD227-OK.xlsx

Is it what you want?
I hope it helps.

--
Belo Horizonte, Brazil
Marcílio Lobão

 

[jod227]

That's very good, thanks! However would that formula return the exact same result as this?

=SUM(((VLOOKUP(B3,$B$3:$C$922,2,FALSE))+VLOOKUP(B3,$I$3:$J$921,2,FALSE))+VLOOKUP(B3,$O$3:$P$927,2,FALSE))

 

[Marcílio_Lobão]

YES.
You can use VLOOKUP function.
It's not necessary to use the SUM function.
=VLOOKUP(B3,$B$3:$C$922,2,FALSE)+VLOOKUP(B3,$I$3:$J$921,2,FALSE)+VLOOKUP(B3,$O$3:$P$927,2,FALSE)

But in this case:
a) Each number, 1 or 2 or 4 can appears ONLY one time on each left column.
b) You must use an error control on each VLOOKUP functions to avoid a N/A ERROR and unvalidate your formula.

Have a nice day.
--
Belo Horizonte, Brazil
Marcílio Lobão

 

[Matty]

I have 3 lists each with 2 columns. Within the 2nd column of each are values I need to be added together. The keys within each lists' left column are mostly matching, however each list has its own variation (ex. on might have a unique key within their left column, different than the others).

[TABLE="width: 100"]
<tbody>[TR]
[TD]1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 100"]
<tbody>[TR]
[TD]1[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]32[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 100"]
<tbody>[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]42[/TD]
[/TR]
</tbody>[/TABLE]

I need to obtain and match the sum with each key on the left. I've tried summing each vlookup but it doesn't seem to work. Any guidance would greatly help on this.

I'm not sure I'm following. What result(s) are you looking for? Could you give an example?

Matty

 

[jod227]

Perfect! This has been very helpful, thank you sir, have a nice day as well.

 

[jod227]

Thanks Matty for inquiring Matty!