Using Table Arrays to extract unique data from table

[cchampagne17]

Good afternoon,
I'm trying to create an automatic table using an array function I found online... However when I tried to apply the formula to my table it finds an error. What I am trying to do is type in a date in cell C10, then the array function returns unique overall grades from that date.

The array formula I entered into E10 (and dragged down..) is..
=INDEX(Item, SMALL(IF(($C$10=Date)*(COUNTIF($E$9:E9,Grade)=0), ROW(Date)-MIN(ROW(Date))+1, ""), 1))

I did hold down Shift+CTRL when I hit enter when I modified the formula. I named B3:B8 "Date", as well as F3:F8 "Grade" for name references. I also made sure that the formula is linking the names and not the date function or whatnot.

If anyone can help me figure out what is wrong with the formula that would be extremely helpful. Thank you

Chris


Below is the table that I am referring to..


[TABLE="class: grid, width: 500"]
<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]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Comments[/TD]
[TD]Yes Total[/TD]
[TD]No Total[/TD]
[TD]Overall Grade[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]7/24/2014[/TD]
[TD][/TD]
[TD]18[/TD]
[TD]11[/TD]
[TD]62.07%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]7/24/2014[/TD]
[TD][/TD]
[TD]25[/TD]
[TD]16[/TD]
[TD]60.98%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]7/24/2014[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]32[/TD]
[TD]31.91%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]7/24/2014[/TD]
[TD][/TD]
[TD]17[/TD]
[TD]33[/TD]
[TD]34%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]7/24/2014[/TD]
[TD][/TD]
[TD]25[/TD]
[TD]23[/TD]
[TD]52.08%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]7/29/2014[/TD]
[TD][/TD]
[TD]31[/TD]
[TD]14[/TD]
[TD]68.89%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]Search[/TD]
[TD][/TD]
[TD]Search Results[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[cchampagne17]

Also for reference... this is where I found the formula:
Vlookup – Return multiple unique distinct values in excel | Get Digital Help - Microsoft Excel resource

 

[skywriter]

Let me know if this is what you are after.

Excel 2010
	B	C	D	E	F	G
2	Date	Comments	Yes Total	No Total	Overall Grade
3	7/24/2014		18	11	62.07%
4	7/24/2014		25	16	60.98%
5	7/24/2014		15	32	31.91%
6	7/24/2014		17	33	34%
7	7/24/2014		25	23	52.08%
8	7/29/2014		31	14	68.89%
9
10	Search	7/24/2014	Search Results	0.6207
11				0.6098
12				0.3191
13				0.34
14				0.5208
15				#NUM!
Sheet35

Cell Formulas
Range	Formula
E10	{=INDEX(Grade, SMALL(IF(($C$10=Date)*(COUNTIF($E$9:E9,Grade)=0), ROW(Date)-MIN(ROW(Date))+1, ""), 1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Date	=Sheet35!$B$3:$B$8
Grade	=Sheet35!$F$3:$F$8

 

[cchampagne17]

Let me know if this is what you are after.

Excel 2010

	B	C	D	E	F	G
Date	Yes Total	No Total	Overall Grade
Search	Search Results

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

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

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

[TD="align: center"]3[/TD]
[TD="align: right"]7/24/2014[/TD]
[TD="align: center"][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]62.07%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]7/24/2014[/TD]
[TD="align: center"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]60.98%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]7/24/2014[/TD]
[TD="align: center"][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]31.91%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]7/24/2014[/TD]
[TD="align: center"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]34%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7/24/2014[/TD]
[TD="align: center"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]52.08%[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]7/29/2014[/TD]
[TD="align: center"][/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]68.89%[/TD]
[TD="align: right"][/TD]

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

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

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

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

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

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

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

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.5208[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet35​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E10[/TH]
[TD="align: left"]{=INDEX(Grade, SMALL(IF(($C$10=Date)*(COUNTIF($E$9:E9,Grade)=0), ROW(Date)-MIN(ROW(Date))+1, ""), 1))}[/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]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Date[/TH]
[TD="align: left"]=Sheet35!$B$3:$B$8[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]Grade[/TH]
[TD="align: left"]=Sheet35!$F$3:$F$8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Yes! Worked great thank you!