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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
Solution
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: 40
Upvote 0
Don't drag-it down, just put it in one cell & it will automatically spill down.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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
Back
Top