Thread: Repeating Variables-VLOOKUP or INDEX/MATCH?

quick note: What I'm trying to get to isn't as complicated as it looks below (I hope) & is much easier to understand in the attachment.

Hello! I'm reallllly hoping some one can help. I'm working on a spreadsheet (sample attached). There are 3 variables: Customer code, Account, & Amount. There are multiple customer codes, but only three accounts (each customer code has the same 3 accounts: 1, 2 & 3).
I get a workbook for each account (but it's just one worksheet for each, so for simplicity here in my Sample it's tabs 1, 2, & 3) and then I create a master worksheet summarizing the data: it shows the totals by account, for each customer code (so 3 rows-1 for each account-per customer code).

Currently I'm using the following formula, adjusting it by tab for each account:
=IF(ISERROR(VLOOKUP(A2,'1'!$A$2:$B$8,2,FALSE)),0,VLOOKUP(A2,'1'!$A$2:$B$8,2,FALSE))

Problems
Main: Customer codes appear repeatedly in each account tab,as seen in tab 1. The current formula does not pick that up--it only takes the value for the first customer code, when I need the sum of the values for that customer code. So I'm currently manually adding these for the related codes.

2. this may be difficult, but since I'm already airing my dirty laundry/excel sheets... For a (very few) number of customers, there are two customer codes (two max though, never more) for that customer that should both be included the subtotal (some accounts have both codes, some have only one of the two).
>In the Sample this is shown in rows 11-15. For Customer "BP" both "BP" and "14"(cell A11) customer codes apply (& in my acutal workbook its displayed the exact same way).
>In these cases, some accounts have both codes, some have only one or the other (shown in Sample: Account 1 has "14" only, Account 2 has both, Account 3 has "BP" only).

3. (this may not be relevant--depends on the solution to 1 & 2)
Customer codes are mainly just 2 letters, but a few are numeric. For the numeric ones, I seem to randomly have trouble getting the formula to work (I've synched the formatting for the master sheet & tabs, tried formatting both as a number, both as text, nothing gives). This is shown in the yellow cell, D9. The formula works in D7 for tab 1, but not in D9 for tab 3.

What is the best way to approach/solve this? I can't decide if I'm better off using VLOOKUP or an INDEX/MATCH combination (or maybe another alternative?) Many thanks!

You can use sumif and indirect function to process it.
See attached.

Hope this helps,
windknife

Thanks! What's the risk/reason to avoid A:A? I had been using it to ensure that any new rows that get added (to the end of the column) are included in the formula.