Thursday, 17 July 2014

How to Pull out Last available String or Number from a Range

How to Pull out Last available String from Strings Data or Number from Numbers Data Range
Scenario-I :
Suppose there is list of Strings in the Range A3:F3 , In that Case if you want to Pull out Last String Value used in the Range , You can try the Following Formulas , also refer in below image for better understanding :

Case-I :Range of Strings with Continuous Occurrence :

 =INDEX(A3:F3,0,COUNTA(A3:F3))

Case-II :Range of Strings with Random Occurrence :


 =INDEX(A10:F10,MATCH("",A10:F10, -1))

Scenario-II :
Suppose there is list of Numbers in the Range A3:F3 , In that Case if you want to Pull out Last Number Value used in the Range , You can try the Following Formulas , also refer in below image for better understanding :

Case-I :Range of Numbers with Continuous Occurrence :

 =INDEX(A15:F15,0,COUNTA(A15:F15))

Case-II :Range of Numbers with Random Occurrence :

=INDEX(A22:F22,0,MATCH(-123456789,A22:F22, -1))


Thanks.,
TAMATAM
       [ BI-Analyst ]

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts