CODEWITHSUNDEEP
excelexcel-formula
skyatis
skyatis

Reputation: 23

Formula to find last blank cell in a range

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?

Upvotes: 2

Views: 6428

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60464

=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.

enter image description here

Upvotes: 5

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19847

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.

Upvotes: 3

Related Questions