Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Use MATCH or INDEX? (Excel 2003)

    I'm never certain which of these to use...I have a list of locations; people are in various locations; there are 3 types of jobs being done....I want to calculate how many of each job type is being done in each location (see sample)..........I tried to use MATCH and a LOOKUP but it always returns #NAME...??...any ideas? I read about an ARRAY formula but didn't try it b/c it seemed that it calculates slowly ??..??
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Use MATCH or INDEX? (Excel 2003)

    I would probably use a pivot table for that, but the formula you need is:
    =SUMPRODUCT(($C$5:$C$14=$H5)*($D$5:$D$14=I$4))
    in I5, then copy across and down. (see attached for both approaches)
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Use MATCH or INDEX? (Excel 2003)

    Thanks, Rory.....for the pivot table...1st one I'e seen that I think I can understand....can I use the formula you gave me if the source data is on a different wksheet from the target data/formula?

    EDIT: Thank you, Rory (and bosco-yip)...I got it all working...now I need to learn about pivot tables..

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Use MATCH or INDEX? (Excel 2003)

    Try………

    Cell I5, entered the formula and copied across and down

    =SUMPRODUCT(($C$5:$C$14=$H5)*($D$5:$D$14=I$4))

    or

    =SUMPRODUCT(--($C$5:$C$14&$D$5:$D$14=$H5&I$4))


    Regards

Posting Permissions

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