Vlookup And Averaging All Of The Matches Not Just First Match

ExcelNoob989

New Member
Joined
Mar 30, 2016
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. 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
Book3
ABCDE
1Serial NumberAverage DownloadAverage UploadCustomer NameAccount #
2X12345John Doe1234
3D123821Bob Dole3456
4Z9938John Smith7890
Customer



DOWNLOAD SHEET
Book3
AB
1Serial NumberDownload Speed Result
2X1234578
3X1234580
4X1234588
5X1234585
6X1234599
7X12345100
8X12345110
9X12345115
10D12382177
11D12382178
12D12382166
13D12382144
14D12382155
15D12382177
16Z9938100
17Z9938105
Download



UPLOAD SHEET
Book3
AB
1Serial NumberUpload Speed Result
2X12345100
3X12345101
4X12345102
5X1234588
6X1234599
7X1234588
8X12345102
9X12345103
10D12382177
11D12382178
12D12382177
13D12382178
14D12382179
15D12382188
16Z9938115
17Z9938120
Upload
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 30, 2016
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, did you have a look at the Averageif function?
 

ExcelNoob989

New Member
Joined
Mar 30, 2016
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
Like
+Fluff 1.xlsm
ABCDEFG
1Serial NumberDownload Speed ResultSerial NumberAverage Download
2X1234578X1234594.375
3X1234580D12382166.16666667
4X1234588Z9938102.5
5X1234585
6X1234599
7X12345100
8X12345110
9X12345115
10D12382177
11D12382178
12D12382166
13D12382144
14D12382155
15D12382177
16Z9938100
17Z9938105
18
19
Test
Cell Formulas
RangeFormula
G2:G4G2=AVERAGEIFS(B:B,A:A,F2)
 

ExcelNoob989

New Member
Joined
Mar 30, 2016
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Like
+Fluff 1.xlsm
ABCDEFG
1Serial NumberDownload Speed ResultSerial NumberAverage Download
2X1234578X1234594.375
3X1234580D12382166.16666667
4X1234588Z9938102.5
5X1234585
6X1234599
7X12345100
8X12345110
9X12345115
10D12382177
11D12382178
12D12382166
13D12382144
14D12382155
15D12382177
16Z9938100
17Z9938105
18
19
Test
Cell Formulas
RangeFormula
G2:G4G2=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,032
Messages
5,639,646
Members
417,102
Latest member
bcselect

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top