Help with Index Match formula

Copper Contributor

Hi there,

 

I'm having trouble writing a formula, and wonder if anyone can point me in the right direction.

 

I want to produce a unique list of Names based on a fixed date (Year End) falling between two dates in a table. I think I can do this with Index, Match and Unique, but I'm struggling to get them all working together. I'd initially like them ordered alphabetically, but may want to add in an aggregate value from the table as well, but that's a secondary issue.

 

The Table, its called Table2, has 125 rows and columns A through to AE, so 31. 

The look up date will be in Cell E3 on a separate worksheet

The text I want to return in the unique list is in column D or 5 (in terms of the column number) of the table

The start date is in Column Y or 25

The finish date is in Column Z or 26

and the column with the value I might want to aggregate is X or 24.

 

I keep getting a spill error and not sure how to debug it, I've likely got stuff in the wrong order.

 

Many thanks if someone can point me in the right direction.

 

Adam

3 Replies

@Adamlang1890 

Column D (4) or E (5) ?

 

Try a formula like this, using the field names from the table

 

=SORT(UNIQUE(FILTER(Table2[ColumnD], (Table2[ColumnY]<=E3)*(Table2[ColumnZ]>=E3))))

Spill error means that the area needed to show all the resulting values, is not empty.
The formula cell (anchor) is always top left. Click on it and you will see a blue shimbering frame that shows the needed spill area.
If you have other things on that sheet, you could try to cut the formula cell and paste it on row one on a blank sheet.
Row one? If a formula results in a full column, it cannot show all the results, from another row than the top row.
You probably have data (not empty cells) in the needed spill area or you formula gives a much wider hit than you expect.
The two cinditions may be the reason for finding everything (a milion rows). Filter takes only one condition and by multiplying severeal conditions the result becomes 1 (true) if they all are true.

@Hans Vogelaar 

 

Thanks, I managed to work it out in the end. I did it in two steps producing an intermediary array which will be helpful to check certain data before the final list is created.

 

Turns out because of the wider dataset I didn't really need to filter by date as the data I wanted in terms of year to end was already in a particularly column (X) so X being greater than one, was the only bit of filtering I needed.  Main List is the name of the workbook with the data in, its also a table, but this referencing seemed to work fine.

 

=SORT(FILTER(FILTER('Main List'!A:AE,'Main List'!X:X>1,),{1,0,0,0,1,0,1,0,0,0,1,1,1,0,0,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0}),7,-1)

 

=UNIQUE(SORT(C7:C100))

 

C7 being the start of the list I wanted unique values for sorted alphabetically.

 

Thanks!