Vlookup And Averaging All Of The Matches Not Just First Match

[ExcelNoob989]

Hey There,

I am struggling to find an excel solution to help gather some data on some customer internet speeds.

I have three sheets.

On the first (customer) sheet, I want to fill in columns B and C with data from the 2nd and 3rd sheets.

I am able to write a VLOOKUP function to match the Serial number in Column A on customer sheet to Column A on download or upload sheet and display the 2nd value to give me the speed value.

However, this only gives me the 1st instance that vlookup matches and I need the average of all values for that serial number not just the 1st vlookup. The other catch is that I may have 2 results for one serial number and 22 results for another.


Any idea’s how I can make this work? Am I on the right track with vlookup or should I be looking at something different? Thank you so much for your help!

CUSTOMER SHEET

Book3
	A	B	C	D	E
1	Serial Number	Average Download	Average Upload	Customer Name	Account #
2	X12345			John Doe	1234
3	D123821			Bob Dole	3456
4	Z9938			John Smith	7890
Customer

DOWNLOAD SHEET

Book3
	A	B
1	Serial Number	Download Speed Result
2	X12345	78
3	X12345	80
4	X12345	88
5	X12345	85
6	X12345	99
7	X12345	100
8	X12345	110
9	X12345	115
10	D123821	77
11	D123821	78
12	D123821	66
13	D123821	44
14	D123821	55
15	D123821	77
16	Z9938	100
17	Z9938	105
Download

UPLOAD SHEET

Book3
	A	B
1	Serial Number	Upload Speed Result
2	X12345	100
3	X12345	101
4	X12345	102
5	X12345	88
6	X12345	99
7	X12345	88
8	X12345	102
9	X12345	103
10	D123821	77
11	D123821	78
12	D123821	77
13	D123821	78
14	D123821	79
15	D123821	88
16	Z9938	115
17	Z9938	120
Upload

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Have a look at the AVERAGEIF function.

 

[ExcelNoob989]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Have a look at the AVERAGEIF function.

Thank you I have updated my profile

 

[Fluff]

Thanks for that, did you have a look at the Averageif function?

 

[ExcelNoob989]

Thanks for that, did you have a look at the Averageif function?

Doing some more reading on it now but havn't quite got it figured out.

 

[Fluff]

Like

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	Serial Number	Download Speed Result				Serial Number	Average Download
2	X12345	78				X12345	94.375
3	X12345	80				D123821	66.16666667
4	X12345	88				Z9938	102.5
5	X12345	85
6	X12345	99
7	X12345	100
8	X12345	110
9	X12345	115
10	D123821	77
11	D123821	78
12	D123821	66
13	D123821	44
14	D123821	55
15	D123821	77
16	Z9938	100
17	Z9938	105
18
19
Test

Cell Formulas
Range	Formula
G2:G4	G2	=AVERAGEIFS(B:B,A:A,F2)

 

[ExcelNoob989]

Like

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	Serial Number	Download Speed Result				Serial Number	Average Download
2	X12345	78				X12345	94.375
3	X12345	80				D123821	66.16666667
4	X12345	88				Z9938	102.5
5	X12345	85
6	X12345	99
7	X12345	100
8	X12345	110
9	X12345	115
10	D123821	77
11	D123821	78
12	D123821	66
13	D123821	44
14	D123821	55
15	D123821	77
16	Z9938	100
17	Z9938	105
18
19
Test

Cell Formulas
Range	Formula
G2:G4	G2	=AVERAGEIFS(B:B,A:A,F2)

I

I was able to take what you had pasted here and make that work!

Thank you so much for the help!

 

[Fluff]

You're welcome & thanks for the feedback.