Look up a column with duplicates and return from multiple specified columns
Hi,
I have a worksheet where I have a product ID column with multiple duplicates followed by various columns with filled and missing data.
I would like to look up a particular product ID say 111 and find specific features from the various columns about this product eg Colour, #ID , Origin etc.
I have attached a sample workbook with colour coded with desired results.
Any help or direction will be greatly appreciated.
Thank you
Cell J3 formula , Drag down
Formula:
Please Login or Register to view this content.
Is there a way that we don't hard core the columns {1,3,5} ?Originally Posted by wk9128
How about this formula
Drag down and across
Formula:Please Login or Register to view this content.
Sorry to be a pain, if I wanted to add another feature say % Value and drag to the right, it doesn't pick it up.
Please try at
J2:L2 spill down
=LET(c,FILTER($B$2:$F$16,$B$1:$F$1=J2),XLOOKUP($I$3:$I$4,IF(c>0,$A$2:$A$16),c))
or with LAMBDA spill table
=LET(id,I3:I4,co,J2:L2,MAP(IF(co>0,id),IF(id,co),LAMBDA(id,co,LET(c,FILTER($B$2:$F$16,$B$1:$F$1=co),FILTER(c,(c<>"")*(A2:A16=id))))))
For Excel 2010-2016 without CSE:
=IFERROR(INDEX($A:$F;MOD(AGGREGATE(15;6;(ROW($B$2:$F$15)%%+COLUMN($B$2:$F$15))/(($B$2:$F$15>0)*($A$2:$A$15=$I3)*($B$1:$F$1=J$2));1);1)/1%%+0,1;INT(AGGREGATE(15;6;(ROW($B$2:$F$15)%%+COLUMN($B$2:$F$15))/(($B$2:$F$15>0)*($A$2:$A$15=$I3)*($B$1:$F$1=J$2));1)));"")
Last edited by T.I.; 03-08-2022 at 10:00 AM.
IN I3 (spill down)
=UNIQUE($A$2:$A$15)
In J3 and copy across and down
=TEXTJOIN("",TRUE,FILTER(FILTER($A$2:$F$15,$A$2:$A$15=$I3,""),$A$1:$F$1=J$2,""))
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks