Can VLOOKUP return multiple non-empty values from a table?

Please see your post at ExcelForum

try this,

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Components</td><td style=";">Product1</td><td style=";">Product2</td><td style=";">Product3</td><td style=";">Product4</td><td style=";">Product5</td><td style=";">Product6</td><td style=";">Product7</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Comp1</td><td style=";">x</td><td style=";">x</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">x</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Comp2</td><td style="text-align: right;;"></td><td style=";">x</td><td style="text-align: right;;"></td><td style=";">x</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Comp3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">x</td><td style="text-align: right;;"></td><td style=";">x</td><td style="text-align: right;;"></td><td style=";">x</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Comp4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Comp5</td><td style="text-align: right;;"></td><td style=";">x</td><td style="text-align: right;;"></td><td style=";">x</td><td style="text-align: right;;"></td><td style=";">x</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="background-color: #FFFF00;;">Components</td><td style="background-color: #9BBB59;;">Comp5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="background-color: #FFFF00;;">Product</td><td style="background-color: #92D050;;">Product2</td><td style="background-color: #92D050;;">Product4</td><td style="background-color: #92D050;;">Product6</td><td style="background-color: #92D050;;"></td><td style="background-color: #92D050;;"></td><td style="background-color: #92D050;;"></td><td style="background-color: #92D050;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B10</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">$B$1:$H$1,AGGREGATE(<font color="Green">15,6,(<font color="Purple">COLUMN(<font color="Teal">$B$1:$H$1</font>)-MIN(<font color="Teal">COLUMN(<font color="#FF00FF">$B$1:$H$1</font>)</font>)+1</font>)/(<font color="Purple">(<font color="Teal">$A$2:$A$6=$B$8</font>)*(<font color="Teal">$B$2:$H$6="x"</font>)</font>),COLUMNS(<font color="Purple">$B9:B9</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

Tremendous thanks to MrVillareal and NBVC! I'll study both of these solutions to learn from them. MrVillareal's solution is shown above. NBVC's solution appeared on excelforum thread:

=IFERROR(INDEX($B$1:$F$1,SMALL(IF(INDEX($B$2:$F$6,
MATCH($B$8,Component_List,0),0)="x",COLUMN($B$1:$F$1)-COLUMN($B$10)+1),
COLUMNS($B$1:B$1))),""
confirmed with CTRL+SHIFT+ENTER (not just ENTER), and copy across.

This thread is solved. Now, to learn how to MARK the thread solved. Thanks again for the education.