It is not a matter of astonishment that we might face the requirement to **find last non blank cell in row in Excel**. Here, we will try to sort out some smooth ways to **find last non blank cell in Excel**.

For more simplification, I am going to use a **Dataset **containing **Employee Name **and **Working Hours**.

**Table of Contents**hide

## Download Practice Workbook

## 5 Ways to Find Last Non Blank Cell in Row in Excel

**1. Applying Excel LOOKUP Function to Find Last Non Blank Cell in Row**

**Lookup Function** is an easy way to **find last non blank cell in row in Excel**.

**Steps**:

- Select a cell to apply the
**LOOKUP Function**. Here, I have selected the**H6**cell. - Apply the formula.

`=LOOKUP(2,1/(B6:G6<>""),B6:G6)`

Here, the **LOOKUP Function **searches through the given **Range B6:G6** according to the **lookup_value** and **returns **the **last non blank cell value**.

- Press
**ENTER**.

- Then, use
**Fill Handle**to**AutoFill**the rests.

**Read More:** **Find the Last Cell With Value in Row in Excel (6 Methods)**

**2. Combination of INDEX & COUNTA Functions to Find Last Non Blank Cell in Row**

There is another easy way to **find last non blank cell in a row in Excel **using a formula that contains both **INDEX Function**** & ****COUNTA Function**.

**Steps**:

- Choose a cell to apply the formula. Here, I have selected cell
**H6**. - Input the Formula.

`=INDEX(B6:G6,COUNTA(B6:G6))`

** **Where, **COUNTA Function **counts the **non blank cell** values according to the** range** and **INDEX Function **returns the value given the **range**.

- Press
**ENTER**.

- To
**AutoFill**, use**Fill Handle**.

**Read More:** **How to Use Excel Formula to Find Last Row Number with Data (2 Ways)**

**3. Using OFFSET Function to Find Last Non Blank Cell in Row**

**OFFSET Function** is vastly used to **find last non blank cell in row**.

**Steps**:

- Pick a cell to apply the method. Here, I have picked cell
**H6**. - Apply the formula.

`=OFFSET(B4,2,5,1,1)`

Here,

**B4**is the**starting point**which works as**2**is the**number of rows**to ignore from the**reference cell.****5**is the**number of columns**to ignore to the right from the**reference cell.****1**is the height in rows of the output cell.**1**is the height in columns of the output cell.

- Hit
**ENTER**.

**AutoFill**the rests.

**Read More:** **Excel Find Last Occurrence of Character in String (6 Methods)**

**Similar Readings**

**Excel Function: FIND vs SEARCH (A Comparative Analysis)****How to Find Max Value in Range with Excel Formula (5 Easy Methods)****How to Use Formula to Find Bold Text in Excel****[Solved!] CTRL+F Not Working in Excel (5 Fixes)****[Fixed]: Can’t Find Project or Library Error in Excel (3 Solutions)**

**4. Employing SUMPRODUCT Function to Find Last Non Blank Cell in Row**

Application of **SUMPRODUCT Function** along with **INDIRECT Function**, **ROW function** and **MAX function** can easily **find the last non blank cell in row**.

**Steps **:

- Select a cell. Here, I have selected cell
**H6**. - Employ the formula on that cell.

`=INDIRECT("G"&SUMPRODUCT(MAX((B6:G6<>"")*(ROW(B6:G6)))))`

Where **SUMPRODUCT **is used to find out the **non blank cell** total number in the selected range. **INDEX Function **here returns the value based on the location. Finally, We can have **last non blank cell in row** by employing the **INDIRECT Function**.

** ****Formula Breakdown**

**ROW(B6:G6)—->**The**ROW**function returns the value of the**row number**.**Output:**6

**MAX((B6:G6<>””)*(ROW(B6:G6))—->**Here. the**ROW**&**MAX**is multiplied by each other.**MAX(True,True,True,True,True,True*6)—->**Here, the boolean value of**TRUE**which is**1**is multiplied by**6**.**MAX(6,6,6,6,6,6)—->**The**MAX**function returns the**largest number**.**Output:**6

**SUMPRODUCT(MAX((B6:G6<>””)*(ROW(B6:G6))))****SUMPRODUCT(6)****Output:**6

**INDIRECT(“G”&SUMPRODUCT(MAX((B6:G6<>””)*(ROW(B6:G6)))))****INDIRECT(“G”& 6)—->**The**Ampersand**joins the texts.**INDIRECT(“G6”)—->**The**INDIRECT**function returns the value of reference cell with text string.**Output:**8.5**Explanation:**Here,**ROW Function**returns the**row number**and**MAX Function**returns the maximum value ignoring the blank cells. Multiplying them with**SUMPRODUCT Function**, we have got the maximum number with non blank cell. Connecting it with**G**through**Ampersand**, we have now declared the**last non blank cell of the row**.**INDIRECT Function**returns the value of that cell.

- Press
**ENTER**.

- Then,
**AutoFill**the next.

**Read More:** **How to Find Last Row with a Specific Value in Excel (6 Methods)**

**5. Applying XLOOKUP Function to Find Last Non Blank Cell in Row**

The **XLOOKUP** Function is an advanced function in Excel. We can use it here to find **last non blank cell in row**.

**Steps**:

- Select a cell to apply the formula. Here, I have selected cell
**H6**. - Apply the formula.

`=XLOOKUP(FALSE,ISBLANK(C6:G6),C6:G6,"Blanks",,-1)`

Here, I have selected the **array C6:D6. ISBLANK Function **works to find out if there is any **blank cell**. On that case, it will return **True**. Thus, the blank cells will be ignored. I have chosen the search option from last to first by giving **-1 **value. Finally, **XLOOKUP Function finds last non blank cell in row** considering all these values.

- Hit
**ENTER**.

- Use
**Fill Handle**to**AutoFill**the rests.

**Read More:** **Find Last Value in Column Greater than Zero in Excel (2 Easy Formulas)**

**Practice Section**

For further expertise, you can practice here.

**Conclusion**

Every method shown here is practically proven **to find last non blank cell in row in Excel**. For any kind of confusion, you can comment below.

**Related Articles**

**How to Find Multiple Values in Excel (8 Quick Methods)****How to Find from Right in Excel (6 Methods)****Find First Occurrence of a Value in a Range in Excel (3 Ways)****How to Find Character in String Excel (8 Easy Ways)****How to Find Lowest 3 Values in Excel (5 Easy Methods)****Find External Links in Excel (6 Quick Methods)**

Hi, how do you find the second last non blank value?

Sorry DIEGO for my late response.

There needs to make some changes in the formula in case of finding the second last non blank cell.

I have used the following formula using LOOKUP function in cells D5 to D15 that is the chemistry marks in the dataset to find the second last non-blank cell.

=LOOKUP(2,1/((D5:D15<>D15)*(D5:D15<>“”)),D5:D15)

My dataset is given below:

Second Last Non Blank Value

Name Physics Chemistry

Green 164 110 (D5)

Jack 185 165

Joey 178 132

Mark 183 137

Austin 165 112

Marvin 173 119

Mason 186 170

Mount 170

Martin 177 160

Freeman 164

Federer 163 111 (D15)

Second Last Non Blank Value 160

For me it worked perfectly. I hope it will work the same way for you too.

Hi, Thank you so much worked perfectly! Can you explain the vlookup arguments. Why is the first argument a “2”?

Hello MARIA, thanks for the comment and sorry for my late reply.

In the LOOKUP function, the first argument is Lookup_value. I have typed 2 just to signify the NUMBER format. You can type any number. It will work just fine.