MS365: excel formula for last used row number. The LUR() function

The LUR (Last Used Row) function is a LAMBDA function, so no macros involved. It works on MS365 / Excel Online. Purpose is simple – return the range in a column from the cell given, e.g. A1 to the last used row.

LUR syntax

The syntax is LUR(TableStart,CheckRows)

TableStart is the cell reference for the start of the array to be returned.
CheckRows is the last row to be checked for used rows in the column referenced in TableStart

The formula

=LUR(A2,100000)

… will return the array from A2 to the last used row in column A. If the last used row is 11, it will return this array:

Combined with XLOOKUP

You can combine LUR with XLOOKUP this way:

=XLOOKUP(LUR(A2,100000);SomeSheet!$A:$A;SomeSheet!$C:$C;"Not found";0)

LUR will check the first 100000 rows and return the last used row in the range from A2 in column A, and spill down to this last row.

XLOOKUP with LUR is 3x faster

If you use XLOOKUP with an array for the lookup_value like this

=XLOOKUP(A2:A1000);SomeSheet!$A:$A;SomeSheet!$C:$C;"Not found";0)

… it works like a charm – but because the formula spills down to A1000, it is significally slower if your last used row is 500.

Why the CheckRows parameter?

The CheckRows parameter limits the calculation to find the last used row, and reduces calculation time significantly. If you set the CheckRows to 1048576, you can see how the function would work without this parameter 🙂

Using LUR in your Workbook

Just copy cell I2 in the sample workbook and paste it in your own workbook. Then you can check Formulas-> Name Manager:

Fun fact

LUR meaning in Norwegian is smart 🙂

The LUR formula

= LAMBDA(
   TableStart,
   CheckRows,
   LET(
     FirstColumn,
     TEXTBEFORE(TEXTAFTER(CELL("address",TableStart),"$"),"$"),
     FirstRow,
     CELL("row",TableStart),
     CheckRows1,
     CheckRows,
     Rng,
     FirstColumn&"1:"&FirstColumn&CheckRows1,
     INDIRECT(FirstColumn
              & FirstRow
              & ":"
              & FirstColumn
              & MAX(ROW(INDIRECT(Rng))*NOT(ISBLANK(INDIRECT(Rng)))))))

Download

Download link: MS365: excel formula for last used row number. The LUR() function (128 downloads )

You are welcome to send questions, critique or kudos to ketil@excelguru.no