Horizontal lookup return data vertically (Index Match ?)

[Prospidnick]

I am trying to return a vertical collation of order data from a horizontal order table supplied by a client that cannot be changed. I have attempted index match, an array all to no avail.

The order table ‘ORDER FORM’ displays the order numbers in column A and starts in row 5.

The individual lines that I need to be returned to the ‘OrderCollate’ tab start from column R through to column BR (Although this could be extended)

What I need is to find the related order number in column A and return the values of that row between Columns R through to BR that have values in them ignoring all blanks. I then need to return the SKU header that is in row 1 which relates to the cells that have a value;

Eg;

Order number 10013
SKU Qty
CE041301A 1
CE041302A 1
CEI080 2
CE041345A 1
CE041313A 1

Order number 10019
CE041332A 1

Order number 10025
CE041340A 1

I have attached the spreadsheet and OrderCollate shows the results I require, but not the formula to do what I require. I will only be looking for one order number each time, we will be using this information to produce a pick/check sheet.

Any advice would be greatly appreciated.

Many thanks

Richard

[JohnTopley]

in C20

=IFERROR(INDEX('ORDER FORM'!$R$1:$BN$1,SMALL(IF(INDEX('ORDER FORM'!$R$5:$BN$66,MATCH($C$14,'ORDER FORM'!$A$5:$A$66,0),0)<>0,COLUMN('ORDER FORM'!$R$1:$BN$1)-COLUMN($R$1)+1,""),ROWS($A$5:A5))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

in D20

=IFERROR(INDEX('ORDER FORM'!$R$5:$BN$66,MATCH(OrderCollate!$C$14,'ORDER FORM'!$A$5:$A$66,),MATCH(OrderCollate!$C20,'ORDER FORM'!$R$1:$BN$1,0)),"")

[FDibbins]

in C20

=IFERROR(INDEX('ORDER FORM'!$R$1:$BN$1,SMALL(IF(INDEX('ORDER FORM'!$R$5:$BN$66,MATCH($C$14,'ORDER FORM'!$A$5:$A$66,0),0)<>0,COLUMN('ORDER FORM'!$R$1:$BN$1)-COLUMN($R$1)+1,""),ROWS($A$5:A5))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

in D20

=IFERROR(INDEX('ORDER FORM'!$R$5:$BN$66,MATCH(OrderCollate!$C$14,'ORDER FORM'!$A$5:$A$66,),MATCH(OrderCollate!$C20,'ORDER FORM'!$R$1:$BN$1,0)),"")

John, looks like that only works for that specific code?

[Prospidnick]

Thanks guys, works perfectly, i have adapted for my needs, just need to work out how to retrieve data from ROWS 2 & 3 which were blank in the example i gave you.

Thanks again.

[FDibbins]

To pull out the code...
=IFERROR(INDEX('ORDER FORM'!$1:$1,SMALL(IF(OFFSET('ORDER FORM'!$R$1,MATCH(OrderCollate!$C$14,'ORDER FORM'!A:A,0)-1,,1,COUNTA('ORDER FORM'!$R$1:$WWW$1))>=1,COLUMN('ORDER FORM'!$R$1:$BR$1)),ROWS($A$1:A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[JohnTopley]

I think it works for all Order numbers pulling all codes. Try order 10024.