ExcelNoob989
New Member
- Joined
- Mar 30, 2016
- Messages
- 7
- Office Version
- 365
- Platform
- Windows
- MacOS
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
DOWNLOAD SHEET
UPLOAD SHEET
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 |