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

[JMatBoS]

I have a lookup table of this format:

	A	B	C
1	Apples, Pears, Watermelons	Green	123
2	Apples	Red	456

<tbody>
</tbody>

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.

	A	B	C
1		Green	Red
2	Apples	123	456
3	Watermelons	123

<tbody>
</tbody>

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

456

<tbody>
</tbody>

 

[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)),"")