Formula Query Multiple Columns with Filter in google sheet

Question

I have two sheets in data google sheet, let's both named is sheet 1 and sheet 2. In sheet 1, I have four columns as below:

And in sheet 2, I have multiple columns and row in below:

If I want to filter sheet 1 based column Lot No, example the Lot No is 01/09/2023, 02/09/2023, and 04/09/2023. If i paste the Lot No to column Lot 1, Lot 2, and Lot 3 (sheet 2), then on sheet 2 it will output the column as follows:

Does anyone know the formula to solve this problem on row Sample Number (S1)?

Answer 1

You could probably do it with something like this, in cell A4 (ensuring there is nothing else in row 4):

=index({"Sample Number (S1)",
iferror(if(B2:2<>"",
vlookup(B2:2,sort({Sheet1!$D:$D,Row(Sheet1!$D:$D)-1},2,1),2,0)
&"-"&
vlookup(B2:2,sort({Sheet1!$D:$D,Row(Sheet1!$D:$D)-1},2,0),2,0)
,),)})

If it helps, I'll add an explanation.