+ Reply to Thread
Results 1 to 11 of 11

Index Match only 4 results required

  1. 07-29-2011, 03:58 AM #1
    pepe.r.taylor
    pepe.r.taylor is offline
    Registered User
    Join Date
    07-29-2011
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    10

    Index Match only 4 results required

    Hi there.

    I have 2 lists of data - Column A = Names, Column B = Dates.
    The dates will always be the first of every month eg 01/08/2011 , 01/09/2011 etc.
    The results I need to display from these details are any 4 names matching the next month for example:

    Today's date is 29/07/2011 and the results I require would be those names matching 01/08/2011. However, if there are only 3 results, the fourth cell would remain blank, but if there are 6 results the formula can pick the first 4.

    I hope that makes sense.
    Thank you, in advance.
    Last edited by pepe.r.taylor; 08-01-2011 at 08:36 AM.
    Reply With QuoteRegister To Reply
  2. 07-29-2011, 04:03 AM #2
    Azam Ali
    Azam Ali is offline
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Index Match only 4 results required

    Upload a sample workbook with sample data and your desired results

    In order to attach a file Click the Go Advanced button, and click on the Manage Attachments button
    Azam
    If you want to say Thank you to a member, click the reputation icon (Star) in the left bottom of the post.
    For prompt answer, be descriptive, concise, short, direct, and to-the-point.
    Reply With QuoteRegister To Reply
  3. 07-29-2011, 06:00 AM #3
    pepe.r.taylor
    pepe.r.taylor is offline
    Registered User
    Join Date
    07-29-2011
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: Index Match only 4 results required

    Thanks Azam Ali

    I have attached an example sheet 1 (Data Set) is a truncated list of sample data with a column of names and a column of corresponding dates. Sheet 2 (Results) has space for 4 results from the list in sheet 1 (Data Set).

    So the formula would need to check the dates column for any results that match the next coming month... eg todays date is 29/07/2011 so this month we would be looking for results matching 01/08/2011 (in my data set there are 5 matches, however I only require 4 results to be placed in the results boxes on the Results sheet so it can ignore one of them - subsequent months may only have 3 results though, in which case I would only want 3 results displayed and the fourth results box left blank - I would imagine that where there aren't enough results the additional results boxes could probably appear blank with Conditional Formatting).

    The formula would have to automatically update to take into account a new month so if I next open the spreadsheet in September, it will automatically be looking for results matching 01/10/2011.

    Thanks. I hope that makes sense.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 07-29-2011, 06:51 AM #4
    Azam Ali
    Azam Ali is offline
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Index Match only 4 results required

    Hi willaby

    find the attatch file with formula.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 07-29-2011, 07:10 AM #5
    pepe.r.taylor
    pepe.r.taylor is offline
    Registered User
    Join Date
    07-29-2011
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: Index Match only 4 results required

    Hi Azam Ali

    Thanks for the formula however no results appear. The results cells remain blank on the version you have updated. It doesn't display an error but it doesn't produce a result either.
    I have completed using an array (C+S+E) but still nothing.

    I'm confused.
    Thanks
    Reply With QuoteRegister To Reply
  6. 07-29-2011, 07:12 AM #6
    pepe.r.taylor
    pepe.r.taylor is offline
    Registered User
    Join Date
    07-29-2011
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: Index Match only 4 results required

    I have just been looking into EOMONTH and I am working on 2003 which I don't think has this function. Is there an alternative?
    Thanks
    Reply With QuoteRegister To Reply
  7. 07-29-2011, 07:23 AM #7
    Azam Ali
    Azam Ali is offline
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Index Match only 4 results required

    use this file in which date function is used

    i do not have 2003 and i am not sure that you could use date function.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 07-29-2011, 07:46 AM #8
    pepe.r.taylor
    pepe.r.taylor is offline
    Registered User
    Join Date
    07-29-2011
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: Index Match only 4 results required

    Hi Azam Ali

    Thanks for that. I can now get results in the boxes, however I do not know how many rows of data there will be and if I increase the ranges to, for example, A1:A100 etc then it doesn't return a value. Is there a way, using worksheet formula to ignore currently blank rows?

    Thanks
    Reply With QuoteRegister To Reply
  9. 07-29-2011, 08:20 AM #9
    Azam Ali
    Azam Ali is offline
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Index Match only 4 results required

    the formula is already ignoring the blank cells

    use this in cell B3 with ctrl+Shift+Enter

    =LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX('Data Set'!$A:$A,SMALL(IF('Data Set'!$B:$B=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),ROW('Data Set'!$B:$B)),ROWS(B$1:B1)))))

    and copy down
    Reply With QuoteRegister To Reply
  10. 07-29-2011, 01:06 PM #10
    pepe.r.taylor
    pepe.r.taylor is offline
    Registered User
    Join Date
    07-29-2011
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    10

    Re: Index Match only 4 results required

    Thanks Azam Ali
    That's working great!
    Thanks for your help
    Reply With QuoteRegister To Reply
  11. 07-31-2011, 11:28 PM #11
    Azam Ali
    Azam Ali is offline
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Index Match only 4 results required

    welcome

    If your problem is solved, mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1