Struggling trying to bring in value using Lookup or Index Match

[desibouy]

Hi,

I am trying to automate some of my product upload CSV files, I have the below in Columns A to C.

[TABLE="width: 615"]
<tbody>[TR]
[TD]Item Name[/TD]
[TD][/TD]
[TD]Matrix Type[/TD]
[TD][/TD]
[TD]Sub-Item of[/TD]
[/TR]
[TR]
[TD]Test 1 - Main Item[/TD]
[TD][/TD]
[TD]Parent[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test a[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test b[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test c[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 2 - Main Item[/TD]
[TD][/TD]
[TD]Parent[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test I[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test II[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test 3 - Main Item[/TD]
[TD][/TD]
[TD]Parent[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test Z[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test Y[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test W[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would like to find all the parent item and bring in the value of the Main item next to all the childs but want the Parent itself blank, like so -

[TABLE="width: 652"]
<tbody>[TR]
[TD]Item Name[/TD]
[TD][/TD]
[TD]Matrix Type[/TD]
[TD][/TD]
[TD]Sub-Item of[/TD]
[/TR]
[TR]
[TD]Test 1 - Main Item[/TD]
[TD][/TD]
[TD]Parent[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test a[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD]Test 1 - Main Item[/TD]
[/TR]
[TR]
[TD]Test b[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD]Test 1 - Main Item[/TD]
[/TR]
[TR]
[TD]Test c[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD]Test 1 - Main Item[/TD]
[/TR]
[TR]
[TD]Test 2 - Main Item[/TD]
[TD][/TD]
[TD]Parent[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test I[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD]Test 2 - Main Item[/TD]
[/TR]
[TR]
[TD]Test II[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD]Test 2 - Main Item[/TD]
[/TR]
[TR]
[TD]Test 3 - Main Item[/TD]
[TD][/TD]
[TD]Parent[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Test Z[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD]Test 3 - Main Item[/TD]
[/TR]
[TR]
[TD]Test Y[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD]Test 3 - Main Item[/TD]
[/TR]
[TR]
[TD]Test W[/TD]
[TD][/TD]
[TD]Child[/TD]
[TD][/TD]
[TD]Test 3 - Main Item[/TD]
[/TR]
</tbody>[/TABLE]

This basically says - This child item is part of this Matrix Item - so when adding add all these Child items to the Parent.


I have tried using Index Match, Vlookup and combination of if lookups but I can't get it to work

please can some-one help me?

Thanks

 

[Comfy]

This should work:

Excel 2010
	A	B	C	D	E
1	Item Name		Matrix Type		Sub-Item of
2	Test 1 - Main Item		Parent
3	Test a		Child		Test 1 - Main Item
4	Test b		Child		Test 1 - Main Item
5	Test c		Child		Test 1 - Main Item
6	Test 2 - Main Item		Parent
7	Test I		Child		Test 2 - Main Item
8	Test II		Child		Test 2 - Main Item
9	Test 3 - Main Item		Parent
10	Test Z		Child		Test 3 - Main Item
11	Test Y		Child		Test 3 - Main Item
12	Test W		Child		Test 3 - Main Item
Sheet1

Cell Formulas
Range	Formula
E2	=IF(C2<>"Parent",INDEX($A$1:A1,SUMPRODUCT(MAX(($C$1:C1="Parent")*(ROW($C$1:C1))))),"")

 

[desibouy]

Hi,

FYI there is no empty columns in my sheet I had entered them so it's easy to read on here.

Thanks for the formula but that only gives me 0's?

========= Edit

Got it working, Thank you!!!! Thank you!!!! Thank you!!!! Thank you!!!! You're a lifesaver!! :D

 

[Comfy]

Hi,

FYI there is no empty columns in my sheet I had entered them so it's easy to read on here.

Thanks for the formula but that only gives me 0's?

========= Edit

Got it working, Thank you!!!! Thank you!!!! Thank you!!!! Thank you!!!! You're a lifesaver!! :D

You're Welcome.