Consolidating columns plus lookup and return multiple values?

[retboell]

Hi all,

I'm looking to find a solution for joining (sorting and removing duplicates) multiple columns in one cell and then lookup all values in a separate table and return potential values in another cell. It would look something like this:

[TABLE="class: grid, width: 646"]
<colgroup><col span="4"><col><col></colgroup><tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[TD]Column4[/TD]
[TD]Consolidated[/TD]
[TD]Lookup values[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Apple[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Banana,Apple[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Apple,Banana[/TD]
[TD]A,C[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Orange[/TD]
[TD]Orange[/TD]
[TD]Orange[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD] [/TD]
[TD]Orange[/TD]
[TD] [/TD]
[TD]Banana,Orange[/TD]
[TD]B,C[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Banana[/TD]
[TD]Apple,Banana[/TD]
[TD]A,C[/TD]
[/TR]
</tbody>[/TABLE]

Note that Columns can contain multiple values.

The lookup table in this example would be:
[TABLE="class: grid, width: 206"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Fruit[/TD]
[TD]Letter[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]C[/TD]
[/TR]
</tbody>[/TABLE]

I have found partial solutions out there, but often combining these slows down my spreadsheet.

Thanks!