Excel 2007 - vlookup/index/match with two datasets on two sheets

I have two data sets across two worksheets. The first worksheet contains sales order numbers (Sheet1:column A) and other data . The second worksheet contains sales order numbers (Sheet2:column A) and the product details.

On sheet1, I had to manually duplicate a sales order number (inserting another row) if the number of units of the order is greater than 1. I then need to fetch the product details from sheet2 for each unit for that specific order number. However in sheet2, there are multiple products for one sales order number.

How do i create a lookup/match to fetch the product details for each sales order number without duplicating the product details if there are multiple products for one sales order number?

I know a simple vlookup function will return the values that it matches first and that is not what i want.

Example: Sheet1

Column A (Sales Order#) | Column B (Quantity)

0417436GPCP | 1

0417436GPCP | 1

0413412FACY | 1

0413412FACY | 1

Sheet2

Column A (Sales Order#) | Column B (Product) | Column C (Serial Number).....

0417436GPCP | Door | A13251

0417436GPCP | Window | A41315

0413412FACY | Window | B41141

0413412FACY | Lock | A4114151

Ideal Output

Column A (Sales Order#) | Column B (Quantity) | Column C (Product) | Column D (Serial Number)

0417436GPCP | 1 | Door | A13251

0417436GPCP | 1 | Window | A41315

0413412FACY | 1 | Window | B41141

0413412FACY | 1 | Lock | A4114151

Sample_Dataset_Formulas.xlsx

I've attached a sample dataset. The fields highlighted in yellow are what i need to populate from Sheet2. The only match between the two sheets are the values from the "DEL_DOC_NUM" columns.

Thank you.

Originally Posted by popipipo

Please post an excel worksheet for better help.

Posted! Thanks in advance!

Basically, i need the formula to return MULTIPLE corresponding values for ONE Lookup Value

Originally Posted by micope21

Hi

Best way to use helper both sheet column A is use COUNTIF Sheet2 column A point to Sheet2 column B DEL_DOC_NUM, Cell A2

Formula:

Please Login or Register  to view this content.

copy down.

COUNTIF Sheet 1 column A cell A2

Formula:

Please Login or Register  to view this content.

copy down

Sheet 1 Cell F2

Formula:

Please Login or Register  to view this content.

copy down.

Index change letter to point next column so on.

See the file!

Regard
micope21

Thank you so much!