Urgent Please Help! Look up a value and return multiple instances and data relating to it

I know this has probably already been discussed but I cant seem to find the post (and am running out of time on a project). I am creating a formula to look up a name in column A and return dates relating to that name from Column B. Here's the formula I am using. It works in the first instance but when I autofill, it comes up as #NUM. What am I doing wrong?

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)

I have tried copying and pasting as well as dragging the formula down.

Try entering it with CTRL SHIFT ENTER and not just enter - it is an array formula

I believe that is a Array formula, so instead of entering with Enter, you have to hold Cntrl + Shift + Enter, it will be entered right when you see { at start and } at the end of the formula, then copy it down

Try it like this...

Let's assume you want the results starting in cell D2 downwards.

Array entered** in D2:

=INDEX($B:$B,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROWS(D$2:D2)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down as needed.

In your file the data on the Main sheet is sorted/grouped together by name.

So, you can use a more efficient formula.

Enter this formula on the Invoice sheet in cell B1. This will return the count of records for the name in cell A1.

=COUNTIF(Main!A:A,A1)

Enter this formula in A4:

=IF(ROWS(A$4:A4)>B$1,"",INDEX(Main!B$4:B$17,MATCH(A$1,Main!A$4:A$17,0)+ROWS(A$4:A4)-1))

Copy down until you get blanks.

If you want all the info returned...

Entered in B4:

=IF(ROWS(A$4:A4)>$B$1,"",INDEX(Main!B$4:B$17,MATCH($A$1,Main!$A$4:$A$17,0)+ROWS(A$4:A4)-1))

Copy across to F4.

You'll have to format the cells B4, D4:F4 in a number format of your choice.

Once you have the formats set select the range A4:F4 and copy down until you get blanks.

You're welcome. Thanks for the feedback!

I'd have to see a before and after file to see what's happening.

I can't open *.docx files.

Maybe someone else can help.