Returning a value for two lookup columns (without a concatenate)

[JMatBoS]

I have a lookup table of this format:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apples, Pears, Watermelons[/TD]
[TD]Green[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apples[/TD]
[TD]Red[/TD]
[TD]456[/TD]
[/TR]
</tbody>[/TABLE]

I need the data represented in the target table as below. Column A can contain multiple values that will appear as separate row names in the target table. Column B will have single values which will match the column names in the target table.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Green[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apples[/TD]
[TD]123[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Watermelons[/TD]
[TD]123[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I can use a vlookup with wildcard to get the value for column A but I'm struggling to add the match to column B.

Thanks in advance[TABLE="width: 500"]
<tbody>[TR]
[TD]456[/TD]
[/TR]
</tbody>[/TABLE]

 

[mart37]

Maybe something likes this: =SUMPRODUCT((ISNUMBER(SEARCH($A2,$A$1:$A$2)))*(B$1=$B$1:$B$2)*$C$1:$C$2)

 

[Aladin Akyurek]

Let's A:C of Sheet1 house the data.

In B2 of Sheet2 enter, copy across, and down:

=SUMIFS(Sheet1!$C$1:$C$2,Sheet1!$A$1:$A$2,"*"&$A2&"*",Sheet1!$B$1:$B$2,B$1)

 

[JMatBoS]

Thanks. Unfortunately the facts aren't actually numeric. They are actually names. Replace 123 with Mr X and 456 with Mrs Y

 

[Aladin Akyurek]

Thanks. Unfortunately the facts aren't actually numeric. They are actually names. Replace 123 with Mr X and 456 with Mrs Y

So you learned something valuable...

In B2 of Sheet2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX(Sheet1!$C$1:$C$2,MATCH("*"&$A2&"*",IF(Sheet1!$B$1:$B$2=B$1,Sheet1!$A$1:$A$2),0)),"")