Index Match only 4 results required

**willaby** · 07-29-2011, 03:58 AM

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.

**Azam Ali** · 07-29-2011, 04:03 AM

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

**willaby** · 07-29-2011, 06:00 AM

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.

**Azam Ali** · 07-29-2011, 06:51 AM

Hi willaby

find the attatch file with formula.

**willaby** · 07-29-2011, 07:10 AM

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

**willaby** · 07-29-2011, 07:12 AM

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

**Azam Ali** · 07-29-2011, 07:23 AM

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.

**willaby** · 07-29-2011, 07:46 AM

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

**Azam Ali** · 07-29-2011, 08:20 AM

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

**willaby** · 07-29-2011, 01:06 PM

Thanks Azam Ali
That's working great!
Thanks for your help

**Azam Ali** · 07-31-2011, 11:28 PM

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.