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 Expert 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:
Solution examples
I need help with a vlookup type formula that will take a given name and reference it against a list of names in columns D-O. then take a given date and reference it against dates in columns A10-A440, then return the cell or its contents at the intersection of those that column and row.
Solved by I. H. in 13 mins
How do I prevent Duplicate entries in multiple columns in Excel using data validation in MSExcel 2016. I would like to prevent the user from data entering a code more than once in three column ranges
Solved by B. D. in 60 mins
index and match with duplicates. I need to use another column as a reference, so my return value has two match the value of two things for it to return
Solved by E. H. in 60 mins
I have a file in which I need to somehow use a conditional formatting for the same names of the data available, or if there is any other way to calculate the frequency of one particular name appearing.
Solved by K. F. in 51 mins
I need a formula to compare the data in two columns and then export the mismatched data in the 3rd column
Solved by S. Q. in 20 mins

Leave a Comment

avatar

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 Expert 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