As we know that the counting function is used to return the last relative position for a mixed data, let us now learn how to return the last row on text data in Excel. To do this Excel uses the match function the generic Formula to perform this operation is as given below.
How the formula works
- To get the last row Excel uses the match function combined with a REPT function. The match function locates the last text value in the range of values in the approximate match mode, for this mode, the third argument of the match defaults to one.
- The generic parameter lookup_value is the value to be looked upon and this is intentionally made a value bigger than what value appears in the range.
- When we work with texts, a text value always appears at the end of the alphabetically sorted order.
- Since here we match a text, there is a need to create or build a lookup value that would never appear in the actual text (original text), but will always be the last, and to do this we use the Excel REPT function that repeats the alphabet ”z” 255 times (this means 255 is the largest number of characters that the Excel match function allows). When the match function does not find this value it would take a step back to the last value in the range of text valued and this return its position
Note on usage
- The approach specified above is not reliable when there are mixed data values (text and numbers).
- We can use the above-specified formula with other Excel functions such as the index functions and offset functions to create a dynamic range.
Let us consider a random text data for which the last relative position is estimated. The result using the MATCH function is as shown below in the figure.
Figure 1. The last relative position for text data using the MATCH function