Dynamic Range For "SMALL" Function Then Offset LookUp

Hi,

I have an annual budget that contains all the days in year, and various formulas calculating values.

That bottom row of the table has a simple indicator to show if the day is in the past.

Please see the attached demo of the layout...

demo.xlsx

I want a formula to create a dynamic range from "tomorrow" to the end of the sheet (J:V in the example) but this will move as the date changes.

I will then use that range to return the 3 smallest values (A7:A9 in the example as today is 8th July), but I also want it to return the date that small value occurs on (B7:B9 in the example). If the small value occurs more than once, that it just needs to return the first and associated date.

Row 1 contains the date to report, row 4 contains the balance to check and row 5 contains the day indicator.

Many thanks.

Hi

I don't understand how these smallest values are obtained. Are they the smallest in each category, Profit, Loss, Balance?

The smallest values are calculated using the SMALL() function, testing the balance line, but only J4:V4 as these are the columns in the future.

I realised there was a slight mistake in the demo SMALL() function included - I've update the demo file and attached below.

demo.xlsx

1 way using this ARRAY formula.

=SMALL(IF($B$5:$BT$5<>"x",IF($B$4:$BT$4>0,$B$4:$BT$4)),ROW(A1))

Copy down.

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

Array formula.

=SMALL(IF($B$5:$BT$5<>"x",IF($B$4:$BT$4>0,$B$4:$BT$4)),ROW(A1))&" On "&TEXT(INDEX($B$1:$BT$1,MATCH(SMALL(IF($B$5:$BT$5<>"x",IF($B$4:$BT$4>0,$B$4:$BT$4)),ROW(A1)),$B$4:$BT$4,0)),"dd/mm/yyyy")

Duplicate post....

........................

A non array solution for B7 ( and pull down)

=TEXT(SMALL(INDEX($4:$4,MATCH(TODAY()+1,$1:$1,0)):INDEX($4:$4,MATCH(9.9999E+307,$4:$4)),ROW()-6),"£#.00")&" on "&TEXT(INDEX($1:$1,MATCH(SMALL(INDEX($4:$4,MATCH(TODAY()+1,$1:$1,0)):INDEX($4:$4,MATCH(9.9999E+307,$4:$4)),1),$4:$4,0)),"dd/mm/yyyy")