How to get the next matching item in row Excel-CodePudding

I have a table with data and I'm struggling with getting the next match data in the row. I've tried many options with no success and every time I'm getting the same solution. can someone help/guide me to the better solution? I've got pic that explain the problem:

CodePudding user response：

Try this 2 formulas solution use for Excel 2010 and up

1] In "Output, ID" A13, formula copied across right to C13 and all copied down :

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$6)/($D$2:$H$6=$K$1),ROW($A1))),"")

2] In "Output, Date" D13, formula copied down :

=IFERROR(INDEX($1:$1,RIGHT(AGGREGATE(15,6,ROW(A$2:A$6)/1% COLUMN(D:H)/(D$2:H$6=K$1),ROW(A1)),2)),"")

Or,

Using this single array (CSE) formula for Excel 2019 and up

In "Output, ID" A13, formula copied across right to D13 and all copied down :

=IFERROR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IF($D$2:$H$6=$K$1,$A$2:$A$6&"</b><b>"&$B$2:$B$6&"</b><b>"&$C$2:$C$6&"</b><b>"&$D$1:$H$1,""))&"</b></a>","//b["&(ROW($A1)*4 COLUMN(A$1))-4&"]"),"")

confirm pressing with "Ctrl Shift Enter" to enter