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:

A	B	C	D	E	F
564	Yes	Teal	Yellow
999	No
874
635	Yes	Blue	Teal	Green	Purple

<tbody>
</tbody>

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...