Formula to take data from horizontal row

D2=INDEX(SHIP!$C$2:$I$1000,MATCH($A2&LEFT($B2,LEN($B2)-3),INDEX(SHIP!$A$2:$A$1000&SHIP!$B$2:$B$1000,0),0),MATCH(RIGHT($B2,2),INDEX(TEXT(SHIP!$C$1:$I$1,"00"),0),0))
OR
D2=AGGREGATE(14,6,SHIP!$C$2:$I$1000/((SHIP!$B$2:$B$1000&TEXT(SHIP!$C$1:$I$1,"-00")=$B2)*(SHIP!$A$2:$A$1000=$A2)),1)
Try the above, copy and paste towards down

Another way. In D2 and filled down.

Formula:

Please Login or Register  to view this content.

Here is an alternate solution. With Power Query (which is an addin for Excel 2010 at no charge from MS), bring your table into the PQ Editor. Highlight the first two columns and Unpivot the remaining columns. Merge the second and third column with a dash (-) separator. Here is the Mcode that results from those steps.

Please Login or Register  to view this content.

Excel 2016 (Windows) 32 bit

	A	B	C
1	P. O. NO	Merged	Value
2	1234	AAAA-3	3
3	1234	AAAA-4	3
4	1234	AAAA-5	3
5	2345	BBB-2	1
6	2345	BBB-4	2
7	2345	BBB-5	2
8	2345	BBB-7	2
9	3456	CCC-2	2
10	3456	CCC-3	2
11	3456	CCC-4	2
12	3456	CCC-5	2
13	3456	CCC-6	2
14	3456	CCC-7	2

Sheet: Sheet1

You are welcome. Thank you for the feedback and marking your thread Solved.