# 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. ## 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. ### 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 You will find the value of the last cell of Row 6, in your selected cell. ### 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 You will find the value of the last cell of Row 5, in your selected cell. ### 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 You will find the number of the last non-empty cell of Row 10, in your selected cell. ### 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 After pressing ENTER, You will find the value of the last cell of the last row of the dataset, in your selected 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 You will find the value of the last cell of Row 8, in your selected cell. ## Conclusion

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