Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel Matrix lookup intersection

Excel Matrix lookup intersection

Excel Matrix lookup intersection

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 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

RE: Excel Matrix lookup intersection

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?

Hook'D on Access
MS Access MVP

RE: Excel Matrix lookup intersection

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

RE: Excel Matrix lookup intersection

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.

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

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

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

Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close