Xlookup to return multiple lines

GoodyCC

New Member
Joined
Jun 11, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,989
Office Version
  1. 365
Platform
  1. Windows
You can use the filter function for that
Excel Formula:
=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.
 
Solution

GoodyCC

New Member
Joined
Jun 11, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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?
 

Attachments

  • review test.png
    review test.png
    17.6 KB · Views: 20

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,989
Office Version
  1. 365
Platform
  1. Windows
Don't drag-it down, just put it in one cell & it will automatically spill down.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,492
Messages
5,832,003
Members
430,103
Latest member
BIGGAZ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top