Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Populate Cells Based on Matched Criteria and Enter Value From Another Cell

    If I have my “wording” wrong in my title and/or questions, I apology in advance. Although, I have a basic knowledge of Excel functions like sum, count, max and min, etc.; I have a very limited knowledge of advanced functions or formulas.

    I have a workbook (see attached) with a function in C2 that equals the current date: =Today(). In C3, I would like a function to look at the date in C2 then match that date to the date I have in Column F that starts with 1/1/2015 in F3 going thru 12/31/2015 in F367. Once the date in C2 is matched, the function would return a value from in a range from J3:J367. As an example, using today’s date July 27, 2015, the value would be 208.

    I’ve been spinning my wheels for over a week researching the best solution to this. I currently have an Index (array) function that works:

    {=INDEX($J$3:$J$367,MIN(IF(($C$2=$F$3:$F$367),MATC H(ROW($J$3:$J$367),ROW($J$3:$J$367)))))}

    However, my question with my limited knowledge of the advanced functions, is this the best approach? I’m not sure the advantage or disadvantages versus other potential Lookup and Reference Functions that that are available (e.g., VLOOKUP, etc.).

    I would greatly appreciate any thoughts or comments using the current solution.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    sbdale,

    Here's another possibility: =OFFSET($C$2,MATCH($C$2,$F$3:$F$367,0),7)

    Note that the offset references C2 vs C3 because you are working with offsets. If you referenced C3 you would have to subtract 1 from the Match function result.

    BTW: I'd advise a Dynamic Range Name for the Date list that way you will never have to change the formula.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    sbdale (2015-07-28)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Sbdale,

    Not sure why you are using an array formula where

    =INDEX(F3:J367,MATCH(C2,F3:F367,0),5) in cell C3 would provide the same results (208 in this case).

    HTH,
    Maud

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    sbdale (2015-07-28)

  6. #4
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    Why not just use =VLOOKUP(C2,F3:J367,5,0) - this seems to much simpler that the other suggested formulae.

    Regards,
    Maria
    Maria
    Simmo7
    Victoria, Australia

  7. The Following User Says Thank You to simmo7 For This Useful Post:

    sbdale (2015-07-28)

  8. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi
    or this

    =C2-DATE(YEAR(C2),1,1)+1


    Then there is =DatedIf function.
    =DatedIf(date1, date2, intervalType)

    Cheers
    G
    Last edited by geofrichardson; 2015-07-28 at 04:43.

  9. The Following User Says Thank You to geofrichardson For This Useful Post:

    sbdale (2015-07-28)

  10. #6
    Lounger
    Join Date
    May 2002
    Posts
    36
    Thanks
    9
    Thanked 0 Times in 0 Posts
    All,

    Thank you for your suggestions. I really appreciate you taking the time to provide feedback. All the suggestions appear to be better and a lot easier than the array function that I initially used.

    Like I indicated in my original post, I have limited known of advanced functions. So, I reviewed each of your suggestion to ensure I understand how they worked.

    My only question is for Geof. Your function (=C2-DATE(YEAR(C2),1,1)+1) works; however, I don’t understand why. The 1,1)+1) is throwing me a curve. If possible, could provide a brief description how the function works or a website that would expand on the functions. I did attempt to some research, but it only confused me more.

    Again, thank you for all the suggestions.

  11. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi

    ..think of it like this, if C2 is 1st January then
    C2 - date(year(c2),1,1) would just give you 1st Jan - 1st Jan = zero
    ..so add +1 at end
    i.e. c2 - date(year(c2),1,1)+1 would give you 1 (if the date c2 was 1st Jan) etc etc etc

    zeddy
    •Satellite Procurement Specialist

  12. #8
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Date arithmetic to find day number

    Hi sbdale
    Welcome to the world of date arithmetic.
    You need to understand that excel treats dates as serial numbers. (Capt Kirk says star date 42214)
    We mere mortals need dates formatted to show years, months and days.

    See this post from the office support folks at microsoft.

    Enter a date in a cell and experiment with the formatting (Ctrl + 1).

    Experiment with the datedif() function as well. See this link for info.

    Cheers
    G

Posting Permissions

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