Multiple answers with lookup

[FROGGER24]

Worksheet "Transport Summary" cell B1 will be the lookup value. My lookup data resides on a worksheet called "Raw Data", columns A-C
I need to look up the value in cell B1, then look on the worksheet "Raw Data" to find all the matching values as in B1. Once found copy data from columns B and C and place on worksheet "Transport Summary" starting in cell A3.

Once that is done will need to put same info starting in row 18.

Thanks in advance for your time and effort

 

[sandy666]

is this similar to what you want?
https://www.mrexcel.com/forum/excel...uplicated-values-post5225154.html#post5225154

 

[FROGGER24]

Sandy, thank you for link. I consolidated my worksheets to make the formula easier to understand. this is my modified formula to suit my needs, it is only pulling in 2 of the 3 values. I would also like to make the range variable length, since we will be updating daily. Row 1 contains headers
{=IFERROR(INDEX($S$1:$S$32,SMALL(IF($R$1:$R$32=$B$1,ROW()),ROW()-1)),"")}

 

[sandy666]

if you prefer formula that's ok

have a nice day

 

[FROGGER24]

I had to make some corrections to the suggested formula
In my data set in cell A3 my formula is this
{=IFERROR(INDEX('Raw Data'!$B$2:$B$1048576,SMALL(IF('Raw Data'!$A$2:$A$1048576='Transport Summary'!$B$1:$C$1,ROW('Raw Data'!$B$2:$B$1048576)-MIN(ROW('Raw Data'!$B$2:$B$1048576))+1),ROWS('Transport Summary'!$A$3:A3))),"")}

cell B3 has the following formula:
=IFERROR(INDEX('Raw Data'!$C$2:$C$1048576,SMALL(IF('Raw Data'!$A$2:$A$1048576='Transport Summary'!$B$1:$C$1,ROW('Raw Data'!$C$2:$C$1048576)-MIN(ROW('Raw Data'!$C$2:$C$1048576))+1),ROWS('Transport Summary'!$B$3:B3))),"").

How could this be put into code, can code/formula be modified to last used row versus 1048546 rows?

Thanks again for any help