Formula to find last blank cell in a range

Question

I need to create a formula that returns the row number of the last empty cell within a range. For example

Cell   Data
B10    text-a
B11    text-b
B12    text-c
B13    
B14
B15    text-d
B16 
B17    text-e

In the range B10:B17 I need the formula to return the value 16, which is the last row with no data entered.

I have tried various permutations of INDEX/COUNTBLANK/LOOKUP but no success.

Any ideas?

Possible duplicate of [Last non-empty cell in a column](https://stackoverflow.com/questions/5441885/last-non-empty-cell-in-a-column) — Our Man in Bananas, Jul 18 '18 at 10:51
Welcome to StackOverflow - Please read [how to ask](https://stackoverflow.com/help/how-to-ask) — Our Man in Bananas, Jul 18 '18 at 10:53
you might find some help [exceltp: Get the Row number of the last non blank cell in a column in Microsoft Excel](http://www.exceltip.com/other-qa-formulas/get-the-row-number-of-the-last-non-blank-cell-in-a-column-in-microsoft-excel.html) — Our Man in Bananas, Jul 18 '18 at 10:55
also, take a look at [ExcelJet: Excel formula - Last row number in range](https://exceljet.net/formula/last-row-number-in-range) — Our Man in Bananas, Jul 18 '18 at 10:57
and [Referencing the Last Cell in a Column](https://excel.tips.net/T002103_Referencing_the_Last_Cell_in_a_Column.html) might be more what you are looking for — Our Man in Bananas, Jul 18 '18 at 11:06
As @OurManinBananas said - use the accepted answer in [last-non-empty-cell-in-a-column](https://stackoverflow.com/questions/5441885/last-non-empty-cell-in-a-column) but change `<>` to `=` and update the column references to yours `A:A` to `B10:B17`, but remove 9 from the ROW argument to avoid a `#REF!` error...... `{=INDEX(B10:B17,MAX((B10:B17="")*(ROW(B10:B17)-9)))}` — Darren Bartrup-Cook, Jul 18 '18 at 11:08

Ron Rosenfeld · Accepted Answer · 2018-07-18T19:39:15.230

4

=LOOKUP(2,1/(LEN(B10:B17)=0),ROW(B10:B17))

Enter normally

How it works:

Len(rng)=0 returns an array of {TRUE;FALSE;...} depending on whether the cell appears empty or not
1/(…) converts that to an array of {1; #DIV/0!;…}
Therefore, the last 1 in the array will be returned at the position of the last blank cell.
LOOKUP(2,...): The 2 is guaranteed to be larger than any element in the array
##If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value., and in an unsorted array, it will be the last value that meets this criteria.
We then use the vector form of the function to match the returned value against the row number.

It might be helpful to use the formula evaluation tool to see how this goes.

edited Jul 18 '18 at 19:39

answered Jul 18 '18 at 12:14

Ron Rosenfeld

That works perfectly. Thank you! I'm not sure how it works though. What does the first parameter, "2" do? And what is the "1/" in the second parameter? – skyatis Jul 18 '18 at 19:30
@skyatis Explanation added to answer – Ron Rosenfeld Jul 18 '18 at 19:39
Wow that's clever! – skyatis Jul 18 '18 at 19:59

score 3 · Answer 2 · answered Jul 18 '18 at 11:12

Scratch my comment saying to use the array formula:
=INDEX(B10:B17,MAX((B10:B17="")*(ROW(B10:B17)-9)))

That will return the value in cell B16, but you're asking for the row number.

Use the array formula:
=MAX((B10:B17="")*(ROW(B10:B17)))

It's the same as the INDEX but doesn't need to return the value from the cell.

2 Answers2