# Vlookup And Averaging All Of The Matches Not Just First Match

#### ExcelNoob989

##### New Member
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
2X12345John Doe1234
3D123821Bob Dole3456
4Z9938John Smith7890
Customer

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

Book3
AB
2X12345100
3X12345101
4X12345102
5X1234588
6X1234599
7X1234588
8X12345102
9X12345103
10D12382177
11D12382178
12D12382177
13D12382178
14D12382179
15D12382188
16Z9938115
17Z9938120

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Fluff

##### MrExcel MVP, Moderator
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
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
Thanks for that, did you have a look at the Averageif function?

#### ExcelNoob989

##### New Member

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
Like
+Fluff 1.xlsm
ABCDEFG
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
Like
+Fluff 1.xlsm
ABCDEFG
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
You're welcome & thanks for the feedback.

Replies
31
Views
390
Replies
21
Views
340
Replies
1
Views
177
Replies
4
Views
309
Replies
18
Views
195

1,129,660
Messages
5,637,626
Members
416,977
Latest member
kdoederlein

### 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.

### Which adblocker are you using?

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

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