Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Excel Line Chart
  • Conditional Formatting Rules in Excel
  • Understanding Pivot Tables in Excel
  • How To Insert and Customize Sparklines in Excel
  • How To Remove Duplicates In Excel Column Or Row?

References

  • How to use Excel COLUMN Function
  • Offset in Excel
  • Extract data with helper column in Excel
  • Lookup entire row in Excel
  • MATCH function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation must contain specific text
  • Excel Data validation exists in list
  • Excel Data validation no punctuation
  • Excel Data validation number multiple 100
  • Data validation must not exist in list

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 generate random number weighted probability 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:   How To Use AGGREGATE function to sum a range with errors 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:   Get cell content at given row and column in Excel

Post navigation

Previous Post:

Weighted average in Excel

Next Post:

Win loss points calculation 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

Logical Functions

  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • IF with boolean logic in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • OR function Examples in Excel

Date Time

  • Basic timesheet formula with breaks in Excel
  • Calculate total hours that fall between two times in Excel
  • How to calculate future date say 6 months ahead in Excel
  • How to get Weekdays, Working days between Two Dates in Excel
  • Roll back weekday to Friday base on a particular date in Excel

Grouping

  • Group arbitrary text values in Excel
  • How to randomly assign people to groups in Excel
  • If cell contains one of many things in Excel
  • How to randomly assign data to groups in Excel
  • Group times into unequal buckets in Excel

General

  • How to calculate percent change in Excel
  • Find Most Frequently Occurring Word in Excel Worksheet
  • How to calculate percent of goal in Excel
  • Flash Fill in Excel
  • Lock Cells in a Worksheet Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning