Excel: Extract Data From Specific Column

I have been struggling with coming up with the right formula/ code to match multiple criteria and provide data from specific column from sheet 2 to sheet 1. I have two worksheets, one in terms of vendor and the other in customer PO. Sheet 1

Customer Order Qty Part Number Due Date
CH34567 14 H738490 Nov 4
I would like to spit out PO Line Item column (B) next to each line on sheet 1 from sheet 2. This is an issue because the data in sheet 2 is formatted differently as I have 4 part numbers columns corresponding to PO Line Item in each row, unlike flattened data in sheet 1. I have to match column A sheet 1 to column A sheet 2. If that is true, I need to match column C sheet 1 to column C, D, E and F on sheet 2. If both are true, I would like the formula to produce Column B sheet 2 in sheet 1 column E. Sheet 2
Customer Order PO Line Item PN Hubs PN Flanges PN Seal Rings PN Bolts
CH34567 131 H778400 A73973 SR38377 B38388
CH34895 225 H363839 A77489 SR47488 B39847
CH07478 145 H476567 A36378 SR73738 B74466
CH34567 155 H778400 A87664 SR64747 B56378
The issue is that on sheet 2, a same part number can correspond to multiple PO line item (note above Hub PN H778400 corresponds to PO Line 131 and 155). I need to be able to spit out all PO Line Items next to Ship Date column on sheet 1. I have been playing around with Index and Match formulas, but I am just not getting it right. Any help would be greatly appreciated.

This question generated 27 answers. To proceed to the answers, click here.

This thread is current as of October 18, 2013.

For more resources for Microsoft Excel