VLOOKUP Skip Blank Cells

[CAhawkeye10]

I have a set of data similar to the below table.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Subject[/TD]
[TD]Length[/TD]
[TD]Width[/TD]
[TD]Height[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Type 1[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Type 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Type 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Type 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Type 1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Type 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Type 1[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Type 1[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

There are multiple types of subjects in question but only some have a length, width and height. I would like to combine all of those which do, Type 1 in this case, into a separate worksheet so that there are no blanks inbetween. The new table would look similar to the one below.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Subject[/TD]
[TD]Length[/TD]
[TD]Width[/TD]
[TD]Height[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Type 1[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Type 1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Type 1[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Type 1[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

I'm assuming that I would use VLOOKUP somehow but I'm not sure what exactly the formula should contain. I'm also aware that this could be easily done using filters on the first worksheet but multiple people will be using this and I want there to be as little room for error in the future. Any help will be greatly appreciated.

Thanks!

 

[FDibbins]

Try this approach using a helper column (I used E), copy this into sheet1 E2, and copy down...
=IF(COUNTA(B2:D2)=0,E1,E1+1)

Then in sheet2 (where you want the answer to be, use this copied dowqn and across...
=IFERROR(INDEX(Sheet1!$A$1:$D$9,MATCH(ROW(A1),Sheet1!$E$1:$E$9,0),MATCH(A$1,Sheet1!$A$1:$D$1,0)),"")

 

[CAhawkeye10]

That worked perfectly! Thanks!