Index , Vlookup Average Formula

[Sparda142]

Hello,

I am trouble creating a formula to go into Cells B2:I2, Im trying to have Excel say Find the Text "Scheduled Date" match the date below with a date in Row 1 and provide the information in Column M for Test 1 , Only Information from the Text "Scheduled Date to Subtotal" Belongs to Test 1. I have tried using a few IFS formula with combination of Vlookup but have failed.
Please help me create a formula that will do the job Excel Gods and have mercy on me! ray:



[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[TD]Column H[/TD]
[TD]Column I [/TD]
[TD]Column J[/TD]
[TD]Column K[/TD]
[TD]Column
L[/TD]
[TD]Column M[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Test G[/TD]
[TD]3/24[/TD]
[TD]3/25[/TD]
[TD]3/25[/TD]
[TD]3/26[/TD]
[TD]3/27[/TD]
[TD]3/28[/TD]
[TD]3/29[/TD]
[TD]3/30[/TD]
[TD]Total Average [/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Test 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Test 1[/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Test 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Schedule Date[/TD]
[TD]N/A[/TD]
[TD]Total Ad[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Test 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/26[/TD]
[TD]N/A[/TD]
[TD]93.47%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Test 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/28[/TD]
[TD]N/A[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Test 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/29[/TD]
[TD]N/A[/TD]
[TD]91.07&[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Test 6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Subtotal:[/TD]
[TD]N/A[/TD]
[TD]94.85%[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Test 2[/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Schedule Date[/TD]
[TD]N/A[/TD]
[TD]Total Ad[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/24
[/TD]
[TD]N/A[/TD]
[TD]21.41%[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/25[/TD]
[TD]N/A[/TD]
[TD]67.25%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Subtotal:[/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Test 3[/TD]
[TD]N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/29[/TD]
[TD]N/A[/TD]
[TD]41.44%[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3/30
[/TD]
[TD]N/A[/TD]
[TD]99.99%[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

Oh in that example you're skipping names, so:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1		24-Mar	25-Mar	26-Mar	27-Mar	28-Mar	29-Mar	30-Mar	empty Column	NAME & Dates	Percent
2	Jacob	100%	#N/A	#N/A	25%	#N/A	#N/A	#N/A		Jacob
3	Scott	80%	#N/A	#N/A	75%	#N/A	#N/A	80%		24-Mar	100%
4	Tim	#N/A	#N/A	#N/A	#N/A	#N/A	99%	#N/A		27-Mar	25%
5	Matt	#N/A	#N/A	#N/A	#N/A	63%	#N/A	#N/A		Jill
6	Howard	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!		25-Mar	30%
7										26-Mar	100%
8										27-Mar	99%
9										Scott
10										24-Mar	80%
11										27-Mar	75%
12										30-Mar	80%
13										Tim
14										29-Mar	99%
15										Matt
16										28-Mar	63%
17										Howard
Sheet7 (2)

Cell Formulas
Range	Formula
B2	=VLOOKUP(B$1,OFFSET(INDEX($J$2:$J$17,MATCH($A2,$J$2:$J$17,0)),,,MATCH("*",INDEX($J$2:$J$17,1+MATCH($A2,$J$2:$J$17,0)):$J$17,0),2),2,0)

 

[Sparda142]

Thank You so much everyone! You guys are awesome.