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.

How to Find Last Non Blank Cell in Row in Excel


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.

How to Find Last Non Blank Cell in Row in Excel

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.

How to Find Last Non Blank Cell in Row in Excel

  • Press ENTER.

  • To AutoFill, use Fill Handle.

How to Find Last Non Blank Cell in Row in Excel

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.

How to Find Last Non Blank Cell in Row in Excel

  • AutoFill the rests.

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


Similar Readings


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.

How to Find Last Non Blank Cell in Row in Excel

  • Press ENTER.

  • Then, AutoFill the next.

How to Find Last Non Blank Cell in Row in Excel

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.

How to Find Last Non Blank Cell in Row in Excel

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

How to Find Last Non Blank Cell in Row in Excel


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

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.

4 Comments
  1. 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. 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.

Leave a reply

ExcelDemy
Logo