Excel Matrix lookup intersection

[mart10]

I am using Office 2010

I need to create an accounting report in Access which relies upon the intersection of data in an Excel matrix. Thing is how do I
a) load the matrix
b) do I do the lookup
so for instance my matrix may look like:



A B C
A Blank 2395 5498
B 1234 Blank 4879
C 5678 4291 Blank

If A is paying B, the rule will lookup first column for A and then go across to find intersection with B to get value 2395 etc etc

 

[dhookom]

I'm not sure what Excel has to do with this although I often use Excel linked to Access tables to analyze data. I believe an Access crosstab query could provide the results you are requesting.

Do you have tables in Access already? If so, could you share the table structures?

Duane
Hook'D on Access
MS Access MVP

 

[mart10]

Excel has everything to do with it because the accounting matrix I need to lookup onto is held in a 3rd party system. I need Access to a complex report based on this data

 

[dhookom]

I assume you have a table linked from either Excel or the 3rd party system. Is this what your linked records look like? If so, there has to be a column name for the first column. Also, note this is not a normalized structure but I assume you don't have any control over it.

[pre]
A B C
A 2395 5498
B 1234 4879
C 5678 4291[/pre]

A better structure would be:
[pre]
PayFrom PayTo PayAmt
A B 2395
A C 5489
' etc
C B 4291[/pre]

What does your actual data look like with actual field and table names? Do the A, B, and C values change frequently?

Duane
Hook'D on Access
MS Access MVP