INDEX MATCH to display unique results

[MarkPr]

Hi,

I have a small issue getting the formula to correctly to pull each unique date record for an exceptions report.

Exceptions tab is where the end user would log each issue and the date (week column is a helper)
Report tab is a sample of the report page I am setting up

Selecting the week from a drop down menu on the report sheet pulls me the first record of the selected week from the exceptions page, which loads each line on the report page with the same result.
I need the report page to list every record for the selected week not just the first.
Secondly any lines on the report sheet that don't require a record i.e there is only 1 record for example need to be left blank.

I have tried the following formula from something else I did but with no avail and cant seem to see what I am missing or doing wrong
p.s it was entered as an Array ctrl/shift/enter

=INDEX(tbl_Exceptions[Date],MATCH(0,COUNTIF(tbl_Exceptions[Week],$K$3,),0))

Any help would be appreciated
Thanks
Mark

[FDibbins]

T start with, you can use thos slightly shorter formula to get the week num...
=IF(B3="","",WEEKNUM(B3))

Try this ARRAY formula...
=IFERROR(INDEX(Exceptions!B:B,SMALL(IF(Exceptions!$D$3:$D$8=Report!$K$3,ROW(Exceptions!$B$3:$B$8)),ROWS($A$1:A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Copy across, then copy down as needed

[MarkPr]

FDibbins,

Cheers the Array formula works a treat, I will spend some time and break it down so I can understand it better for future use

As for the week num, im so used to doing it the other way due to using it with SUMIF and COUNTIF's lol

[FDibbins]

Happy to help and thanks for the feedback

Regarding the weeknum, your way is most certainly not wrong (and I can fully understand habit lol), but using fewer functions is, I believe, more efficient - not to mention a lot less typing

[MarkPr]

Habit does sometimes get the better of us I must admit

I do agree that cleaner formulas are better on multiple levels, and by my own admission my knowledge is getting better all the time the more I play about and with help for folks like yourself

[FDibbins]

Practice makes (approaching) perfect