Author: Oscar Cronquist Article last updated on November 29, 2018

The image above demonstrates a formula in cell D3 that rearranges values, bottom value is now on top etc.

Formula in C2:

=INDEX($B$3:$B$14, LARGE(IF($B$3:$B$14<>"", MATCH(ROW($B$3:$B$14), ROW($B$3:$B$14)), ""), ROWS($D$1:D1)))

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Explaining formula in cell D3

Step 1 - Find non-empty cells

$B$3:$B$14<>""

becomes

{"AA"; ""; "BB"; "CC"; ""; "BB"; "DD"; ""; "FF"; "GG"; ""; "HH"}<>""

and returns

{TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE}

Step 2 - Replace TRUE with corresponding row value

The IF function returns the row number if cell is not blank. FALSE returns "" (nothing).

IF($B$3:$B$14<>"", MATCH(ROW($B$3:$B$14), ROW($B$3:$B$14)), "")

becomes

IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE}, MATCH(ROW($B$3:$B$14), ROW($B$3:$B$14)), "")

becomes

IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE}, {1;2;3;4;5;6;7;8;9;10;11;12}, "")

and returns

{1;"";3;4;"";6;7;"";9;10;"";12}

Step 3 - Find k-th largest row number

The LARGE function extracts the k-th smallest number from cell range or array. LARGE(array. k) The second argument contains ROWS($D$1:D1), it has an expanding cell reference that grows when cell is copied to cells below.

LARGE(IF($B$3:$B$14<>"", MATCH(ROW($B$3:$B$14), ROW($B$3:$B$14)), ""), ROWS($D$1:D1))

becomes

LARGE({1;"";3;4;"";6;7;"";9;10;"";12}, ROWS($D$1:D1))

becomes

LARGE({1;"";3;4;"";6;7;"";9;10;"";12}, 1)

and returns 12.

Step 4 - Return value

The INDEX function returns a value based on a row and column number. In this formula the cell range is only in one column, the row number is only needed.

INDEX($B$3:$B$14, LARGE(IF($B$3:$B$14<>"", MATCH(ROW($B$3:$B$14), ROW($B$3:$B$14)), ""), ROWS($D$1:D1)))

becomes

INDEX($B$3:$B$14, 12)

and returns "HH" in cell D3.

Download Excel *.xlsx file

Invert a list ignoring blanks.xlsx