+ Reply to Thread
Results 1 to 5 of 5

check in which range a date falls and return a specific value

  1. #1
    Registered User
    Join Date
    04-24-2013
    Location
    Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    2

    check in which range a date falls and return a specific value

    I am having a problem, which I did not manage to solve via other threads on this forum, so I hope someone can help me.

    In the attached file I am looking for the price of a material (column A) based on the entry date.
    So the formula first needs to check which Material is related (column E) and then in which date range it falls (columns F & G) and then returns the price (column H).
    I was not able to create an unique value to create a VLOOKUP, I also had a look at INDEX/MATCH and SUMPRODUCTS, but without much success.
    The final data will be over a large amount of lines.

    Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: check in which range a date falls and return a specific value

    Try this...

    =SUMPRODUCT(--(E$2:E$10=A2),--(B2>=F$2:F$10),--(B2<=G$2:G$10),H$2:H$10)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: check in which range a date falls and return a specific value

    P.S.

    You may have to replace the commas with semi-colons.

  4. #4
    Registered User
    Join Date
    04-24-2013
    Location
    Amsterdam
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: check in which range a date falls and return a specific value

    that works very well......thanks for your very fast help Tony.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: check in which range a date falls and return a specific value

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 07-14-2017, 12:04 PM
  2. [SOLVED] Check a date to find if it falls between a range of dates
    By Kausch in forum Excel General
    Replies: 3
    Last Post: 10-21-2014, 07:56 AM
  3. [SOLVED] If statement to check if date falls between date range
    By mcranda in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-21-2013, 07:31 PM
  4. check whether a date falls in a range
    By RGB in forum Excel General
    Replies: 1
    Last Post: 05-19-2006, 09:20 AM
  5. Return a specified date when it falls within a range....
    By Nokose451 in forum Excel General
    Replies: 1
    Last Post: 01-16-2006, 06:10 PM
  6. how to check if date falls within range
    By Bharat Saboo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-30-2005, 06:40 AM
  7. [SOLVED] Can I check if a date falls between a range then sum other # if tr
    By clovins1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-08-2005, 10:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1