Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

Last Row in Text Data

We can use the MATCH function to get the last row in text data when working with excel spreadsheets. This can be done either with or without empty cells as thoroughly explained in this post.

Figure 1: Using MATCH function to find last relative position of text data

Syntax of the formula

=MATCH(REPT(text, number_times), lookup_array)

Understanding the formula

For this formula to locate the last text in a rage, it uses the MATCH function in an approximate mode. There are two ways I which the approximate match mode can be enabled as shown below;

  1. Set the 3rd argument in MATCH to 1
  2. Omit the 3rd argument, thus setting the default to 1.

In the formula, we have the text, also referred to as the bigtext or big text, which is supposed to be a value that is bigger than any other value that will appear in the range. If it is numbers, then this will be the biggest number in the data provided. If it is alphabetical text, then this will be the text that appears at the most bottom if the text alphabetically sorted.

How the formula works

  • MATCH function allows only 255 characters to appear in any lookup value. This is why we use the REPT function, to repeat “z” 255 times.
  • In the event the MATCH function does not find this letter, then it will step back to the last text and return the position of that value.

Example

Figure 2: Using MATCH function to get the last relative position of a text data

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar
WymSkPhN
Guest
WymSkPhN

1

Comment awaiting moderation

wUmrLVWz
Guest
wUmrLVWz

1

Comment awaiting moderation

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc