Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sumproduct and index/match formulas (2003)

    Can anyone help me with a few examples of how the sumproduct and index/match formula work? I have heard that they are pretty useful formula's to know, would you agree with this comment?

    Kind regards
    Hayden

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: sumproduct and index/match formulas (2003)

    Hi Hayden:
    See the attachment for an example of a two way lookup! I find it very handy myself!
    This of course is only one way to use the functions...obviously nested in this example. In my opinion, they are not often used by themselves. Its more useful to nest them!

    With regards to SUMPRODUCT. This is an array function. Have a look at the Excel Help file. Select SUMPRODUCT in the Paste Function Dialog and click the ? button.
    It explains the function very well...
    Regards,
    Rudi

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: sumproduct and index/match formulas (2003)

    Hi Hayden

    index/match is very useful for performing lookups and is more versatile than the standard lookup formulas, especially for performing 2 way lookups. For examples see:
    MrExcel.com
    mvps.org
    excel-vba.com

    Sumproduct is very usefuland can be an alternative to using arrays. For examples see:
    meadinkent.co.uk
    excel-vba.com
    excelonlinetraining.com

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumproduct and index/match formulas (2003)

    Click on the Lounge Search button, enter "sumproduct index match" (without the quotes) into the Search For box, change the Search Options from And to Or, then click on Submit. You will find many examples here in the Lounge.
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumproduct and index/match formulas (2003)

    On SumProduct:

    http://tinyurl.com/5vnur

    INDEX() is a fast, non-volatle, accessor function. Combined with MATCH(), it forms the most general lookup function. LOOKUP() is comparable to the combination when the match-type of MATCH() is set to 1/TRUE.
    Microsoft MVP - Excel

Posting Permissions

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