Swapping AGGREGATE with SMALL for backwards compatibility with Excel 2007

[jahsquare]

Hi, I'm trying to make this formula compatible with Excel 2007, I think I just need to replace AGGREGATE with SMALL but I'm getting errors. The INDIRECTS are referring to Data Validation cells where I have user-selectable filter criteria. Here is the original formula that works in modern versions:

=IF(ROWS(V$4:V4)>$A$36,"",
INDEX(Table1[Name],
AGGREGATE(15,6,(ROW(Table1[Name])-ROW(Table1[#Headers]))/
(
(INDIRECT("Table1[["&$A$30&"]:["&$A$30&"]]")<>"")*
(INDIRECT("Table1[["&$A$31&"]:["&$A$31&"]]")<>"")*
(INDIRECT("Table1[["&$A$5&"]:["&$A$5&"]]")<>"")*
(INDIRECT("Table1[["&$A$8&"]:["&$A$8&"]]")<>"")*
(INDIRECT("Table1[["&$A$11&"]:["&$A$11&"]]")<>"")*
(INDIRECT("Table1[["&$A$14&"]:["&$A$14&"]]")<>"")*
(INDIRECT("Table1[["&$A$17&"]:["&$A$17&"]]")<>"")*
(INDIRECT("Table1[["&$A$20&"]:["&$A$20&"]]")<>"")*
(INDIRECT("Table1[[Year]:[Year]]")>=$A$24)*
(INDIRECT("Table1[[Year]:[Year]]")<=$A$26)),
ROWS(V$4:V4))))

And here is where I'm at with my attempt to use SMALL:

=IF(ROWS(V$4:V4)>$A$36,"",
INDEX(Table1[Name],
SMALL(IF((ROW(Table1[Name])-ROW(Table1[#Headers]))/
(
(INDIRECT("Table1[["&$A$30&"]:["&$A$30&"]]")<>"")*
(INDIRECT("Table1[["&$A$31&"]:["&$A$31&"]]")<>"")*
(INDIRECT("Table1[["&$A$5&"]:["&$A$5&"]]")<>"")*
(INDIRECT("Table1[["&$A$8&"]:["&$A$8&"]]")<>"")*
(INDIRECT("Table1[["&$A$11&"]:["&$A$11&"]]")<>"")*
(INDIRECT("Table1[["&$A$14&"]:["&$A$14&"]]")<>"")*
(INDIRECT("Table1[["&$A$17&"]:["&$A$17&"]]")<>"")*
(INDIRECT("Table1[["&$A$20&"]:["&$A$20&"]]")<>"")*
(INDIRECT("Table1[[Year]:[Year]]")>=$A$24)*
(INDIRECT("Table1[[Year]:[Year]]")<=$A$26)),1),
ROWS(V$4:V4))))

This returns a #Div/0 error. I also tried IFERROR(SMALL(IF(...)),"") but then I get a #Value error... I am doing CTRL+SHIFT+Enter.

Any help would be much appreciated! Thanks..

 

[barry houdini]

AGGREGATE function can be set to ignore errors (the 6 as 2nd argument) so in that version you are dividing the row numbers by the conditions - when the conditions aren't met you get #DIV/0! errors, which is fine because AGGREGATE just ignores them.....but SMALL won't so the construction needs to be different, i.e. usually an IF function which returns the row numbers if the conditions are met (and no errors, just FALSE values), e.g.

[LEFT][COLOR=#333333][FONT=monospace]=IF(ROWS(V$4:V4)>$A$36,"",
INDEX(Table1[Name],
SMALL(IF(
(INDIRECT("Table1[["&$A$30&"]:["&$A$30&"]]")<>"")*
(INDIRECT("Table1[["&$A$31&"]:["&$A$31&"]]")<>"")*
(INDIRECT("Table1[["&$A$5&"]:["&$A$5&"]]")<>"")*
(INDIRECT("Table1[["&$A$8&"]:["&$A$8&"]]")<>"")*
(INDIRECT("Table1[["&$A$11&"]:["&$A$11&"]]")<>"")*
(INDIRECT("Table1[["&$A$14&"]:["&$A$14&"]]")<>"")*
(INDIRECT("Table1[["&$A$17&"]:["&$A$17&"]]")<>"")*
(INDIRECT("Table1[["&$A$20&"]:["&$A$20&"]]")<>"")*
(INDIRECT("Table1[[Year]:[Year]]")>=$A$24)*
(INDIRECT("Table1[[Year]:[Year]]")<=$A$26),
[LEFT][COLOR=#333333][FONT=monospace]ROW(Table1[Name])-ROW(Table1[#Headers])),[/FONT][/COLOR][/LEFT]
ROWS(V$4:V4))))[/FONT][/COLOR][/LEFT]

 

[jahsquare]

Thank you, that does it. I'm learning, slowly...