Vlookup on lookup value which is placed on more than one row

[a1b2c3d4]

One employee id in 2nd image is spread into more than one column. I want them to be in a single row rather than in split rows. While applying vlookup in 1st image only the value of 1st row gets picked. For emp_key 559584 when I use regular vlookup then from 22nd data is blank in 1st row, so in this case I want lookup to identify and pick data from 2nd row till 23rd and from 24th lookup to identify from 3rd row and so on. Someone please assist.

 

[jasonb75]

Assuming that the bottom image is Sheet2, and that the dates in row 1 are the same in both sheets.

In B2 of the first sheet, then fill as needed.

=FILTER(Sheet2!B$2:B$100,Sheet2!$A$2:$A$200=A$2,"")

 

[a1b2c3d4]

Assuming that the bottom image is Sheet2, and that the dates in row 1 are the same in both sheets.

In B2 of the first sheet, then fill as needed.

=FILTER(Sheet2!B$2:B$100,Sheet2!$A$2:$A$200=A$2,"")

Thanks Jason, by using the formula provided by you, it is just replicating the same data in the same format from Sheet 2 to Sheet1. In sheet 2 there are 6 records of same employee as you can view the image in my initial post. In Sheet 1, I have unique employee records and the data which is split in 6 rows (rows count may differ from employee to employee) needs to be arranged in a single row. Volume of records (rows) ranges from 10K to 15k. So looking for a solution where after applying the formula sheet/file size doesn't become heavy because of formula (as is in case while applying arrays formulas).

Hopefully, you must have got what I looking for, please assist me accordingly.

 

[Fluff]

What version of Excel are you using? Also what Platform?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jasonb75]

Sorry, I forgot to filter out the blanks. It should have been:-

=FILTER(Sheet2!B$2:B$100,(Sheet2!$A$2:$A$200=A$2)*(Sheet2!B$2:B$200<>""),"")

 

[a1b2c3d4]

What version of Excel are you using? Also what Platform?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Dear Fluff, have updated account details

 

[Fluff]

Thanks for that, another option would be in B2 dragged down only

=TRANSPOSE(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,FILTER(Sheet2!B2:L100,Sheet2!A2:A100=A2))&"</m></k>","//m"))

 

[a1b2c3d4]

Sorry, I forgot to filter out the blanks. It should have been:-

=FILTER(Sheet2!B$2:B$100,(Sheet2!$A$2:$A$200=A$2)*(Sheet2!B$2:B$200<>""),"")

Thanks Jason for your extended support, its working fine only for the 1st record (row) as shown in the picture below.
For my better understanding, in your formula =A$2 is meant for Sheet 2 only if I'm not wrong as shown in formula below in italics & with underline.
=FILTER(Sheet2!B$2:B$100,(Sheet2!$A$2:$A$200=Sheet2!A$2)*(Sheet2!B$2:B$200<>""),"")

Challenge that I'm facing here is that for EMP_KEY i.e. 560862 if there are two records (rows) in sheet 2 (picture not shown here) wherein in certain columns, data is one below the other in same column then it picks up both or all the data falling in that column instead of first record and produces before EMP_KEY 560905 and so forth (depending upon the number of rows) which is not presenting the correct data (as shown in above table which is from Sheet1). Is there a way that data gets picked only from the first row if the cell has some data as it applies while executing Vlookup and where it is blank it picks from the 2nd row for same EMP_Key. By doing so, duplicity of records can be avoided. Please assist.

 

[jasonb75]

Sorry, it should have been $A2 not A$2 (no sheet name), when you post screen captures instead of usable mini sheets we are unable to test any suggestions for errors.
Note that this was based on your original screen captures with the EMP_KEY in column A and the dates starting in column B (on both sheets).

=FILTER(Sheet2!B$2:B$100,(Sheet2!$A$2:$A$200=$A2)*(Sheet2!B$2:B$200<>""),"")

 

[a1b2c3d4]

Thanks Fluff, don't know if I'm going wrong because the solution suggested by you is not working for me.
Below screen is for sheet1

and below is for sheet2 from where data is getting picked

Thanks for that, another option would be in B2 dragged down only

=TRANSPOSE(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,FILTER(Sheet2!B2:L100,Sheet2!A2:A100=A2))&"</m></k>","//m"))