Find the Last Row of data in Excel Using INDEX and COUNTA
Written by kazamraza
no comments
Classified in : Excel Formulas, Lookup Functions
Sometime we have a requirement that we want to fetch data from last cell in a column. This task can be achieve by using
- INDEX Function (Return the value or reference of the cell at the intersection of particular row and column in a give range)
- COUNTA Function (Count the number of cell in a range that are not empty)
=INDEX(A:A,COUNTA(A:A))
In the following example in Column A, we use COUNTA to find the last cell number containing data is 14. and place this value in INDEX Function to get last cell value
| ◢ | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Data | Formula | Result Last Cell Value | ||
| 2 | RAZIA BEEGUM W/o JAVAID MASIH | =INDEX(A:A,COUNTA(A:A)) | ARIFA BIBI W/o FAZAL ABBAS | ||
| 3 | RAZIA ASIF W/o ASIF ALI | ||||
| 4 | SUMERA FAROOQ W/o FAROOQ RAFIQUE | ||||
| 5 | ZUMURD BIBI W/o MOHAMMAD BASHIR | ||||
| 6 | FOZIA BIBI W/o ASHRAF HUSSAIN | ||||
| 7 | SHAHEEN ARSHAD W/o MUHAMMAD ARSHAD | ||||
| 8 | NAZIA ABID W/o ABID HUSSAIN | ||||
| 9 | ISHRAT ASIF W/o ASIF ALI | ||||
| 10 | JOTI D/o IRFAN JAVED | ||||
| 11 | SANA SHABBIR W/o SHABBIR AHMAD | ||||
| 12 | SOBIA ASLAM W/o MUHAMMAD ASLAM | ||||
| 13 | KHALIDA PARVEEN W/o MUHAMMAD ISHAQ | ||||
| 14 | ARIFA BIBI W/o FAZAL ABBAS | ||||
| 15 | |||||
| 16 | |||||
| 17 | |||||
| 18 | |||||
| 19 | |||||
| 20 |