# How to Find Last Non Blank Cell in Row in Excel (5 Methods)

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. ## 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.  ### 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. ### 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. ### 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. ### 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. ## 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 #### Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

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

• Reply Naimul Hasan Arif Jul 19, 2022 at 1:12 PM

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.

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

3. Reply Naimul Hasan Arif Jul 19, 2022 at 3:59 PM

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.  5 Excel Hacks You Never Knew  