Formula to return date to the next available column in the row.

[RickJC]

Hi I am trying to figure out how to make this work.

I have a list of names on Tab 1 in Column A

On tab 2 I have a list of names and dates Column C and D

I am looking for a formula or VBA that will look at the name in Column A on Tab 1, Check the list on Tab 2 and if the name is found then place the date next to the name on Tab 1 in Column B.  If the name is found a second time then place the next date in Column C and so on across the page for each time the name is found.

Tab A would look like this

Tab B would look like this

Any help would be appreciated

[#2]

@RickJC 

This is a 365 solution.

I'd table the data set and then use the standard REDUCE/VSTACK approach:

=LET(
    organize, LAMBDA(a, v,
        LET(
            dates, TOROW(SORT(FILTER(Table1[Date], Table1[Person] = v))),
            VSTACK(a, dates)
        )
    ),
    IFNA(REDUCE("dates", names, organize), "")
)

[#3]

@Patrick2788 Thanks,  I'll take a look.

[#4 · Accepted solution]

@RickJC 

=IFERROR(INDEX($B$2:$B$22,SMALL(IF($A$2:$A$22=$D2,ROW($A$2:$A$22)-1),COLUMN(A$1))),"")

An alternative for e.g. Excel 2013 could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell E2 and filled across range E2:J9 in this example.

[#5]

@OliverScheurich 

Thank you.  I will try that as well.

[#6]

OK,I did this in 365 and it worked perfectly. Had to re-arrange my original design of the spreadsheet but that is totally fine. Thanks again.