+ Reply to Thread
Results 1 to 4 of 4

Display last 6 month's worth of data matching a specific criteria

  1. #1
    Registered User
    Join Date
    04-11-2014
    Location
    Washington DC, USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Display last 6 month's worth of data matching a specific criteria

    Hi,

    I have a table of historical values which I want to return the last 6 months based on the value selected in Cell A7. The data will be dynamically increased each month. I haven't been to find a formula that works without having to explicitly name each row when I do an 'IF' statement. Is there any other way? I also tried to combine vlookup with offset but it did not return the correct value. I want to shorten this formula as I may add more rows in the future. Any help would be appreciated. I am using Excel 2007.

    I have attached an example of my problem with the only solution I have come up with so far.

    Thanks,

    Example.xlsx

  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: Display last 6 month's worth of data matching a specific criteria

    Will there always be at least 6 months worth of data? If not then what should happen?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    04-11-2014
    Location
    Washington DC, USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Display last 6 month's worth of data matching a specific criteria

    Yes, there will always be at least 6 month's worth of data - it won't be exactly 1 year.

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

    Re: Display last 6 month's worth of data matching a specific criteria

    One way...

    This array formula** entered in B7 and copied across to G7:

    =INDEX($B2:$M4,MATCH($A7,$A2:$A4,0),LARGE(IF(INDEX($B2:$M4,MATCH($A7,$A2:$A4,0),0)<>"",COLUMN($B2:$M4)),7-COLUMNS($B7:G7))-COLUMN($B2)+1)

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

+ 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. Display & summing value based on 3 criteria month
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-03-2014, 07:02 PM
  2. Move Rows of Data from a Specific Column (Based on Matching Criteria) to a new Sheet
    By jordan1214 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-21-2013, 02:30 AM
  3. To get sum of values matching to specific criteria
    By Bhawna in forum Excel General
    Replies: 2
    Last Post: 01-12-2012, 12:28 PM
  4. Counting the Number of Dates with Matching Month Criteria
    By zom in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-17-2008, 07:47 PM
  5. Counting the Number of Dates with Matching Month Criteria
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 08-25-2005, 05:50 AM

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