Forum Discussion
insert DATA from one sheet to another..
=IFERROR(INDEX(Sheet1!$B$3:$B$500,SMALL(IF(MMULT(N(Sheet1!$G$3:$L$500=Sheet2!B$3),ROW($1:$6)^0)=1,ROW($1:$498)),ROW(1:1))),"")You are welcome. With this formula you can enter employees in range B3:B500 and their corresponding dates in range G3:L500.
6 Replies
=IFERROR(INDEX($A$2:$A$20,SMALL(IF(MMULT(N($B$2:$E$20=G$1),ROW($1:$4)^0)=1,ROW($1:$19)),ROW(1:1))),"")You can try 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 G2 and filled across range G2:J12 in the example. This can be done across two worksheets accordingly. In cells G1:J1 are dates 01.01.2023 and so on formatted as "mmmm".
This is definitely the kind of thing i am looking for, i am trying to paste this formula and trying to adjust to the table i have, however its not bringing up the names.... How do i add my 'test' sheet (for GDPR purposes). so that you can see my layout? i have added my 'test' workbook, for you to see ?
=IFERROR(INDEX(Sheet1!$B$3:$B$12,SMALL(IF(MMULT(N(Sheet1!$E$3:$J$12=Sheet2!B$3),ROW($1:$6)^0)=1,ROW($1:$10)),ROW(1:1))),"")In your test file i've adapted the formula and it returns the intended results.
