FILLDOWN

=FILLDOWN(range)

range
a column of values with gaps consisting of blanks, errors, or zeros to be filled in from the next value above it.

The value of a previous cell is propagated to the null-valued cells below in the columns specified.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,929
Office Version
  1. 365
Platform
  1. Windows
FILLDOWN: The value of a previous cell is propagated to the null-valued cells below in the columns specified.

FILLUP: The opposite.

FILLUP uses a helper LAMBDA `REVTABLE`, which is listed below.

FILLDOWN/FILLUP
ABCDEFGHIJKLMNOPQR
1FILLDOWN w/ errorFILLDOWN w/ zeroFILLDOWN w/ blankFILLUP w/ errorFILLUP w/ zeroFILLUP w/ blank
2
3111111#N/A3033
4#N/A1011#N/A3033
5#N/A1011#N/A3033
6#N/A1011#N/A3033
7#N/A1011#N/A3033
8222222#N/A3033
9#N/A2022333333
10#N/A2022#N/A2022
11#N/A2022#N/A2022
12333333#N/A2022
13#N/A3033222222
14#N/A3033#N/A1011
15#N/A3033#N/A1011
16#N/A3033#N/A1011
17#N/A3033#N/A1011
18#N/A3033111111
19
20
21AAAAAA#N/AC0CC
22#N/AA0AA#N/AC0CC
23#N/AA0AA#N/AC0CC
24#N/AA0AA#N/AC0CC
25#N/AA0AA#N/AC0CC
26BBBBBB#N/AC0CC
27#N/AB0BBCCCCCC
28#N/AB0BB#N/AB0BB
29#N/AB0BB#N/AB0BB
30CCCCCC#N/AB0BB
31#N/AC0CCBBBBBB
32#N/AC0CC#N/AA0AA
33#N/AC0CC#N/AA0AA
34#N/AC0CC#N/AA0AA
35#N/AC0CC#N/AA0AA
36#N/AC0CCAAAAAA
Sheet3
Cell Formulas
RangeFormula
B3:B18,H21:H36,E21:E36,B21:B36,H3:H18,E3:E18B3=FILLDOWN(A3:A18)
L3:L18,R21:R36,O21:O36,L21:L36,R3:R18,O3:O18L3=FILLUP(K3:K18)
A4:A7,A31:A36,A27:A29,A22:A25,A13:A18,A9:A11A4=NA()
Dynamic array formulas.


FILLDOWN
Excel Formula:
=LAMBDA(range,
    LET(
        r,IF((range=0)+(range="")+ISERROR(range),NA(),range),
        SCAN(0,r,LAMBDA(s,c,IF(ISERROR(c),s,c)))
    )
)

FILLUP
Excel Formula:
=LAMBDA(range,
    REVTABLE(FILLDOWN(REVTABLE(range)))
)

REVTABLE
Excel Formula:
=LAMBDA(table,
    LET(
        d,table,
        r,ROWS(d),
        INDEX(d,SEQUENCE(r,,r,-1),SEQUENCE(,COLUMNS(d)))
    )
)
 
Upvote 0
Just had a thought. These could be combined to 1 `FILL` formula.

Excel Formula:
=LAMBDA(range,[up],
    IF(up=0,FILLDOWN(range),FILLUP(range))
)
 
Thanks for sharing your Lambda functions on the board.

I edited this thread's title as it is supposed to contain a single function name. Please feel free to post alternative or additional Lambda functions separately.
For this particular one, it is also a good idea to combine multiple functions as you suggested.
 

Forum statistics

Threads
1,216,503
Messages
6,131,022
Members
449,616
Latest member
PsychoCube

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top