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

Lookup and Reference Examples

  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • Multi-criteria lookup and transpose in Excel
  • Get nth match with INDEX / MATCH in Excel
  • Last row number in range
  • How to use Excel TRANSPOSE Function

Data Analysis Examples

  • Data Series in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • How to calculate current stock or inventory in Excel
  • Working With Tables in Excel
  • How to perform a t-Test in Excel

Data Validation Examples

  • Excel Data validation only dates between
  • Excel Data validation require unique number
  • Excel Data validation date in specific year
  • Excel Data validation don’t exceed total
  • Excel Data validation must contain specific text

Find longest string with criteria in Excel

by

This tutorial shows how to Find longest string with criteria in Excel using the example below;

Formula

{=INDEX(range1,MATCH(MAX(LEN(range1)*(range2=criteria)),LEN(range1)*(range2=criteria),0))}

Explanation

To find the longest string in a range with criteria, you can use an array formula based on INDEX, MATCH, LEN and MAX. In the example shown, the formula in F6 is:

{=INDEX(names,MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0))}

Where “names” is the named range C5:C14, and “class” is the named range B5:B14.

Worked Example:   How to get first row number in range in Excel

Note: this is an array formula and must be entered with control + shift + enter.

How this formula works

The core of this formula is the MATCH function, which locates the position of the longest string using supplied criteria:

MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)

Note MATCH is set up to perform an exact match by supplying zero for match type. For lookup value, we have:

LEN(names)*(class=F5)

The LEN function returns an array of results (lengths), one for each name in the list where class = “A” from cell F5 :

{5;6;8;6;6;0;0;0;0;0}

This effectively filters out all of Class B, and the MAX function then returns the largest value, 8.

Worked Example:   Left Lookup in Excel

To construct a lookup  array, we use the same approach:

LEN(names)*(class=F5)

And get the same result:

{5;6;8;6;6;0;0;0;0;0}

After LEN and MAX run, we have a MATCH formula with these values:

MATCH(8,{5;6;8;6;6;0;0;0;0;0},0))

MATCH then returns the position of 8 in the list, 3, which feeds into INDEX like this:

=INDEX(names,3)

Finally, INDEX dutifully returns the value in the 3rd position of names, which is “Jonathan”.

Worked Example:   How to get last row in text data in Excel

Post navigation

Previous Post:

Find longest string in column in Excel

Next Post:

Find lowest n values in Excel

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 extract multiple lines from a cell in Excel
  • Two ways to Compare Text in Excel
  • How to translate letters to numbers in Excel
  • How to extract domain from email address in Excel
  • How to extract text between parentheses in Excel
  • Convert Excel time to decimal seconds
  • Convert date string to date time in Excel
  • Get project end date in Excel
  • Roll back weekday to Friday base on a particular date in Excel
  • How to enter Today’s Date or Static Date and Time in Excel
  • VDB function: Description, Usage, Syntax, Examples and Explanation
  • How to set check register balance in Excel
  • How to calculate annual compound interest schedule in Excel
  • How to calculate compound interest in Excel
  • XNPV function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles