Find the last non-empty cell in a row
Searching for the last non-empty cell in Microsoft excel is such a simple task to accomplish with the help of LOOKUP function.
Let see how that can be achieve in the data set below
This can be fix with the LOOKUP using this formula
=LOOKUP (2, 1/ (3:3<>””), 3:3)
How the Formula Works
- 2 is the lookup_value
- The expression 3:3<>”” is a range in row 3 that returns an array of TRUE or FALSE values.
- The number 1 will then in turn divided by this array in (b) above to get another new array that made up of either 1’s (1/1) or Divide by zero errors (#DIV/0!) Which will now form our new lookup _vector.
- The lookup function will search for lookup_value and in case it cannot be found, the next smallest value position will be automatically matched. That shows lookup will match the last 1 in the array since 1 is the largest value in the lookup_vector and next smallest value to 2.
- The corresponding value at that same position will be returned by LOOKUP.
Hope this post is helpful?
For more Insight on Microsoft Excel, kindly buy any of my books “Productivity Boosting Aspects of Microsoft Excel” and “PivotTable Recipe” on Kindle Amazon. You can as well subscribe to our newsletter here