merge data
 

I have a worksheet with two columns: job titles and job codes.

I have 15 other worksheets that have several columns including job code, and
I need to add the job title in a column next to the job code.

Example of 1st workbook:

Job Title Job Code
President A4000
Secretary B1200
Technical Asst D3211
etc.

Other 15 workbooks are set up like:

Job Code Score Average Total
A4000 421 321 732
D3211 320 170 642

I just need to add a column that shows Job Title next to Job Code in the 15
workbooks, pulling that data from first workbook.

Help!

merge data
 

use index and match (needs a backwards vlookup).
assume your first sheet is called sheet1

in one of your 15 worksheets, add a new column after col A

Col A new Col B
row 1 A4000 =INDEX(sheet1!A:A,MATCH(A1, sheet1!B:B,0),1)
row 2 B1200 drag the formula above downwards

if this works, then you are all set.

You can do the rest of the 14 sheets in one go, providing they are all set
up quite similarly, you can select them all first before adding the column,
and you will find that it does all of them at once. Save your file before
you start just in case you make a mess.

--
Allllen


"Betsy" wrote:

I have a worksheet with two columns: job titles and job codes.

I have 15 other worksheets that have several columns including job code, and
I need to add the job title in a column next to the job code.

Example of 1st workbook:

Job Title Job Code
President A4000
Secretary B1200
Technical Asst D3211
etc.

Other 15 workbooks are set up like:

Job Code Score Average Total
A4000 421 321 732
D3211 320 170 642

I just need to add a column that shows Job Title next to Job Code in the 15
workbooks, pulling that data from first workbook.

Help!

merge data
 

Betsy,

You are using the terms worksheets and workbooks interchangably: worksheets are sheets within a
workbook (a single Excle file), while workbooks are separate, individually named Excel files.

If you have 15 WORKSHEETS, insert a new column B in each sheet, and use

=VLOOKUP(A2,'SheetName'!A:B,2,False)

in cell B2, copied down.

If you have 15 WORKBOOKS, open all the files, insert a column in each sheet of each workbook, and
use

=VLOOKUP(A2,'[WorksBookName.xls]SheetName'!$A:$B,2,FALSE)

in cell B2, copied down.

HTH,
Bernie
MS Excel MVP


"Betsy" wrote in message
...
I have a worksheet with two columns: job titles and job codes.

I have 15 other worksheets that have several columns including job code, and
I need to add the job title in a column next to the job code.

Example of 1st workbook:

Job Title Job Code
President A4000
Secretary B1200
Technical Asst D3211
etc.

Other 15 workbooks are set up like:

Job Code Score Average Total
A4000 421 321 732
D3211 320 170 642

I just need to add a column that shows Job Title next to Job Code in the 15
workbooks, pulling that data from first workbook.

Help!