VLOOKUP mult. values and sum result?

[BradMichaels]

I have a question on the VLOOKUP function.

I have a spreadsheet that lists out clients with their account numbers and the revenue they earn (B2:D7). I have a data range (F2:G22) that lists revenue by account number. Some clients have multiple accounts and I'd like the vlookup result to show the sum of the revenue for the multiple accounts. If possible, how would I structure that formula?

 

[cjcobra]

You could do something like this, but it would change depending on how many accts you are looking up.

=vlookup(left(c3,4),f2:g22,2,0)+vlookup(mid(c3,6,4),f2:g22,2,0)+ ect...

 

[Domenic]

Are the account numbers all four digits in length, as per your example? If so, try...

D3, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(ISNUMBER(MATCH($F$2:$F$22,MID(SUBSTITUTE(C3," ",""),ROW(INDIRECT("1:"&LEN(C3)-LEN(SUBSTITUTE(C3,",",""))+1))*5-5+1,4)+0,0)),$G$2:$G$22))

or

D3, confirmed just with ENTER, and copied down:

=SUMPRODUCT(SUMIF($F$2:$F$22,MID(SUBSTITUTE(C3," ",""),ROW(INDIRECT("1:"&LEN(C3)-LEN(SUBSTITUTE(C3,",",""))+1))*5-5+1,4),$G$2:$G$22))

 

[BradMichaels]

You could do something like this, but it would change depending on how many accts you are looking up.

=vlookup(left(c3,4),f2:g22,2,0)+vlookup(mid(c3,6,4),f2:g22,2,0)+ ect...

Thanks, a few additional questions on your answer:

In the formula you provided, for the second vlookup formula, is the purpose of '(C3,6,4)' saying in cell C3 starting at character 6 use next 4 characters to match?

Continuing with your pattern (left, mid, etc) what would the last one be right? What if I had 4 account numbers?

 

[BradMichaels]

Are the account numbers all four digits in length, as per your example?

Thanks. Yes, they're all 4 digits long.

 

[BradMichaels]

Are the account numbers all four digits in length, as per your example? If so, try..

Used the second formula you provided and it worked perfectly. I wish I could understand how it works though. What would have to change if I switched my account number to a different length?

 

[Domenic]

Thanks. Yes, they're all 4 digits long.

Have you tried the formulas? If so, do they return the desired result?

 

[BradMichaels]

Have you tried the formulas? If so, do they return the desired result?

Yes they both work.

What would I have to change if I switched my account number to a different length? Sorry, I don't fully understand the functions of the formula to figure it out myself.

 

[T. Valko]

I have a question on the VLOOKUP function.

I have a spreadsheet that lists out clients with their account numbers and the revenue they earn (B2:D7). I have a data range (F2:G22) that lists revenue by account number. Some clients have multiple accounts and I'd like the vlookup result to show the sum of the revenue for the multiple accounts. If possible, how would I structure that formula?

This would be so much easier if you put each acct number in its own cell:

Book1
	A	B	C	D	E
1	Revenue	Client	Acct(s)	_	_
2	500	A	1001	1004	_
3	200	B	1002	_	_
4	1200	C	1003	1004	1005
Sheet1

Book1
	G	H
1	Acct	Revenue
2	1001	100
3	1002	200
4	1003	300
5	1004	400
6	1005	500
Sheet1

Then, the formula entered in cell A2 and copied down:

=SUMPRODUCT(SUMIF(G$2:G$6,C2:E2,H$2:H$6))

 

[BradMichaels]

This would be so much easier if you put each acct number in its own cell:

I agree, but that's not going to happen although I'm making a case for it. The example I provided was just to explain what I'm trying to figure out how to do as I do multiple account summations manually today.