Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Location
    melbourne, Victoria, Australia
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with a Formula (Excel XP)

    I am creating a spreadsheet as below:-
    A1 B1 C1 D1
    Date Start End Total
    1/1/03 2 4 5
    2/1/03 4 2 8

    In A1 I will enter a date, in column B1 I will enter 2 and in C1 I will enter 4. In D1 I want a formula that will look at the grid (below) and return 5. Does anyone know a formula that will do this for me?
    1 2 3 4
    1 0 3 3 7
    2 3 0 3 8
    3 3 3 0 5
    4 7 5 5 0
    The actual grid will be 1 -40 across and 1-40 down.

    I have attached a copy of the question so you can see the grid and the above table in Word format.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help with a Formula (Excel XP)

    <pre>=index(Table, B1,C1)</pre>


    where Table is the range of the lookup table.

    Steve

  3. #3
    Star Lounger
    Join Date
    Jul 2002
    Location
    melbourne, Victoria, Australia
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with a Formula (Excel XP)

    I am still having problems with the formula. I have attached a copy of the spreadsheet, if you could have a look at it and tell me what I need to do.
    Thanks kindly.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with a Formula (Excel XP)

    You don't say what problems.

    The actual lookup table is not F2:K7 but G3:K7, since row 2 and column F contain index numbers. So try this in D2:

    =INDEX($G$3:$K$7,B2,C2)

    and fill down to D12.

  5. #5
    Star Lounger
    Join Date
    Jul 2002
    Location
    melbourne, Victoria, Australia
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with a Formula (Excel XP)

    Thank you very much for all you help, you have just saved me hours of work. I have been entering the totals manually for the last two years and it takes forever. I

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with a Formula (Excel XP)

    I'm not sure I understand your question correctly, but I think you want this formula in D2, then fill down:

    =INDEX($G$3:$M$9,MATCH(B2,$F$3:$F$9,0),MATCH(C2,$G $2:$M$2,0))

    See attached modified workbook.

  7. #7
    Star Lounger
    Join Date
    Jul 2002
    Location
    melbourne, Victoria, Australia
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with a Formula (Excel XP)

    Thank you for all your trouble, this will now save me hours of work. It's been great that I have gained a understanding of INDEX and MATCH function. One little thing though, I have been trying to work out the formula, but not clear on what the 0, at the end of the MATCH formula, represents.
    [ =INDEX($G$3:$M$9,MATCH(B2,$F$3:$F$9,0),MATCH(C2,$G $2:$M$2,0)) ].
    Can I trouble you this last time for you to explain this? Thanking you again.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help with a Formula (Excel XP)

    That's OK. The optional third argument in the MATCH function can be 1, 0 or -1.

    If it is 1, the function looks for the greatest value in the lookup range that is less than or equal to the search value, and returns its index in the lookup range. This range *must* be sorted in ascending order. This is the default, if you omit the third argument, it is assumed to be 1.

    If it is -1, the function looks for the smallest value in the lookup range that is greater than or equal to the search value, and returns its index in the lookup range. This range *must* be sorted in descending order.

    If it is 0, the function looks for an exact match. The lookup range does not need to be sorted. This is the argument used in the formula for your workbook.

    You can find this information, and more, in the online help. Type MATCH into the Answer Wizard or Help Index.

Posting Permissions

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