+ Reply to Thread
Results 1 to 6 of 6

Excel 2007 : Lookup column data based upon row/column matches

  1. #1
    Registered User
    Join Date
    05-25-2011
    Location
    Vancouver, WA
    MS-Off Ver
    Office 2016
    Posts
    6

    Lookup column data based upon row/column matches

    I am trying to figure out how to use data from the attached spreadsheet to consolidate and evaluate data.

    Essentially I need to query the data on the "Feb 2011- Data" tab to lookup the salesman account # and set name and number of times used each month. I would like to put this figure on the "2011 EC" in order to evaluate the usage of each set and which salesman are using them.

    For example, I have highlighted on the first tab what I would need to pull. I want to see the number of times salesman 703521 has used set name 112A in the month of February. I need to do this with each salesman/set for upcoming months as that data becomes available and I can substitute a new table into the "Feb 2011- Data" tab as fresh data is available.


    I thought I was getting close with the use of MATCH, but can't seem to get this to pan out.

    Appreciate any help on this one.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-25-2011
    Location
    Vancouver, WA
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: Lookup column data based upon row/column matches

    Any ideas here? I was thinking something along the lines of an IF statement with nested MATCH to check salesman number and set name against the required cells on the 2011 EC tab.

    Maybe I'm headed in the wrong direction and need to pursue the formula differently.

    Thanks... appreciate any insight.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Lookup column data based upon row/column matches

    Hi DirtElk and welcome to the forum,

    See if Pivot Tables works for you on the attached.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    05-25-2011
    Location
    Vancouver, WA
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: Lookup column data based upon row/column matches

    Ultimately I would like to get the sum as you have laid out in the pivot table, but I also need to break it down per set name and rep.

    I have a set of inventory "112A" that is being ordered by various sales reps (703113, 703521, etc.). I would like to see not only the total number of times it was ordered each month (three times total in April), but also the numbers of times each individual rep is ordering to identify trends for specific inventory.

    Does this make sense? Every rep can order any of the sets, so I have many different sets that have been requested by a single rep which I would like to look at individually.

    The data that I receive is just a dump and I am trying to figure out how to get it in a friendly format with a template which sorts it for specific reps based on they office location as each month I will get an update report.

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Lookup column data based upon row/column matches

    Hi,
    Look at the attaached where I've added a count column. You need to study Pivot Tables and see how robust and versitile they are. I think they are what you need but you will have to learn a little to use them effectively.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-25-2011
    Location
    Vancouver, WA
    MS-Off Ver
    Office 2016
    Posts
    6

    Re: Lookup column data based upon row/column matches

    Perfect... that looks like the direction I need to go. I will dig into it more and see what I can create.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1