Match text from two different workbooks, return value from different column

Question

I have never been so frustrated with Excel in my life. I'm coming up against two major deadlines at work, and the only way to get the right info to the right people involves solving the problem below.

I have two workbooks with different metrics for different "clients" in each, and I need to get all of the info into one sheet - can't simply copy/paste the data over because I could have Client A in the first workbook, but not in the second. Also, can't use "Client Name" as the unique identifier between workbooks because one could be "The John Smith Company" and the other could say, "The John Smith Co."

Here is a very simplified example:

Workbook A

• Client ID
• Number of Employees

Workbook B

• Client ID
• Years as Client
• Location
• Client Name

Before you mark me as a duplicate question, know that I have tried the following formulas to no avail:

=VLOOKUP(D2,[othersheet.xlsx]sheet1!$A:$D,2,FALSE)

=IF(ISERROR(EXACT(D2,[othersheet.xlsx]sheet1!$1:$1048576)), "True", VLOOKUP(D2,[othersheet.xlsx]sheet1!$1:$1048576,2,FALSE))

Losing my mind here - Any ideas would be marvelous. Thank you in advance!

ETA:

I'm using Excel 2011 for Mac, v. 14.4.1.

Answer 1

have you tried :
=VLOOKUP(D2,[othersheet.xlsx]sheet1!$A$1:$D$1000,2,FALSE)

Answer 2

I think the easiest way for you to do this would be to use

=match(clientID_WorkbookA_CellA2,ClientID_WorkBookB,0) in workbook A to see which ID's are in both lists. Then manually adjust the Client ID's that are the same but typed incorrectly.

Then any that do not match are not in workbook B. Filter for these and append them to the end of your table in workbook B.

Then create a column labelled number of employees workbook B and use a sumif. something like:

=sumif(ClientID_WorkBookA,ClientID_WorkBookB_CellA2,Employees_WorkBookA).

Obviously without seeing the actual spreadsheets it's hard to provide better code but I'm sure you understand the jist.

UPDATE

Worksheet A

In cell E2 put =trim(D2) and drag down

Worksheet B

In cell O2 put =trim(D2) and drag down

In cell C2 put =sumif(O2,sheetA!E:E,sheetA!B:B) and drag down

If there are any errors it means that your client ID in column O doesn't exist in sheet A.

Answer 3

When using a Vlookup, the value you are checking must be in the leftmost column of the range you are looking in. Your current formula is attempting to look up a Client ID against the Client Name.

Have you tried doing a Match() against Column D? You can combine this with the Index() function to get what you want:

=INDEX([othersheet.xlsx]sheet1!$B:$B,MATCH(D2,[othersheet.xlsx]sheet1!$D:$D,FALSE))

Answer 4

Unless finding a solution in Excel is a compulsion, a solution may be found through MS Access.

Import both the excel files in to a database in Access, open design view of the QUERY, open both files, link (innerjoin) on customer id, drag the required fields from both the files in to the table below and view the result in database view of the QUERY.

Save with a proper name and close QUERY. Right click the saved QUERY in left side pane; export the file to excel. Excel file is ready.

It is stated that that data is arranged in multiple matrices in the original file. If multiple tables are created in the same worksheet, naming the ranges is a good idea for easy import of data into database. Header and order of data in columns are to be similar for all tables. Multiple worksheets pose no problem, because Access will ask if worksheet is to be imported or a Range is to be imported.