+ Reply to Thread
Results 1 to 13 of 13

Index and Match help needed

  1. #1
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    211

    Index and Match help needed

    Hi,
    I have a spreadsheet with budget information. I want to use that sheet to populate a different one that will combine all the codes for all the locations into one line. I assume I need Index and Match for this.
    For example, I may have loc 1 with 6410.1000 for $50.00 and loc 2 with 6410.1000 for $100.00 and so on the new sheet I want 6410.1000 to show $150.00 total.
    There will be many entries to this.
    I attached the spreadsheet with the data. The blue cells show where there are multiple account codes for multiple locations which are what I want to combine into one line on a new sheet.
    Thank so much...don't know what I would do without this site.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Index and Match help needed

    Wouldn't this just be a =SUMIF issue?

    https://support.office.com/en-us/art...2-1697a653039b

  3. #3
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    211

    Re: Index and Match help needed

    Sorry, I was thinking this through wrong.
    I don't want the numbers to combine...that will happen on the pivot table.
    I want to want to do a lookup on that test budget sheet, and have it return the value I need.
    So on my new sheet, I want to look up company 01 AND code 6410.1000 and return the dollar amount shown for that row.
    Then look up company 02 AND code 6410.1000 and return the dollar amount shown for that row.
    Does that make sense.

    Too much going through my mind and in a crunch to make this work.

  4. #4
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Index and Match help needed

    No problem. Where do you want the results?

  5. #5
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    211

    Re: Index and Match help needed

    Thank you.
    I will attach the form where the information should populate. It would go in the Jan 2015 and Feb 2015, etc. columns.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Index and Match help needed

    Can you provide a couple examples of your desired outcomes? For example, on the worksheet "Entry Sheet" the January 2015 total for Co 01 and Account 6124.0000-14-10 should be $XXX (should appear in Cell F2). And explain where that data has come from. For example, $XXX is the subtotal for that company and that account in January listed in Column E on Sheet1.

  7. #7
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    211

    Re: Index and Match help needed

    Here is an updated sheet.
    So the Entry sheet will come from the Database tab. Right now it looks like a simple copy and paste could be done, but it will be more complicated than that. So basically, the so co 01 and acct 6623.6000-14-45 should look up on database tab and return what it finds for co 01 and 6623.6000-14-45 and the result should be 2664.00.
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Index and Match help needed

    Will a company/account combination ever have more than one row?

  9. #9
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    211

    Re: Index and Match help needed

    It should not. They should only ever have one account for each company as it is already compiled into one on the database tab.

  10. #10
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Index and Match help needed

    Then I'm not really sure that you need to do anything. Take the Database spreadsheet and just insert Budget columns like you have in the Entry Sheet. It doesn't seem that you're performing any calculations. Just reformatting the data you already have.

  11. #11
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    211

    Re: Index and Match help needed

    The data that is on the entry sheet in columns A:E will change, I pasted that in there for now. As the managers key in those codes that they want to use for each company, I want it to populate in the Jan 2015, Feb, 15 column, etc. The database tab will have thousands of rows of data when it is all done. So when the entry sheet has those codes added to it, the months should populate by looking them up on the database tab. I only filled the entry sheet with data so you could see what the end result was as I thought that was what you were asking.

  12. #12
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Index and Match help needed

    Maybe this is what you're looking for. Populated the data for January. If it's correct, I'd just copy the formulas over and THEN insert columns for the budget lookups.

  13. #13
    Forum Contributor
    Join Date
    12-21-2004
    Location
    MN
    MS-Off Ver
    Microsoft 365
    Posts
    211

    Re: Index and Match help needed

    OMG.....YES!!!!!!
    That is exactly what I needed hoyasaxa215!
    Thank you SO much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] index match help needed
    By krish2503 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-02-2015, 09:29 AM
  2. Second and Third Match on Index Needed
    By excelnoob927 in forum Excel General
    Replies: 0
    Last Post: 11-05-2014, 06:22 PM
  3. Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  4. Index Match help needed
    By SKIDDERWOLF in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-14-2013, 04:19 AM
  5. Index match help needed
    By jmanz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-06-2012, 05:52 AM
  6. Help needed with INDEX MATCH...
    By Nerf Herder in forum Excel General
    Replies: 4
    Last Post: 06-27-2012, 04:40 AM
  7. [SOLVED] Index and Match Help Needed
    By carl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-26-2005, 05:05 PM

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