I've got 2 worksheets with lists of products, the 1st colum on each sheet
contains the productcodes. These lists are not exactly the same, list one is
much longer than the other, but all codes in list 2 are in list 1. I now want
to have 1 list with the data from both worksheets. I want the codes from list
2 to line up on the same row with the matching code from list 1. Both lists
have several colums with additional info about the product and this info has
to move aswell.

Index n Match is one way to get it done

Assume you want to update Sheet1 (your master)
with data from Sheet2's cols B to D
where the key match col = col A (product codes) in both Sheet1/2

In Sheet1,
You've got existing data in cols A to D,
with key col = col A (product codes)

Place in say, E2:
=IF(ISNA(MATCH($A2,Sheet2!$A:$A,0)),"",INDEX(Sheet 2!B:B,MATCH($A2,Sheet2!$A:$A,0)))
Copy E2 across to G2, fill down to extract data from Sheet2's cols B to D
based on matching with the product codes in col A. Unmatched codes, if any,
will return blanks: "".
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"odi et amo" wrote:
I've got 2 worksheets with lists of products, the 1st colum on each sheet
contains the productcodes. These lists are not exactly the same, list one is
much longer than the other, but all codes in list 2 are in list 1. I now want
to have 1 list with the data from both worksheets. I want the codes from list
2 to line up on the same row with the matching code from list 1. Both lists
have several colums with additional info about the product and this info has
to move as well.

Please follow following steps
1.Select the cell B1 in your sheet1 and enter following formula
=VLOOKUP(A1,Sheet2!$A$1:$C$4,2,FALSE)
2.Similarly select C1 in sheet1 and enter following formula
=VLOOKUP(A1,Sheet2!$A$1:$C$4,3,FALSE)

Here, Note that the more additional information column are in sheet2, the
more columns in sheet1 should be held with the vlookup formula

Have a nice time€¦.

Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com

--
Message posted via http://www.officekb.com