Reversing a list in Excel within a formula

Question

So there are a bunch of ways to reverse a list to turn it into a new list, using helper columns. I've written some code that does use helper columns to reverse a list and then use it.

I'm wondering how I would reverse a list without using a helper column for use in a sumproduct - for example,

=sumproduct(Reverse(A1:A3),B1:B3)

Answer 1

In Excel 365, the best ways are as follows:

The best method for readability is

=SORTBY(A1:A3, ROW(A1:A3), -1)
=SORTBY(A1:C1, COLUMN(A1:C1), -1)

The best method for calculation speed is

=INDEX(A1:A3,SEQUENCE(ROWS(A1:A3),,ROWS(A1:A3),-1))
=INDEX(A1:C1,SEQUENCE(,COLUMNS(A1:C1),COLUMNS(A1:C1),-1))

Sorting is an expensive and slow operation. We already know the order we want the output to appear in, so it is much faster to directly tell Excel how to order the items with a combination of INDEX and SEQUENCE.

The best method for advanced users who like a modern, dynamic, and computationally efficient solution is

=LAMBDA(list,
  LET(
    rcount,ROWS(list),
    ccount,COLUMNS(list),
    INDEX(
      list,
      SEQUENCE(rcount,,rcount,-1),
      SEQUENCE(,ccount,ccount,-1)
    )
  )
)(A1:A3)

You can add this LAMBDA function to the Name Manager to make a friendly and fully dynamic REVERSE function without using any VBA. After naming the LAMBDA, you can use it in formulas as follows

=SUMPRODUCT(REVERSE(A1:A3),B1:B3)

Additional Resources:

Microsoft: adding a LAMBDA to the Name Manager

ExcelJet: more step-by-step tutorial for naming a LAMBDA

Answer 2

=INDIRECT(ADDRESS(ROW()+COUNTA(A1:$A$3)+1-ROW(A1),1))

Answer 3

With Dynamic Arrays

This is the best solution I have found.

=SORTBY(list,SEQUENCE(ROWS(list),1,ROWS(list),-1))

https://exceljet.net/formula/reverse-a-list-or-range

Answer 4

Found an easy solution that works in the latest Excel versions:

=SORTBY(*rowarray*, column(*rowarray*),-1)

=SORTBY(*columnarray*, row(*columnarray*),-1)

Answer 5

Actually you can make the formula in your Question work (with a small UDF()):

Pick a cell and enter:

=SUMPRODUCT(reverse(A1:A3),B1:B3)

with this in a standard module:

Public Function reverse(rng As Range)
    Dim ary(), N As Long, i As Long
    N = rng.Count
    ReDim ary(1 To N)
    i = N

    For Each r In rng
        ary(i) = r.Value
        i = i - 1
    Next r

    With Application.WorksheetFunction
        reverse = .Transpose(ary)
    End With
End Function

Answer 6

This array formula will reverse the order of a vertical array:

= INDEX(B18:B21,N(IF({1},MAX(ROW(B18:B21))-ROW(B18:B21)+1)))

Also, this reverses a horizontal array:

= INDEX(A1:D1,N(IF({1},MAX(COLUMN(A1:D1))-COLUMN(A1:D1)+1)))

---

EDIT

More generally, to vertically flip a matrix instead of just an array (which is just a one-dimensional matrix), use this array formula: (e.g. for range A1:D2)

= INDEX(A1:D2,N(IF({1},MAX(ROW(A1:D2))-ROW(A1:D2)+1)),
              N(IF({1},COLUMN(A1:D2)-MIN(COLUMN(A1:D2))+1)))

And to horizontally flip a matrix, use this:

= INDEX(A1:D2,N(IF({1},ROW(A1:D2)-MIN(ROW(A1:D2))+1)),
              N(IF({1},MAX(COLUMN(A1:D2))-COLUMN(A1:D2)+1)))

And a bonus... to flip a matrix horizontally and vertically in one shot (i.e. rotate it 180 degrees):

= INDEX(A1:D2,N(IF({1},MAX(ROW(A1:D2))-ROW(A1:D2)+1)),
              N(IF({1},MAX(COLUMN(A1:D2))-COLUMN(A1:D2)+1)))

Actually this last one here could more generally be used to flip either a horizontal or vertical array.

Answer 7

For what it's worth, here's another completely different method to reverse an array. (I'm posting this as a separate answer just because it is apples and oranges to the other answer I already provided.)

Instead of reversing the order of the array by reversing the indexing, it is also possible to use matrix multiplication (MMULT) to accomplish this.

If your data in A1:A3 is {1;3;5} (for example) then the following matrix multiplication effectively reverses this array:

[0 0 1]   [1]   [5]
[0 1 0] * [3] = [3]
[1 0 0]   [5]   [1]

In order to generate that matrix of 1's and 0's above, you can do this (line break added for readability):

= (ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:A3)))=
  (COLUMN(INDEX(1:1,ROWS(A1:A3)))-COLUMN(INDEX(1:1,1):INDEX(1:1,ROWS(A1:A3)))+1))+0

So in the end, the formula to reverse this array would be:

= MMULT((ROW(INDEX(A:A,1):INDEX(A:A,ROWS(A1:A3)))=
  (COLUMN(INDEX(1:1,ROWS(A1:A3)))-COLUMN(INDEX(1:1,1):INDEX(1:1,ROWS(A1:A3)))+1))+0,A1:A3)

This same line of thinking can be used to reverse a horizontal array. For example if A1:C1 is {1,3,5}, then:

          [0 0 1]
[1 3 5] * [0 1 0] = [5 3 1]
          [1 0 0]

Note how the matrix of 1's and 0's is the second argument this time instead of the first argument.

Using the same general line of reasoning, you can get to this formula to reverse a horizontal array.

= MMULT(A1:C1,(ROW(INDEX(A:A,1):INDEX(A:A,COLUMNS(A1:C1)))=
  (COLUMN(INDEX(1:1,COLUMNS(A1:C1)))-COLUMN(INDEX(1:1,1):INDEX(1:1,COLUMNS(A1:C1)))+1))+0)

This method has two major disadvantages as compared two the N(IF(...)) solution, namely:

1. It's way longer.

2. It only works for numbers since MMULT requires numbers, but the other method works if the cells contain anything (e.g. text).

I was using this solution to reverse arrays without helper columns until just recently when I learned about the N(IF(...)) alternative.

Answer 8

This will do what you are asking:

=SUMPRODUCT(INDEX(A:A,N(IF(1,{3;2;1}))),B1:B3)

To make a little more dynamic you can use this array formula:

=SUM(INDEX(A:A,N(IF(1,LARGE(ROW(A1:A3),ROW(A1:A3)))))*B1:B3)

Being an array formula, it needs to be confirmed with Ctrl-Shift-Enter, instead of Enter when exiting Edit mode.