Find the Last Cell With Value in Row in Excel (6 Methods)

If you have a very large dataset, it can be quite time consuming to find the last cell with value in a row in your Excel dataset. In this article I’ll introduce you with 6 methods by which you can easily find the last cell with value in a row.

Consider the following dataset. Here Information about different customers who have applied for a bank loan is given. Now we will find the last cell with data in a row using this dataset.

Find the last cell dataset

Download Practice Workbook

6 Methods to find the Last Cell with Value in Row in Excel

1. Find the Last Cell with Value Using Keyboard

The easiest way to find the last cell with value in a row is using keyboard command. Click on the first cell of the row and press CTRL+ Right Arrow Key. Your cursor will move to the last non-empty cell in that row.

Find the last cell with value in row

Read more: How to Find Last Cell with Value in Column in Excel

2.   Using The OFFSET Function

If you know the number of columns and rows of your dataset, you can find the last cell value in any row by using the OFFSET function. To find out the last cell value in Row 6, type the formula in an empty cell,

=OFFSET(A4,2,7,1,1)

Here, A4 = First cell of your dataset

2 =  Number of the row of your dataset excluding the first row

7 =  Number of the column of your dataset excluding the first column

1 = cell height

1 = cell width

find the last cell with OFFSET function

You will find the value of the last cell of Row 6, in your selected cell.

Find the last cell with value in row

3.   Find the Last Cell Value Using INDEX Function

Using the INDEX function along with the COUNTA function allows you to find the last cell value of any row. To find the last cell value in Row 5, type the following formula in an empty cell,

=INDEX(5:5,COUNTA(5:5))

Here, 5:5= Row 5

Find the last cell using INDEX function

You will find the value of the last cell of Row 5, in your selected cell.

Find the last cell with value


Similar Readings:


4.   Find the Number of The Last Cell Using MATCH Function

By using the MATCH function you can find the number of the last cell which has value in any particular row. To find the number of the last non-empty cell (last entry) in Row 10 type the following formula in any of the empty cells,

=MATCH(MAX(10:10),10:10,0)

Here, 10:10= Row 10

           0 = Exact match

Find the last cell with value using MATCH function

You will find the number of the last non-empty cell of Row 10, in your selected cell.

Last cell in Row

5. Last Cell Value in Last Row Using LOOKUP Function

You can find the last cell value of the last row by using the LOOKUP function. Type the formula in an empty cell,

=LOOKUP(2,1/(H:H<>""),H:H)

Here, H:H= Last column of the dataset

Last cell with lookup

After pressing ENTER, You will find the value of the last cell of the last row of the dataset, in your selected cell.

Last cell

6. Find The Last Cell Value in any Row Using HLOOKUP Function

Using the HLOOKUP Function is another way to find the value of the last cell of any row. Now we will find the last cell of Row 8 in our dataset. To find the value, type the formula in an empty cell,

=HLOOKUP(H4,A4:H12,5,FALSE)

Here, H4= Last column of the first row (Reference cell)

         A4:H12 = Range of the Dataset

         5 = 5th Row of our dataset including reference cell’s row

         FALSE = Exact Match

HLOOKUP

You will find the value of the last cell of Row 8, in your selected cell.

LAST CELL USING HLOOKUP

Conclusion

You can find the last cell by using any of the described methods. If you face any confusion, please leave a comment.


Further Readings

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo