IF Cell contains data Vlookup that cell THEN vlookup next cell if it contains data

[DTVFEdata]

Hello Excel Gurus,
I am stuck and hoping that what I am trying to do will make sense. If I have a chart that looks like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]564[/TD]
[TD]Yes[/TD]
[TD]Teal[/TD]
[TD]Yellow[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]999[/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]874[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]635[/TD]
[TD]Yes[/TD]
[TD]Blue[/TD]
[TD]Teal[/TD]
[TD]Green[/TD]
[TD]Purple[/TD]
[/TR]
</tbody>[/TABLE]

I want to show that if Column B is No or Blank to list "None" That part is a simple If statement.
Where it gets complicated is that if B shows Yes then I want to lookup C; if D contains data then I want to return C & D but I want it to stop if there is a blank Cell.

So for example
for user 564 I would want the end result to be Teal; Yellow
for user 635 I would want the end result to be Blue; Teal; Green; Purple
For user 999 I would want the end result to be None

In a perfect world I would like there to be an enter between each one (char(10) I think)
So 564 would show as:
Teal
Yellow

999:
None

635
Blue
Teal
Green
Purple


I know I could concatenate then all into another cell but I am trying to avoid having additional lines added to the sheet if it isn't needed. My actual data has many columns.


Is there a formula that would show if the look up for B returns yes then pull the answer from C, then if D is blank end the formula but if D has text show D, then if E is blank end the formula but if F has text show F and so on until it returns a blank cell...

Or the inverse of if B shows No or blank then show none and end the formula but if B shows Yes then pull the answer from C, then if D is blank end the formula but if D has text show D, then if E is blank end the formula but if F has text show F and so on until it returns a blank cell...


Any advice would be greatly appreciated. Thanks

 

[DTVFEdata]

My other thought was to add a column and do a count between the number of columns and build the If statement as If the count =4 then return C-F, where then the false portion would be another if the count is 3 then return c-E...

If that might be another option...