Three way lookup - two different MS Excel workbooks 1

[BxWill]

Have two workbooks and having a challenge creating a lookup - from Workbook1 to Workbook2 based on
account number, month, and category.

The overall objective is to import the data from workbook 1 into workbook and determine if there are any differences
in the amounts for a specific combination of account number, month,and category.

Workbook1 has over 4000 records with 10 columns and Workbook2 has approximately 3000 records with 15 columns.

Initially tried to normalize the data in Workbook1 but to no avail.
Unsuccessful with Index Match, Using named ranges, sumproduct and vlookup with match.

Formulae tried include;
=VLOOKUP(AcctNbr_Workbook2,'Workbook1]Worksheet1'!Data,MATCH(Month_Workbook2,Workbook1'!Month_Workbook1,0)+2,FALSE)

Any insight as to a expedient method to accomplish the import of data from Workbook1 into Workbook is appreciated.


Workbook1-Worksheet1

Name----- Category- AcctNbr-- 1/1/2014- 2/1/2014- 3/1/2014
Jane----- Gross---- 1234----- 7500----- 8500----- 7600
John----- Gross----1456----- 9500----- 10000---- 12000
Shirley-- Expense-- 1423----- 1050----- 4600----- 5500

Workbook2-Worksheet1

Month------------- Name----- Account-- Gross---- Data_FromWksht1---Variance----Expense---Data_FromWksht1--Variance
1/1/2014---------- Jane----- 1234----- 8000----- 7500-----------------500---------
1/1/2014---------- Jane----- 1235----- 2500-----
1/1/2014---------- Shirley-- 1423----- 3500---------------------------------------------1250------1050-----200
2/1/2014---------- Jill----- 2246----- 4400


Thanks in advance for your time.

 

[kjv1611]

Have you looked into using MS Query to get your needs met? You can query against sheets in the same workbook or in different workbooks. That way you can work with the query similar to how you would in a database. There are limitations, but sometimes it can do amazing things.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[BxWill]

Thanks for the insight.

Yes, I am familiar with MS Query and will attempt to find a solution using MS Query.

However, I believe that MS Query is better suited for normalized data.

The current issue is where one workseet contains normalized data but the other worksheet within another workbook is not normalized.

Of course, one can use the pivot table wizard to normalize and I am currently exploring this as well.

I believe that one can use one of the following methods to solve the problem;

Using Index & Match

Using Named Ranges & Intersection Operator (Space)

Using Sumproduct formula

Using Vlookup with Match formula

It is just that I have not quite figured out the method to use to solve the problem!

Note, I am currently using the Sumproduct approach - where I need to match up the account number, the month and the category ("Gross", "Expense", etc).

Initially, using the Index & Match approach resulted in a error - "You've entered too many arguments for this function"

The quest continues!

 

[SkipVought]

faq68-5287

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[BxWill]

Is it possible to perform the lookup without normalizing the data?

 

[SkipVought]

Yes using INDEX, MATCH To find the correct date column and MATCH To find the correct row, But you'll need a concatenated key

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[BxWill]

It appears that the reason for the concatenated key is that the index function can not have more than three arguments.

Is this correct?

 

[BxWill]

Still at it...

Also, realized that I need to lookup based on four criteria.

Formula constructed so far;

Index Match – conditional on four criteria – two distinct workbooks

=INDEX('Worksheet1.xlsm'!tblData,
MATCH('Worksheet1'!AcctNbr
&'Worksheet1'!Status
&'Worksheet1'!Month,
J8&E8&B8,0),
MATCH('Worksheet1'!Category,N5,0))

Result: Not successful - Value Error

Thoughts:
1) Destination Worksheet – Worksheet 2 - has the following;

Cell location-------------- Contents----------------------------Description
B8-------------------------1/1/2013 (displayed as “Jan”)--------Month (custom formatted – “mmm”)
E8-------------------------Status-------------------------------Status of project
J8-------------------------‘123456------------------------------Account Nbr
N5-------------------------"Gross Margin"-----------------------Category (entered in row N)

2) Formula was entered as array

Any additional insight is gladly appreciated.

Will revert to the normalization of data in worksheet 1 if Index Match function doesn't work.

Thanks in advance.

 

[SkipVought]

1) first, foremost get all the data into one workbook before trying to put it together.

Then it seems that a SUMPRODUCT() function might work very well, specifying the necessary criteria, rather than creating a key.

Your workbook examples are unintelligible! Please use the PRE control, PREVIEW the results and fix the spacing accordingly before posting any tabular data.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}