Forum Discussion
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 w...
=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.
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), "")
)Patrick2788 Thanks, I'll take a look.
