excelexcel-formulaexcel-2019

How to transpose horizontal data into vertical form with date match using formula


I have been trying to transpose data from a horizontal list (Table A) into vertical set (Table B) with the condition of date and enroll match.

Table A

Table A

Table B

Table B

I would like to have one person data at a time. For example, one enroll data will be manually put in the Cell K2; this should result into getting shift data returned into Column N correspondingly.

I use MS Excel 2019 version.

Thanks in advance!


Solution

  • Here is one way of doing this:

    Solution is based on OP's post which is clearly written:

    I use MS Excel 2019 version.


    enter image description here


    • Formula used in cell B7

    =VLOOKUP(A7,$A$2:$B$4,2,0)
    

    • Formula used in cell C7 --> This can be made dynamic based on Enroll and Name as well.

    =TRANSPOSE(C1:I1)
    

    • Formula used in cell D7

    =IFERROR(INDEX($C$2:$I$4,MATCH(1,($A$7=$A$2:$A$4)*($B$7=$B$2:$B$4),0),MATCH($C7,$C$1:$I$1,0)),"")