=filter function

%3CLINGO-SUB%20id%3D%22lingo-sub-2760403%22%20slang%3D%22en-US%22%3E%3Dfilter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2760403%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHey%20I%20need%20to%20create%20a%20spreadsheet%20of%20reviews%20for%20a%20project%20I'm%20working%20on.%20I%20have%20a%20library%20of%20reviews%20sorted%20into%204%20columns%20of%20product%20code%2C%20star%20rating%2C%20name%20and%20review.%20I%20have%20a%20separate%20workbook%20where%20on%20sheet%202%20I%20paste%20the%20product%20codes%20that%20I%20need%20review%20codes%20for%20then%20it%20searches%20the%20library%20and%20returns%20the%20information%20on%20sheet%201%2C%20(this%20is%20more%20of%20an%20aesthetic%20look%20that%20for%20function)%20and%20it%20all%20works%20fine.%20My%20issue%20comes%20when%20I%20need%20multiple%20reviews%20for%20the%20same%20product%2C%20so%20is%20there%20a%20way%20of%20rather%20than%20the%20lookup%20only%20pulling%20the%20first%20product%20code%20it%20see's%20can%20it%20pull%20all%20the%20lines%20with%20that%20product%20code.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EFormula%20I%20first%20used%20was%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3E%3DXLOOKUP(Sheet2!B2%2C'%5BReview%20LIbrary.xlsx%5DSheet1'!%24A%3A%24A%2C'%5BReview%20LIbrary.xlsx%5DSheet1'!%24A%3A%24D%2C%2C0%2C1)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20then%20changed%20to%20a%20filter%20function%20so%20that%20if%20i%20had%20more%20than%20one%20result%20for%20the%20product%20it%20would%20load%20them%20first%20then%20the%20next%20product%20however%20it%20either%20does%20not%20auto%20fill%20down%20over%20or%20if%20i%20pull%20it%20down%20then%20it%20returns%20the%20%23spill%20error%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DFILTER('%5BReview%20LIbrary.xlsx%5DSheet1'!%24A%3A%24D%2C'%5BReview%20LIbrary.xlsx%5DSheet1'!%24A%3A%24A%3DSheet2!B2)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ehow%20can%20i%20get%20it%20so%20its%20look%20up%20a%20range%20of%20products%20from%20sheet%202%2C%20finds%20the%20reviews%20in%20the%20library%20workbook%2C%20and%20then%20lists%20the%20all%20the%20reviews%20for%20that%20product%20and%20then%20the%20next%20ect%20rather%20than%20just%20the%20first%20product%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2760403%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2760510%22%20slang%3D%22en-US%22%3ERe%3A%20%3Dfilter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2760510%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1159016%22%20target%3D%22_blank%22%3E%40liamgoodmancc%3C%2FA%3E%26nbsp%3B%20Well%2C%20the%20FILTER%20function%20you%20wrote%20could%20work.%20Though%2C%20it's%20difficult%20to%20say%20why%20it%20doesn't%20give%20the%20desired%20result%20if%20you%20don't%20upload%20an%20example%20of%20your%20file%20with%20the%20incorrect%20result%20and%20explaining%20why%20and%20what's%20expected.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBe%20aware%20that%20the%20FILTER%20function%20tries%20to%20output%20an%20entire%20array%20in%20one%20go.%20If%20anything%20gets%20in%20the%20way%2C%20you'll%20get%20%23SPILL!.%20So%20NEVER%20copy%20down%20or%20across%20a%20formula%20with%20dynamic%20array%20functions%20as%20it%20will%20obstruct%20itself%20and%20produce%20the%20%23SPILL!%20error.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hey 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 I first used was
=XLOOKUP(Sheet2!B2,'[Review LIbrary.xlsx]Sheet1'!$A:$A,'[Review LIbrary.xlsx]Sheet1'!$A:$D,,0,1)

 

I then changed to a filter function so that if i had more than one result for the product it would load them first then the next product however it either does not auto fill down over or if i pull it down then it returns the #spill error

 

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

 

how can i get it so its look up a range of products from sheet 2, finds the reviews in the library workbook, and then lists the all the reviews for that product and then the next ect rather than just the first product 

1 Reply

@liamgoodmancc  Well, the FILTER function you wrote could work. Though, it's difficult to say why it doesn't give the desired result if you don't upload an example of your file with the incorrect result and explaining why and what's expected.

 

Be aware that the FILTER function tries to output an entire array in one go. If anything gets in the way, you'll get #SPILL!. So NEVER copy down or across a formula with dynamic array functions as it will obstruct itself and produce the #SPILL! error.