sum and vlookup

mopey12345

New Member
Joined
Nov 26, 2020
Messages
31
Office Version
  1. 2010
Platform
  1. Windows
I have 5 named tables W1T,W2T,W3T,W4T,W5T each on a separate sheet which has a variable a list of names in column A . I need to add items from each occurrence of the same name from the 5 tables on a summary sheet. This formula works but can it be simplified, or would a VBA routine be better? Thanks in advance Phil =SUM(IFERROR(VLOOKUP($A2,W1T,6,FALSE),0),IFERROR(VLOOKUP($A2,W2T,6,FALSE),0),IFERROR(VLOOKUP($A2,W3T,6,FALSE),0),IFERROR(VLOOKUP($A2,W4T,6,FALSE),0),IFERROR(VLOOKUP($A2,W5T,6,FALSE),0))
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

mopey12345

New Member
Joined
Nov 26, 2020
Messages
31
Office Version
  1. 2010
Platform
  1. Windows
Tried INDEX MATCH but same length of formula. =SUM(IFERROR(INDEX(W1T,MATCH($A2,M_WK1!$A$2:$A$220,0),E$230),0),IFERROR(INDEX(W2T,MATCH($A2,M_WK2!$A$2:$A$220,0),E$230),0),IFERROR(INDEX(W3T,MATCH($A2,M_WK3!$A$2:$A$220,0),E$230),0),IFERROR(INDEX(W4T,MATCH($A2,M_WK4!$A$2:$A$220,0),E$230),0),IFERROR(INDEX(W5T,MATCH($A2,M_WK5!$A$2:$A$220,0),E$230),0))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,016
Office Version
  1. 365
Platform
  1. Windows
This formula works but can it be simplified
I assume that you mean 'shortened' as the current formula is simple, just a bit long.
If that is correct then I would say that there is nothing practical that you could do with it. If you are going to be adding more tables over time then maybe vba methods would be worth considering, but for what you have at present the existing formula is probably the most practical solution.
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
1,003
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Agreed. What's understood by simplify? Like you said in #3.
Simple for me is Power Query. No formulas.
 

mopey12345

New Member
Joined
Nov 26, 2020
Messages
31
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

I assume that you mean 'shortened' as the current formula is simple, just a bit long.
If that is correct then I would say that there is nothing practical that you could do with it. If you are going to be adding more tables over time then maybe vba methods would be worth considering, but for what you have at present the existing formula is probably the most practical solution.
Thanks, response much appreciated . 5 weeks of data will be the max so I'll stick with vlookup.
 

mopey12345

New Member
Joined
Nov 26, 2020
Messages
31
Office Version
  1. 2010
Platform
  1. Windows
I assume that you mean 'shortened' as the current formula is simple, just a bit long.
If that is correct then I would say that there is nothing practical that you could do with it. If you are going to be adding more tables over time then maybe vba methods would be worth considering, but for what you have at present the existing formula is probably the most practical solution.
Thanks. Well put. Just is very long.
 

Forum statistics

Threads
1,144,703
Messages
5,725,839
Members
422,646
Latest member
matgabgrp

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
Top