SOLVED

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

Copper Contributor

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

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

5 Replies

# Re: Formula to return date to the next available column in the row.

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), "")
)``````

# Re: Formula to return date to the next available column in the row.

@Patrick2788 Thanks,  I'll take a look.

best response confirmed by RickJC (Copper Contributor)
Solution

# Re: Formula to return date to the next available column in the row.

``=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.

# Re: Formula to return date to the next available column in the row.

Thank you.  I will try that as well.

# Re: Formula to return date to the next available column in the row.

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.
1 best response

Accepted Solutions
best response confirmed by RickJC (Copper Contributor)
Solution

# Re: Formula to return date to the next available column in the row.

``=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.