Index/Match OR VLOOKUP/AVERAGEIF/HLOOKUP to return values

chrono2483

Board Regular
Joined
Aug 23, 2014
Messages
164
Office Version
  1. 2016
Hello Everyone,

I have two sheets: Sheet1 and Sheet2.

Sheet1, contains the raw data.
Sheet2, contains tables with formulas to pull from Sheet1

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Day of Week
[/TD]
[TD]Sunday
[/TD]
[TD]Monday
[/TD]
[TD]Tuesday
[/TD]
[TD]Wednesday
[/TD]
[TD]Thursday
[/TD]
[TD]Friday
[/TD]
[TD]Saturday
[/TD]
[TD]Sunday
[/TD]
[TD]Monday
[/TD]
[TD]Tuesday
[/TD]
[TD]Wednesday
[/TD]
[TD]Thursday
[/TD]
[TD]Friday
[/TD]
[TD]Saturday
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]WeekNum
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Name
[/TD]
[TD]1-Jan-17
[/TD]
[TD]2-Jan-17
[/TD]
[TD]3-Jan-17
[/TD]
[TD]4-Jan-17
[/TD]
[TD]5-Jan-17
[/TD]
[TD]6-Jan-17
[/TD]
[TD]7-Jan-17
[/TD]
[TD]8-Jan-17
[/TD]
[TD]9-Jan-17
[/TD]
[TD]10-Jan-17
[/TD]
[TD]11-Jan-17
[/TD]
[TD]12-Jan-17
[/TD]
[TD]13-Jan-17
[/TD]
[TD]14-Jan-17
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Paula Smith
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]23
[/TD]
[TD]26
[/TD]
[TD]30
[/TD]
[TD]-
[/TD]
[TD]45
[/TD]
[TD]65
[/TD]
[TD]99
[/TD]
[TD]99
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[TD]-
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]John Doe
[/TD]
[TD]56
[/TD]
[TD]57
[/TD]
[TD]88
[/TD]
[TD]98
[/TD]
[TD]76
[/TD]
[TD]44
[/TD]
[TD]56
[/TD]
[TD]44
[/TD]
[TD]43
[/TD]
[TD]41
[/TD]
[TD]38
[/TD]
[TD]36
[/TD]
[TD]39
[/TD]
[TD]40
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Jane Doe
[/TD]
[TD]99
[/TD]
[TD]99
[/TD]
[TD]99
[/TD]
[TD]100
[/TD]
[TD]101
[/TD]
[TD]111
[/TD]
[TD]112
[/TD]
[TD]79
[/TD]
[TD]77
[/TD]
[TD]74
[/TD]
[TD]100
[/TD]
[TD]101
[/TD]
[TD]112
[/TD]
[TD]99
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Craig Lowes
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Peter Oz
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]








;

What I would like to do is have a formula that will do similar to a VLOOKUP to match the name of the client. Then, based on the weeknum, do an AVERAGEIF of all the values that fall within that range, for that name. What it will return is the average for that week, for that client.

Example: John Doe would return 67.8, for Week 1 // Jane Doe would return 103 for Week 1 // Paula Smith would return 77, for Week 2.

I've been playing around with this formula that works in a similar scenario, with no success:

=IFERROR(VLOOKUP($A$5,Sheet1!$A:$A,AVERAGEIF(SUMPRODUCT(--(Sheet1!$2:$2=Sheet2!$C$1),Sheet1!7:7)),FALSE),0)

Where $A$5 = Client's name, $C$1 = Week Number


Am I missing something with my formula? Any help would be great. TY!
 

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
Try array entering this formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.


Excel 2012
ABCDEFGHIJKLMNO
1Day of WeekSundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
2WeekNum11111112222222
3Name1/1/20171/2/20171/3/20171/4/20171/5/20171/6/20171/7/20171/8/20171/9/20171/10/20171/11/20171/12/20171/13/20171/14/2017
4Paula Smith---232630-45659999---
5John Doe5657889876445644434138363940
6Jane Doe99999910010111111279777410010111299
7Craig Lowes
8Peter Oz
9
10
11
12John Doe67.85714
131
Sheet1
Cell Formulas
RangeFormula
B12{=AVERAGE(IF(($A$4:$A$8=A12)*(A13=B2:O2),$B$4:$O$8))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Try array entering this formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Excel 2012
ABCDEFGHIJKLMNO
Day of WeekSundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
WeekNum
Name
Paula Smith-------
John Doe
Jane Doe
Craig Lowes
Peter Oz
John Doe

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]1/2/2017[/TD]
[TD="align: right"]1/3/2017[/TD]
[TD="align: right"]1/4/2017[/TD]
[TD="align: right"]1/5/2017[/TD]
[TD="align: right"]1/6/2017[/TD]
[TD="align: right"]1/7/2017[/TD]
[TD="align: right"]1/8/2017[/TD]
[TD="align: right"]1/9/2017[/TD]
[TD="align: right"]1/10/2017[/TD]
[TD="align: right"]1/11/2017[/TD]
[TD="align: right"]1/12/2017[/TD]
[TD="align: right"]1/13/2017[/TD]
[TD="align: right"]1/14/2017[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]30[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]56[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]40[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]99[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]111[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]112[/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]67.85714[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B12[/TH]
[TD="align: left"]{=AVERAGE(IF(($A$4:$A$8=A12)*(A13=B2:O2),$B$4:$O$8))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Thanks FlameRetired!

I've tried the above - and it does return a value [i had to define the search range for the name ie. A4:A8 instead of A:A]. However, the value returned is not the average of the range in question.

Any idea/workaround?
 
Last edited:
Upvote 0
Thanks FlameRetired!

I've tried the above - and it does return a value [i had to define the search range for the name ie. A4:A8 instead of A:A]. However, the value returned is not the average of the range in question.

Any idea/workaround?




++ I played around with my ranges, and was able to get it to work. Thank you so much - works perfectly!!! :)
 
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,164
Members
453,021
Latest member
Justyna P

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
Back
Top