IF + INDEX-MATCH and return value from different cell

[davidov]

Last time I asked here something this forum was very helpful, so I hope one of you can help me with my next problem.

I have data in one sheet, with an ID number, Attributes that go with it and corresponding values. But one ID can have multiple attributes and values. And this data has to be transferred to another sheet. To try I made a simple sheet set up, which I can translate easily in a final version, but I am already failing in the trial file. I have 2 sheets in total:
Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Values Attributes AAAA, BBBB or CCCC
[/TD]
[TD]Values Attributes DDDD or EEEE
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Attribute
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]AAAA
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]DDDD
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]BBBB
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]CCCC
[/TD]
[TD]25
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]BBBB
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]EEEE
[/TD]
[TD]35
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]EEEE
[/TD]
[TD]40
[/TD]
[/TR]
</tbody>[/TABLE]

I want the result to be this for Sheet 1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Value AAAA BBBB or CCCC (V1)
[/TD]
[TD]Value DDDD or EEEE (V2)
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]10
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]20
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]25
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]30
[/TD]
[TD]35
[/TD]
[/TR]
</tbody>[/TABLE]

What formula to use? So far I used IF statements to check if there is an AAAA, if not go to BBBB, if not go to CCCC. There will never be a value for both AAAA and BBBB or CCCC at the same time.
My formula for V1:
=IF(INDEX(Sheet2!$B:$B,MATCH($A2,Sheet2!$A:$A,0))="AAAA",
INDEX(Sheet2!$C:$C,MATCH($A2,Sheet2!$A:$A,0)),
IF(INDEX(Sheet2!$B:$B,MATCH($A2,Sheet2!$A:$A,0))="BBBB",
INDEX(Sheet2!$C:$C,MATCH($A2,Sheet2!$A:$A,0)),
IF(INDEX(Sheet2!$B:$B,MATCH($A2,Sheet2!$A:$A,0))="CCCC",
INDEX(Sheet2!$C:$C,MATCH($A2,Sheet2!$A:$A,0)),"")))

This kind of works, but only if every ID only has 1 Attribute. If I adjust this formula for V2 it will work fine as well as long there is no value for V1. At the moment there is a value for both V1 as for V2 it fills in only the column with the first Attribute in the list of sheet 2.

I tried index-match with criterion but could not make it work, and I tried index-match-match but I am not that good with it. Does anyone know a formula I can use that will give the desired result, without using macros. I try to make it so that other people who will use this file don't have to worry about macros as they are likely to be even less knowledgeable about it than me.

Thanks in regard!

 

[wideboydixon]

There's probably a more elegant solution but here's my stab:

Book1
	A	B	C
1	ID	Attribute	Value
2	1	AAAA	10
3	1	DDDD	15
4	2	BBBB	20
5	3	CCCC	25
6	4	BBBB	30
7	4	EEEE	35
8	5	EEEE	40
Sheet2

Book1
	A	B	C
1	ID	Value AAAA BBBB or CCCC	Value DDDD or EEEE
2	1	10	15
3	2	20
4	3	25
5	4	30	35
Sheet1

Cell Formulas
Range	Formula
B2	{=IFERROR(1/(1/MAX(IF(Sheet2!$A$2:$A$8=$A2,IF(IFERROR(FIND(Sheet2!$B$2:$B$8&"~","AAAA~BBBB~CCCC~"),0)>0,Sheet2!$C$2:$C$8)))),"")}
C2	{=IFERROR(1/(1/MAX(IF(Sheet2!$A$2:$A$8=$A2,IF(IFERROR(FIND(Sheet2!$B$2:$B$8&"~","DDDD~EEEEE~"),0)>0,Sheet2!$C$2:$C$8)))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD