Xlookup to return multiple lines

[GoodyCC]

I need to create a spreadsheet of reviews for a project I'm working on. I have a library of reviews sorted into 4 columns of product code, star rating, name and review. I have a separate workbook where on sheet 2 I paste the product codes that I need review codes for then it searches the library and returns the information on sheet 1, (this is more of an aesthetic look that for function) and it all works fine. My issue comes when I need multiple reviews for the same product, so is there a way of rather than the lookup only pulling the first product code it see's can it pull all the lines with that product code.

Formula being used is
=XLOOKUP(Sheet2!B2,'[Review LIbrary.xlsx]Sheet1'!$A:$A,'[Review LIbrary.xlsx]Sheet1'!$A:$D,,0,1)

Thanks

 

[Fluff]

You can use the filter function for that

=FILTER('[Review LIbrary.xlsx]Sheet1'!$A:$D,'[Review LIbrary.xlsx]Sheet1'!$A:$A=Sheet2!B2)

although it's best to avoid using whole column references.

 

[GoodyCC]

This is why I love this site! only problem with this is it returns the first line no problem but if i try to drag it down so that all product codes on sheet 2 are on sheet one it leaves me with a lot of #Spill errors. is there any way i can paste the formula and drag it down?

 

[Fluff]

Don't drag-it down, just put it in one cell & it will automatically spill down.