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 ROWS Function
  • How to reference named range different sheet in Excel
  • How to get relative column numbers in a range in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to get last column number in range in Excel

Data Analysis Examples

  • How to create Gauge Chart in Excel
  • Add Outline to Data in Excel
  • How to Create Thermometer Chart in Excel
  • Excel Bar Chart
  • How To Filter Data in Excel

Data Validation Examples

  • Excel Data validation must begin with
  • Excel Data validation require unique number
  • Excel Data validation whole percentage only
  • Data validation must not exist in list
  • 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:   Excel MAX, MATCH and ADDRESS function illustration

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:   Find missing values 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:   How to get last line in cell 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
  • How to count specific words in a range in Excel
  • How to count specific words in a cell in Excel
  • TRIM function: Description, Usage, Syntax, Examples and Explanation
  • How to strip html from text or numbers in Excel
  • How to count specific characters in a range in Excel
  • How to calculate next anniversary date or birthday in Excel
  • How to calculate Next working/business day in Excel
  • Display Days until expiration date in Excel
  • Custom weekday abbreviation in Excel
  • SECOND function: Description, Usage, Syntax, Examples and Explanation
  • PRICE function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest rate for loan in Excel
  • Calculate periods for annuity in Excel
  • Calculate interest for given period in Excel
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles