It is Possible? Index Match - returning column header

[fidelito101]

Hello Team!

I have a table that contains Products and parts for manufacturing(sheet1)

[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]Part Number[/TD]
[TD]Part Name[/TD]
[TD]TOYOTA 374F[/TD]
[TD]TOYOTA 637F[/TD]
[TD]NISSAN 1224[/TD]
[TD]VOLVO 2650[/TD]
[/TR]
[TR]
[TD]HU294150-0400[/TD]
[TD]CYLINDER[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HU294150-0410[/TD]
[TD]CYLINDER[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]HU294191-0100[/TD]
[TD]CAMSHFT[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HU294191-0070[/TD]
[TD]CAMSHAT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]HU294110-0380[/TD]
[TD]BEARING COVER[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

And in Sheet2 I need a table like this below:

[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]HU294150-0400[/TD]
[TD]CYLINDER[/TD]
[TD]TOYOTA 374F[/TD]
[TD]NISSAN 1224[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HU294150-0410[/TD]
[TD]CYLINDER[/TD]
[TD]TOYOTA 637F[/TD]
[TD]VOLVO 2650[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HU294191-0100[/TD]
[TD]CAMSHFT[/TD]
[TD]TOYOTA 374F[/TD]
[TD]TOYOTA 637F[/TD]
[TD]NISSAN 1224[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HU294110-0380[/TD]
[TD]BEARING COVER[/TD]
[TD]TOYOTA 374F[/TD]
[TD]TOYOTA637F[/TD]
[TD]NISSAN[/TD]
[TD]VOLVO2650
[/TD]
[/TR]
[TR]
[TD]HU294191-0070[/TD]
[TD]CAMSHAT[/TD]
[TD]VOLVO 2650[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


And if is possible to avoid blank cells.
****** id="cke_pastebin" style="position: absolute; top: 274.4px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="class: grid, width: 800, align: center"]
<tbody>[TR]
[TD]CYLINDER[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[AhoyNC]

Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER.
Drag formula down and across as needed.

Excel Workbook
	A	B	C	D	E
1	Part Number	Part Name
2	HU294191-0100	CAMSHAFT	Toyota 374	VOLVO 920
3	294096-0040	WASHER	Toyota 374	Lombardini 1800	VOLVO 920
4	HU294198-0010	O-RING	Toyota 374	VOLVO 920
Sheet2

Excel Workbook
	A	B	C	D	E
1	Part Number	Part Name	Toyota 374	Lombardini 1800	VOLVO 920
2	HU294191-0100	CAMSHAFT	1		1
3	294096-0040	WASHER	2	2	1
4	HU294198-0010	O-RING	3		2
Sheet1

 

[fidelito101]

Hi AhoyNC

This works like a charm!

Thanks, Thanks a lot for your help

 

[AhoyNC]

You're welcome. Thanks for the feedback.