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

[chrono2483]

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!

 

[FlameRetired]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Day of Week	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
2	WeekNum	1	1	1	1	1	1	1	2	2	2	2	2	2	2
3	Name	1/1/2017	1/2/2017	1/3/2017	1/4/2017	1/5/2017	1/6/2017	1/7/2017	1/8/2017	1/9/2017	1/10/2017	1/11/2017	1/12/2017	1/13/2017	1/14/2017
4	Paula Smith	-	-	-	23	26	30	-	45	65	99	99	-	-	-
5	John Doe	56	57	88	98	76	44	56	44	43	41	38	36	39	40
6	Jane Doe	99	99	99	100	101	111	112	79	77	74	100	101	112	99
7	Craig Lowes
8	Peter Oz
9
10
11
12	John Doe	67.85714
13	1
Sheet1

Cell Formulas
Range	Formula
B12	{=AVERAGE(IF(($A$4:$A$8=A12)*(A13=B2:O2),$B$4:$O$8))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[chrono2483]

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

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
Day of Week	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
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?

 

[chrono2483]

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!!!

 

[FlameRetired]

Glad to hear. Thank you for the feedback.