Found the solution :D TY for the information mateI don't know if there is any formula to do that, maybe any Formula Master will give you solution
btw. you've XL2016 so there is Power Query (Get&Transform) built-in
Book1 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Product | Product | Color 1 | Color 2 | Color 3 | Color 4 | ||||
2 | 123456 | black | ||||||||
3 | 123456 | white | ||||||||
4 | 123456 | orange | ||||||||
5 | 987654 | black | 123456 | black | white | orange | pal blue | |||
6 | 987654 | green | ||||||||
7 | 147258 | white | ||||||||
8 | 147258 | black | ||||||||
9 | 123456 | pal blue | ||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E5:H5 | E5 | =IFERROR(INDEX($B$2:$B$18,SMALL(IF($A$2:$A$18=$D5,ROW($A$2:$A$18)-ROW($A$2)+1),COLUMNS($D5:D5))),"") |
Press CTRL+SHIFT+ENTER to enter array formulas. |