Help with vlookup

With formulas only we may put in second row

for the identifier

=OFFSET('PRODUCT DIMS '!$B$3,INT((ROW()-1-0.1)/4),0)

for ID

=CHOOSE(MOD(ROW()-2,4)+1,"CartonLength","CartonWidth","CartonHeight","CartonWeight")

for Value

=OFFSET('PRODUCT DIMS '!$B$3,INT((ROW()-1-0.1)/4),CHOOSE(MOD(ROW()-1-1,4)+1,9,11,13,5)-1)

and drag all 3 cells down.

Formulas could be adjusted a bit with cosmetic and making them less dependent from starting row.

Next block in attached.

Hi Jon,

Okay, I'll try. However, the easiest way is to use Power Query (aka Get&Transform in 2016) to receive result in few clicks. The only point is to convert the range in Tab1 into the table.

Query done with User Interface only is

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product.............", type text}, {"Column1", type text}, {"Description...................", type text}, {"Sin/Inn", Int64.Type}, {"Sin/Outer", Int64.Type}, {"Weight", type number}, {"Sin/Pallet", Int64.Type}, {"Volume", type number}, {"OUTERS LENGTH", Int64.Type}, {"Column2", type number}, {"OUTERS WIDTH", Int64.Type}, {"Column3", type number}, {"OUTERS HEIGHT", Int64.Type}, {"height", type number}, {"INNERS LENGTH", Int64.Type}, {"INNERS WIDTH", Int64.Type}, {"INNERS HEIGHT", Int64.Type}, {"OUTER TI", Int64.Type}, {"OUTER HI", Int64.Type}, {"INNER TI", Int64.Type}, {"INNER HI", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([#"Product............."] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Product.............", "Weight", "Column2", "Column3", "height"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Product.............", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Product..............1", "Product..............2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Product..............1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Weight", "CartonWeight"}, {"Column2", "CartonLength"}, {"Column3", "CartonWidght"}, {"height", "CartonHeight"}, {"Product..............2", "Product"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Product", "CartonLength", "CartonWidght", "CartonHeight", "CartonWeight"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Product"}, "Attribute", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "CustomFieldID"}, {"Value", "CustomValue"}})
in
    #"Renamed Columns1"

and in attached.