+ Reply to Thread
Results 1 to 3 of 3

Index & Match use with Sumproduct and Arrays.

  1. #1
    Registered User
    Join Date
    03-13-2014
    Location
    Leeds, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Angry Index & Match use with Sumproduct and Arrays.

    Hi

    Im struggling with a particular formula issue, and not sure whether this can be done or whether it will need to be done with VBA.

    The page attached contains a table of rates, and a timesheet. the time sheet contains only hours, and teh table contains the rates.
    What I would like to have as a total, is the correct end value i.e hours multiplied by correct rate for the date in the top of the column where the time is kept.

    I've managed to work out how to select the rate using match and index, but I'm struggling to get the rate to multiple the hours and then display the correct total. my work in progress is attached.

    I had considered just having both the rate and the hours on the same sheet but everyone has access to imput their times, and the rates ... well get in teh way and cause more hassle than they are worth, if people get to see what others are charged out at.

    as the time sheet expands and the rates are increased from time to time, I need alway to retain the old rate that was applicable at the time the work was carried out, not just the newest rate.

    any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Index & Match use with Sumproduct and Arrays.

    Try this array formula:

    =SUMPRODUCT(VLOOKUP(I3,$A$2:$F$43,MATCH($J$2:$M$2,Revisiondate,1)+2,0)*J3:M3)

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Quang PT

  3. #3
    Registered User
    Join Date
    03-13-2014
    Location
    Leeds, England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Index & Match use with Sumproduct and Arrays.

    Thanks Bebo,

    that works!

    not tried a vlookup with a match before.

    regards

    Rob
    Last edited by robwebber; 02-07-2015 at 02:27 PM.

+ 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. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  2. sumproduct IF two arrays match a range of criteria?
    By Paul Sword in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-10-2013, 10:06 AM
  3. Sum Index Match Arrays
    By CaesarBob in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-19-2012, 04:38 AM
  4. [SOLVED] Index & Match returning incorrect value. Arrays fixed and exact match used.
    By SDes in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-07-2012, 08:29 PM
  5. referencing arrays in index and match
    By inky in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-31-2007, 07:06 AM

Tags for this Thread

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