Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have two columns of data. One column (A) has the dates listed in it (01/01/09 - 12/31/09), the other column ( has some numbers in it that get entered in to it when data is available. The numbers entered into column B correspond to the date they apply to. Some times, there are days where no data is entered, weekends, holidays and other various reasons.

    What I'm trying to do is find the last number entered into column B, then find the date it corresponds to. I've been able to do this with a MAX IF array, but I would like to try and avoid this option if at all possible since array formulas tend to take longer to calculate. I'm going to need a total of 47 of these type of formulas, which again is the reason to avoid the array. Is there and easier way to do this with out having a formula that requires a lot of calculations that will slow the sheet down? Thanks for any help!

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The following finds the last number in the B column and returns the value in the A column for the same row.
    =INDEX(A1:A100,MATCH(9.9E+307,B1:B100))

    Adjust your ranges as needed.

  3. #3
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='796815' date='07-Oct-2009 13:36']The following finds the last number in the B column and returns the value in the A column for the same row.
    =INDEX(A1:A100,MATCH(9.9E+307,B1:B100))

    Adjust your ranges as needed.[/quote]


    Thanks for the suggestion. That seems to do the trick. I knew there was a better way. What is the purpose of matching to 9.9E+307?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    9.9E307 (or rather 9.99999999999999E307) is the largest number that one can enter in a cell in Excel, so it is virtually impossible that MATCH will find this value. If MATCH (with the third argument omitted) can't find a value larger than or equal to the search value, it will return the index of the last non-blank cell in the search range.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='b0bito' post='796819' date='07-Oct-2009 14:02']Thanks for the suggestion. That seems to do the trick. I knew there was a better way. What is the purpose of matching to 9.9E+307?[/quote]

    The Match function, in this form - using no "Match Type", assumes the numbers in the column are sorted ascending. It will return the first exact match for the lookup value or the first value that is less than the lookup value.

    9.9E+307 (99 followed by 306 zeros) is typically used since it is highly unlikely your column would use that large of a number.

  6. #6
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans & mbarron,

    Thank you very much for the explanation. That makes perfect sense.

Posting Permissions

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