Invert Results of an Array Formula

Hi Guys,

I'm working with an array formula (see below) that searches for and organises a particular value (in this case System Name) based on the date that it is due to finish. The formula works fine, but I am trying to set it up in such a way that the results appear at the bottom of the chart, as opposed to the top. I've attached the file in question for clarity.....any assistance would be greatly appreciated.

=IF(ROW()-ROW(T$3:T$23)+1>ROWS(G$3:G$28)-COUNTBLANK(G$3:G$28),"",INDIRECT(ADDRESS(SMALL((IF(G$3:G$28<>"",ROW(G$3:G$28),ROW()+ROWS(G$3:G$28))),ROW()-ROW(T$3:T$23)+1),COLUMN(G$3:G$28),4)))




SC Skyline Rev 01.xls

Here, try this:

put this in cell Q29:

=INDEX(Q$3:Q$23,21-ROW($A1))

then copy across and down. You will need to set up the same conditional formatting as above.

Hope this helps.

Pete

put this in cell Q29:

=INDEX(Q$3:Q$23,21-ROW($A1))

then copy across and down. You will need to set up the same conditional formatting as above.

Hope this helps.

Pete

Thanks guys, it worked a treat!

Hi zbor,

Thanks again for the help with this problem. I was was wondering, how would I go about modifying the formula:

=IF(21-ROWS(R$3:R5)-COUNTIF(E$3:E$100,"?")>=0,"",INDEX(E$3:E$28,SUMPRODUCT(LARGE((E$3:E$28<>"")*ROW(E$1:E$26),COUNTIF(E$3:E$28,"?")-COUNTIF(R$2:R4,"?")))))

if I wanted to have the source data located in another location of the worksheet? For example, I've added a few rows above the table we've been referencing (see attached) and it's now returning different values in the lookup table you developed. Is there a workaround for this, or possibly another formula that would prevent this from happening?

Thanks in advance.

Works a treat! Thanks for that mate!