This post will guide you how to find the first non-blank cell in a row or column or a range in Excel. How do I get the value of the last non-blank cell in a row or column using formula in Excel.

Table of Contents

## 1.Get First Non-blank Cell Value

Assuming that you have a list of data in range A1:A5, and you want to find the first non-blank cell in a given range of cells in Excel. How to do it. You can use a formula based on the INDEX function and the MATCH function to get the first non-blank value in a one-column range in Excel. Here is the formula you can use:

`=INDEX(A1:A5,MATCH(TRUE,INDEX((A1:A5<>0),0),0))`

Type this formula into a blank cell and press Enter key on your keyboard. You would get the first non-blank cell value from your given range A1:A5.

Or you can use an array formula based on the INDEX function, the MATCH function and the ISBLANK function to achieve the same result of extracting the first non-blank cell value from a given range in Excel. Like this:

`=INDEX(A1:A5,MATCH(FALSE,ISBLANK(A1:A5),0))`

Type this formula into a blank cell and press **Ctrl + Shift + Enter** keys on your keyboard to make your formula as array formula.

## 2. Get Last Non-blank Cell Value

If you want to find the value of the last non-blank cell in a row or column in Excel, you can use a formula based on the LOOKUP function to achieve it. Here is the formula we are using is:

**=LOOKUP(2,1/(A1:A5<>""),A1:A5)**

Type this formula into a blank cell and press Enter key on your keyboard. You would get the last non-blank cell value from your given range A1:A5.

## 3. Video: Find the First or Last Non-blank Cell in a Row or Column

This Excel video tutorial, we’re going to show you how to find the first and last non-blank cells in a row or column using formula.

## 4. Related Functions

- Excel LOOKUP function
The Excel LOOKUP function will search a value in a vector or array.The syntax of the LOOKUP function is as below:= LOOKUP (lookup_value, lookup_vector, [result_vector])…
- Excel INDEX function

The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])… - Excel MATCH function

The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])…. - Excel ISBLANK function

The Excel ISBLANK function returns TRUE if the value is blank or null.The syntax of the ISBLANK function is as below:= ISBLANK (value)…

## Leave a Reply

You must be logged in to post a comment.