Using =sum for duplicate ID in VLOOKUP

[RBerry]

Hi, first post so hope I’m doing it correct, but here goes:

I own a business, and my employees have ID numbers.
I use VLOOKUP for their names, but our system sometimes leaves the names off and just uses their ID numbers.

How would I find their duplicate sales and add it into one cell using a VLOOKUP formula?

The closest I have got is: =sum(VLOOKUP(C4, sales, $c$2:$j$150,8,FALSE)+VLOOKUP(C4, sales, $c$2:$j$150,8,FALSE)
I know this is wrong because it added the same cell twice, and I only want it to “sum” if it’s a duplicate?

Thanks in advance

 

[keef2]

Something like this should work i think? Do you mean duplicate for employee ID? That's what I assumed.

Book1
	A	B	C	D
1	ID	Emp Name		Sales
2	123	Jon Doe		$ 1,000.00
3	234	Suzy Cook		$ 1,500.00
4	345	Keith Miller		$ 2,000.00
5	123	Jon Doe		$ 750.00
6	345	Keith Miller		$ 650.00
7	123	Jon Doe		$ 500.00
8	234	Suzy Cook		$ 1,200.00
9	567	Mike Jordan		$ 5,000.00
10	891	Dennis Rodman		$ 4,876.00
11	891	Dennis Rodman		$ 3,587.00
12	Total			$ 21,063.00
13
14	ID	Total Sales
15	123	$ 2,250
16	234	$ 2,700
17	345	$ 2,650
18	567	$ 5,000
19	891	$ 8,463
20
21		$ 21,063
22	check	$ -
Sheet1

Cell Formulas
Range	Formula
D12	D12	=SUM(D2:D11)
A15:A19	A15	=UNIQUE(A2:A11)
B15:B19	B15	=SUMIFS($D$2:$D$11,$A$2:$A$11,A15)
B21	B21	=SUM(B15:B19)
B22	B22	=B21-D12
Dynamic array formulas.

 

[RBerry]

I think you did it, thank you very much, a huge help.

 

[keef2]

Good deal thanks for feedback!

 

[RBerry]

No problem, if i could give you a rating, 5* for sure.
I have a question kind of related to my last post, but slightly different, can i ask it here or in a new thread?

 

[keef2]

No problem, if i could give you a rating, 5* for sure.
I have a question kind of related to my last post, but slightly different, can i ask it here or in a new thread?

Sure thing

 

[RBerry]

OK, so if i had a list of the ID's, but some of the names were missing from the "Names" column, is there a formula that would link the ID to the name.
So if i input only the ID, or only the ID was generated, the cell next to it would automatically generate the correct name?

 

[keef2]

Shouldn't be a problem. You can reference C15:C19 for this example. Let me know if this is what you meant?

Book1.xlsx
	A	B	C	D
1	ID	Emp Name		Sales
2	123	Jon Doe		$ 1,000
3	234	Suzy Cook		$ 1,500
4	345	Keith Miller		$ 2,000
5	123	Jon Doe		$ 750
6	345	Keith Miller		$ 650
7	123	Jon Doe		$ 500
8	234	Suzy Cook		$ 1,200
9	567	Mike Jordan		$ 5,000
10	891	Dennis Rodman		$ 4,876
11	891	Dennis Rodman		$ 3,587
12	Total			$ 21,063
13
14	ID	Total Sales	Name
15	123	2250	Jon Doe
16	234	2700	Suzy Cook
17	345	2650	Keith Miller
18	567	5000	Mike Jordan
19	891	8463	Dennis Rodman
20
21		21063
22	check	0
Sheet2

Cell Formulas
Range	Formula
D12	D12	=SUM(D2:D11)
A15:A19	A15	=UNIQUE(A2:A11)
B15:B19	B15	=SUMIFS($D$2:$D$11,$A$2:$A$11,A15)
C15:C19	C15	=IFERROR(VLOOKUP(A15,A2:B11,2,FALSE),"")
B21	B21	=SUM(B15:B19)
B22	B22	=B21-D12
Dynamic array formulas.

 

[RBerry]

Yes, that is great. Could i expand on that, so say if i had a product range, i could insert a "Product ID", and then it would generate Product Name, Product Description, Buy In Price, Sell Price (This is all in a sheet already, so i would just need to use this as the range)? So there would essentially be 4+ cells within the range to generate. Would it simply be changing the look up range? =IFERROR(VLOOKUP(A15,A2:E11,2,FALSE),"")
The answer, again, is perfect though, thank you

 

[keef2]

Yes, that is great. Could i expand on that, so say if i had a product range, i could insert a "Product ID", and then it would generate Product Name, Product Description, Buy In Price, Sell Price (This is all in a sheet already, so i would just need to use this as the range)? So there would essentially be 4+ cells within the range to generate. Would it simply be changing the look up range? =IFERROR(VLOOKUP(A15,A2:E11,2,FALSE),"")
The answer, again, is perfect though, thank you

If you can upload an example with XL2BB that would be best. Or a sample of your sheet with a screen shot (even if you use snipping tool to capture and paste here)?

First of all its not that simple to just change the range you would most likely need additional lookups that I could easily provide some guidance on with a sample of the data.

Either way there is a solution but since you are using Office 365 there are a few ways to handle this. With your expanded data there maybe a better solution. I just need to understand how the data is inputted.

For example. Product names for same Employee ID's are these always the same product names for all employee's i would guess no. And how do you want to pull the data list all Products in one cell?

Long and short of it is please upload some how a sample of your data and we can get you on the right track.

You may want to post in a new thread just FYI so others don't think its solved and may chime in too. Thanks!