# How to Find Last Cell with Value in Column in Excel (3 Ways)

We have a simple dataset where we’ll find the last cell with a value in a column.

## Method 1 – Inserting the LOOKUP Function to Find Last Cell with Value in Column in Excel

### Case 1.1 – Using the Basic LOOKUP Function Only

We will check the column C.

Steps:

• Go to Cell D5.
• Insert the following formula:
`=LOOKUP(2,1/(C:C<>""),C:C)`
• Hit Enter.
• We get the last value of Column C.

Note:

• C:C<>”” – Checks the whole Column C for empty cells and returns TRUE/FALSE for each cell of that range.
• 1/1 will be divided with the value from the previous step, which may be TRUE or FALSE. For FALSE (i.e. a nonblank cell), the formula returns an error, #DIV/0! because we can’t divide any number by zero.
• 2 – The LOOKUP function attempts to locate 2 in the list of values produced in the last step. Since it can’t locate the number 2, it looks for the next maximum value, which is 1. It searches this value starting from the end of the list and proceeding to the start of this list. The process will end when it gets the first result. This will be the last cell in the range that contains a value.
• C:C – This is the last statement of the LOOKUP function, which is the range of cells that the function will fetch the corresponding value from.

### Case 1.2 – Combining LOOKUP, NOT, and ISBLANK Functions to Find the Last Cell with a Value in Column

Steps:

• In the 10th row, we added an error by dividing a number by 0.

• Add the NOT and ISBLANK functions in the formula. The formula becomes:
`=LOOKUP(2,1/(NOT(ISBLANK(C:C))),C:C)`
• Hit Enter.
• We can see that in the result section, an error value is showing. Usually, the LOOKUP function avoids this error value.

### Case 1.3 – Applying LOOKUP and ISNUMBER Functions to Find the Last Cell with a Numeric Value in a Column

Steps:

• Add textual data in the 10th row.

• Modify the original formula and add ISNUMBER so it becomes:
`=LOOKUP(2,1/(ISNUMBER(C:C)),C:C)`
• Press Enter to get a return value.

### Case 1.4 – Using LOOKUP with the ROW Function to Find the Row Where the Last Value Exists

Steps:

• Modify the formula to the following:
`=LOOKUP(2,1/((C:C)),ROW(C:C))`
• Hit Enter.

Read More: How to Find Last Row with a Specific Value in Excel

## Method 2 – Finding the Last Cell with a Numeric Value in a Column Using Excel INDEX and COUNT Functions

Steps:

• Remove the blank cell and add textual values in the range. Add a blank cell as the last.
• Insert the following formula in the result cell:
`=INDEX(C5:C10,COUNT(C5:C10))`
• Hit Enter.

• We get only numeric values as we used the COUNT function.
• To get any results, use the following:
`=INDEX(C5:C10,COUNTA(C5:C10))`

## Method 3 – Applying the OFFSET Function to Find the Last Cell with a Value in a Column in Excel

### Case 3.1 – Using the Basic OFFSET Function

Steps:

• Make sure there are no empty cells in the dataset.
• Write OFFSET and select Cell C5 as reference. The next two arguments are the number of rows and columns, respectively:
`=OFFSET(C5,4,0)`
• Hit Enter.

### Case 3.2 – Using the OFFSET and COUNT Functions to Find the Last Cell with a Value in a Column

Steps:

• Go to Cell D5.
• Insert the following function.
`=OFFSET(C5,COUNT(C5:C10)-1,0)`
• Hit Enter.

• To get any values (not just numeric ones), use the following formula:
`=OFFSET(C5,COUNTA(C5:C10)-1,0)`

This formula doesn’t work if there are blank values inside the dataset.

<< Go Back To Excel Last Value in Range | Excel Find Value in Range | Excel Range | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

1. Thanks for the info, will be following up with structured lessons

2. Excellent. Useful formulas. Well explained in a very easy way.

• Hello, Mubashir!

Thanks for your appreciation. To get more helpful content stay in touch with ExcelDemy.

Regards
ExcelDemy

3. Thank You!
I’ve been looking for a solution to referencing the last non empty cell in a sheet to another cell within the same sheet.
Took me a while and a lot of searching to find your solution.
But, it works great!
Thanks again!

4. I am using your solution 1.4 Using LOOKUP with ROW Function to Find Row Where Last Value Exists. I tried to modify it to lookup the information on another sheet, but it is returning #NAME? instead of the row number.

I am using this: =LOOKUP(2,1/((Week_1!B:Week_1!B)),ROW(Week_1!B:Week_1!B))

Lutfor Rahman Shimanto Mar 10, 2024 at 6:23 PM

Hello JULI

Thanks for visiting our blog and sharing your query. You can modify the formula mentioned in example 1.4 and find the last cell with a number value on another sheet: `=LOOKUP(2,1/(Week_1!C:C),ROW(Week_1!C:C))`

I hope the formula will overcome your issue; good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy

Advanced Excel Exercises with Solutions PDF