Help with vlookup

[Jon Cooke]

Hi 

I'm not sure how to explain what i'm trying to achieve or the correct terminology so google has not been my friend . So I need to take date from Tab one that is in rows and pivot some information from row to column . so I need 4 identical part numbers and information for these parts . I can do the Vlookup but if i try to copy the data down the sheet it skips every other 3 part numbers , 

hopefully i have explained my issue and any help greatly appreciated 

[#2]

Could be like

=VLOOKUP(A2,'PRODUCT DIMS '!$B$3:$W$862,CHOOSE(MOD(ROW()-1-1,4)+1,9,11,13,5),FALSE)

[#3]

Thanks for that , although I may not have explained myself , on the second tab i want to be able to copy the Identifier and the 4 rows of data and then copy the same data for each row . 

so if i have an identifier in column B of Tab 1

A4DEA

AC1420

AC1449

AC1475

AC1510

Then Tab 2 should look like this as I copy the cells down 

A4DEA

A4DEA

A4DEA

A4DEA

AC1420

AC1420

AC1420

AC1420

AC1449

AC1449

AC1449

AC1449

But if I copy AC1449 down it will jump 4 rows and return AC1522 rather than AC1475 

[#4]

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.

[#5]

Wow , that is amazing , Now all i have to do is learn how you do it but . Thank you so much . I will now work backwards and learn from this  

[#6]

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.