Google Sheets to Excel

Question

I have this array formula.

=ArrayFormula(IF(P3:P="",,IFERROR(VLOOKUP(P3:P,sort({{C:C,L:L,A:A};{D:D,M:M,A:A}},3,0),2,0),UPLUS(V3:V))))

It works fine in google sheets. But when I converted my google sheets to excel it said sort formula is invalid. And I notice arrays work a bit different in excel. So how can I convert this formula from google sheets to excel without losing function.

Answer 1

The error is in the sort function. Before we start, let's see an example of the Google Sheets sort function which you have used:

Let's see we have a grid from A1 to C3 as below:

          -------------------------------
          |    A    |    B    |    C    |
-----------------------------------------
|    1    |    3    |    11   |    61   |
-----------------------------------------
|    2    |    7    |    13   |    21   |
-----------------------------------------
|    3    |    5    |    12   |   141   |
-----------------------------------------

Then if you issue this formula in E7:

=SORT(A1:C3, 2, 0)

This will create a sorted grid out of A1:C3 with it's topmost and leftmost cell located in E7 and sorted according to the second column (column B or column 2). The third argument calls for ascending (1) or descending (0) order of column 2 or column B. The result is:

          -------------------------------
          |    E    |    F    |    G    |
-----------------------------------------
|    7    |    7    |    13   |    21   |
-----------------------------------------
|    8    |    5    |    12   |   141   |
-----------------------------------------
|    9    |    3    |    11   |    61   |
-----------------------------------------

Your range of values is piecewise (not continuous) and {{C:C,L:L,A:A};{D:D,M:M,A:A}} is the input which looks like this when pasted in let's suppose Q14:

          -------------------------------------------------
          |       Q       |       R       |       S       |
-----------------------------------------------------------
|    14   |entire C column|entire L column|entire A column|
-----------------------------------------------------------
|14+Len(C)|entire D column|entire M column|entire A column|
-----------------------------------------------------------

where Len(C) is length of column C. Note that all the columns must have the same length (number of rows) in order to use the SORT function. Now here you are issuing the following formula:

sort({{C:C,L:L,A:A};{D:D,M:M,A:A}},3,0)

As you must have guessed, column 3 is now column S which is basically the merger of column A twice. 0 suggests it is sorted in descending order.

You need to mimic this functionality in Excel since Excel does not have the same SORT function.

If you see this page, it explains how to sort an array using the INDEX, MATCH and ROWS functions to perform the same operation in MS Excel, HOWEVER, the Excel arrays do not work the same way as Google Sheet arrays (the latter being much simpler with the sort function). Just look at the huge sorting formula for Excel on the link. Unfortunately there is no simpler method in Excel. Technically, when sorting using formulas in Excel, you are writing the sort algorithm yourself.

I daresay that in this scenario, you will have to isolate the sorting from the main formula. As in, first perform sorting for dynamic data (sorting using formula), and then apply the VLOOKUP and other operations on it.