Forum Discussion
Solved
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
=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.
=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.
- 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.
Thank you. I will try that as well.
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.
