Hi

I have two spreadsheets .

Spreadsheet 1 has sales rep name , client id , name of client

Spreadsheet 2 has client id ( not in same sequence as spreadsheet 1 ) , client name , client address , tel numbers

I would like to combine spreadsheet 1 , i.e. rep name , client id , name of client together with address and contact details on spreadsheet 2  . Spreadsheet one is based on client id , and only contains active client id numbers , whereas spreadsheet 2 contains all existing client id numbers .

How do I do it ?

Assuming Client ID is in column B of both SS1 and 2, and by spreadsheet you mean separate files, not separate tabs within one workbook.

Open both spreadsheets, then in SS1 type in a cell on row 2 of the first blank columns to the right of your data:

=VLOOKUP($B2,

then navigate to SS2, and select the entire columns of information with Client ID as the first column of the selection, and press F4 until you get all $ for both columns, (should look like this:

=vlookup(B2,'[File Name.xlsx]Sheet Name'!$B:$E

and then type 

, COLUMN(B2), False)

and press Enter. Then copy that cell down and to the right to match your data set and extract as many columns of data as you have.

In addition to the vlookup solution, you can consider using PowerPivot or Power Query. For large datasets vlookup will heavily consume resources, and will slow down your interactivity whenever your workbook is recalculated.

• Edited by Gil RavivMicrosoft employee 19 hours 48 minutes ago

In addition to the vlookup solution, you can consider using PowerPivot or Power Query. For large datasets vlookup will heavily consume resources, and will slow down your interactivity whenever your workbook is recalculated.

• Edited by Gil RavivMicrosoft employee Friday, March 20, 2015 11:37 AM

Excel 2010 with free Power Query Add-In.
Compatible with Office 2013 Pro Plus.
As Gil so cogently commented, Power Query offers many advantages.
PQ also dispenses with old-fashioned reference designators, like $AK$47.
http://www.mediafire.com/view/j8er32s7bo91395/03_20_15.xlsx