# last cell in a range

#### 1inus

##### New Member
I'm looking for a formula I can put in a cell that will reference the last cell in a single column range (A1:A10). After searching around I came across the following formula:

=INDEX(A1:A10,MATCH(9.99999999999999E+307,A1:10))

Is there a different formula I should use or is this one a good one? Also, I'm not understanding why the 9.99999999999999E+307.

thank you

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### RalphA

##### Well-known Member
=INDEX(A1:A10,MATCH(9.99999999999999E+307,A1:10))

The MATCH() function returns the relative row a value in the range A1:A10 matches, or is equal to, the number 9.9999999999999E+307. However, if it finds no match, it returns the number of rows it looked through. Note that you could use any reasonable number that will always be larger than any number that will be found in the range A1:A10. So, if the range contains the series 1, 2, 3...10, you could use 11 as your "large number". Probably, a large number, such as 1000, might be perfectly suitable, but, the excelent programmer who suggested the 9.99999999 etc.E+307 chose the largest number that Excel recognizes, just to cover all bases!

The INDEX() function returns the data that is in the cell on the row returned by the MATCH() function, counting from the first row in the range.
In your case, it should return the data in the last cell in column A.

If you need to know the last row number, rather than the data in that last cell, you could use, assuming cell A3 is the first cell in your range:
=row(A3)-1+MATCH(9.99999999999999E+307,A1:10)

Let us know if you are now satisfied with the above.

#### Barry Katcher

##### Well-known Member
Great explanation, Ralph

#### RalphA

##### Well-known Member
Coming from you, Barry, I feel truly honored. Thank you!

##### MrExcel MVP

I'm looking for a formula I can put in a cell that will reference the last cell in a single column range (A1:A10). After searching around I came across the following formula:

=INDEX(A1:A10,MATCH(9.99999999999999E+307,A1:10))

Is there a different formula I should use or is this one a good one? Also, I'm not understanding why the 9.99999999999999E+307.

thank you

See: http://tinyurl.com/83b2x

#### RalphA

##### Well-known Member
If still interested in showing the value in the last cell of your range A1:A10, try this:

=INDIRECT("a"&COUNTA(A:A))

And, this formula is dynamic, so it will show the last cell in the column, even as you add new data. How about it?

#### ckng

##### New Member

hi
if my data starts from A3 instead of A1.. how do i edit the formula????

=INDIRECT("a"&COUNTA(A:A))

#### RalphA

##### Well-known Member
hi
if my data starts from A3 instead of A1.. how do i edit the formula????

=INDIRECT("A"&COUNTA(A:A))
You don't. But, you do have to have something in A1 and A2, either text or numbers. If either cell is empty, enter a single quote, which will not be visible, but will allow the formula to work.

Oops!

ok, thank you

Replies
3
Views
91
Replies
2
Views
82
Replies
3
Views
87
Replies
5
Views
76
Replies
12
Views
280

1,136,706
Messages
5,677,309
Members
419,685
Latest member
hennLow

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back