Excel Find the largest partial value in an indexed list

Question

I am working with excel and trying to find if a portion of one cell matches anything from a list. I am attempting to extract that part of the cell as my result.

The formula I am working with is:

{=INDEX($A$1:$A$10,MATCH(1,COUNTIF(B1,"* "&$A$1:$A$10&"*"),0))}

note: had to space out the asterisk to avoid italics

A1 to A10 is the list i am referencing and anything in column B is what I am searching partail parts for in the list

The problem is the formula return the most common value found in the list instead of the most specific part of the list.

Example:

        A(list)          B           (formula result)  (desired Result)

1       ABC          sdfjABCsdhfs          ABC               ABC
2       ABC123       asdfasdfa             #N/A              #N/A
3       RBZ456       agfhABC1234shj        ABC               ABC1234
4       ABC1234      wuefhiuvbsiue         #N/A              #N/A
5       DEF123       dfsghABC123sdf        ABC               ABC123

The maximum result found in column B is what I am looking to find as my result. Not the lowest common denominator.

Any advice would be great!

Thanks in advance.

edit: added brackets, it is an array formula

Your formula does not return the "most common" value, nor the "lowest common denominator". Rather it returns the first entry matching your criteria that it finds. What do you mean by "maximum" in this case? How can you have a "maximum" of an alphanumeric string? Do you perhaps mean "longest"? Or perhaps the last example in the column which satisfies your test? — XOR LX
Yes, I do mean longest string of text available that matches something in the list. B5 contains ABC123 which is in column A (the list), but since B3 contains ABC1234, which is also in the list, returning ABC1234 would be correct. — Kevin
Sorting the list by length appears to have worked. I will do some double checking to make sure. Thank you for that tip. Is there anyway to do this via code without sorting? I was not aware that this type of formula was just going down a list and stopping at the first match instead of searching out the most specific match. — Kevin

XOR LX XOR LX · Accepted Answer · 2014-10-01T20:28:02

Array formula**:

=INDEX($A$1:$A$10,MATCH(MAX(IF(COUNTIF(B1," *"&$A$1:$A$10&" *"),LEN($A$1:$A$10))),IF(COUNTIF(B1," *"&$A$1:$A$10&" *"),LEN($A$1:$A$10)),0))

(I also added a single space before each of the four asterisks.)

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Excel Find the largest partial value in an indexed list

1 Answers