Explanation
The goal is to find the longest text string in the range B5:B16.
The easiest way to solve this problem is with theXLOOKUP functionor theFILTER function.
However in older versions of Excel without the XLOOKUP function, you might use anINDEX and MATCH formula.
Both approaches are explained below.
For convenience, the range B5:B16 is nameddata.
However, you could use a regular cell reference as well.
The formula in E6 is:
Wheredatais thenamed rangeB5:B16.
The result is “Esmeralda”, which contains 9 characters.
This makes sense in cases where there may be ties, and you would like to report all results.
We are asking FILTER forall valuesindatawhere the length of the text string equals the max length found indata.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
you’re able to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
MAX ignores empty cells, the logical values TRUE and FALSE, and text values.
LEN will also count characters in numbers, but number formatting is not included.
Related videos
Basic XLOOKUP example
FILTER function basic example
