Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel VLOOKUP Function
  • How to get last column number in range in Excel
  • How to use Excel LOOKUP Function
  • How to retrieve first match between two ranges in Excel
  • MATCH function: Description, Usage, Syntax, Examples and Explanation

Data Analysis Examples

  • How To Remove Duplicates In Excel Column Or Row?
  • Remove Duplicates Example in Excel
  • How to count table columns in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • How to create a Histogram in Excel

Data Validation Examples

  • Excel Data validation date in next 30 days
  • Excel Data validation must not contain
  • Excel Data validation exists in list
  • Excel Data validation require unique number
  • Excel Data validation date in specific year

How to get last row in text data in Excel

by

To get the last relative position (i.e. last row, last column) for text data (with or without empty cells), you can use the MATCH function. See example below:

Formula

=MATCH(bigtext,range)

Explanation

In the example shown, the formula in E5 is:

=MATCH(REPT("z",255),B4:B11)

How this formula works

This formula uses the MATCH function in approximate match mode to locate the last text value in a range. Approximate match enabled by setting by the 3rd argument in MATCH to 1, or omitting this argument, which defaults to 1.

Worked Example:   Extract most frequently occurring text in Excel

The lookup value is a so-called “big text” (sometimes abbreviated “bigtext”) which is intentionally a value “bigger” than any value that will appear in the range. When working with text, which sorts alphabetically, this means a text value that will always appear at the end of the alphabetic sort order.

Since this formula matches text, the idea is to construct a lookup value that will never occur in actual text, but will aways be last. To do that, we use the REPT function to repeat the letter “z” 255 times. The number 255 represents the largest number of characters that MATCH allows in a lookup value.

Worked Example:   How to get last line in cell in Excel

When MATCH can’t find this value, it will “step back” to the last text value in the range, and return the position of that value.

Note: this approach works fine with empty cells in the range, but is not reliable with mixed data that includes both numbers and text.

Worked Example:   SUMPRODUCT count multiple OR criteria in Excel

Last relative position vs last row number

When building advanced formulas that create dynamic ranges, it’s often necessary to figure out the last location of data in a list. Depending on the data, this could be the last row with data, the last column with data, or the intersection of both. Note: we want the last *relative position* inside a given range, not the row number on the worksheet:

Post navigation

Previous Post:

How to get last row in numeric data in Excel

Next Post:

Last row number in range

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Count Number of Words in Excel
  • FIXED function: Description, Usage, Syntax, Examples and Explanation
  • Remove line breaks in a cell in Excel
  • BAHTTEXT function: Description, Usage, Syntax, Examples and Explanation
  • How to check if cell contains all of many things in Excel
  • Get project end date in Excel
  • How to enter Today’s Date or Static Date and Time in Excel
  • Get days between dates in Excel
  • TIMEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • Get last day of month in Excel
  • Calculate original loan amount in Excel
  • How to Calculate Tax Rates in Excel
  • Bond valuation example in Excel
  • DISC function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate present value of annuity in excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles