Search for text at end of string

[njkollauf]

Currently I am working on pulling out the max values based off criteria.

However, the array where there =Max(if(iserror(search( array function is search can contain a variety of formats.

Say I want to look up "KD100". I would want to pull back 3 instances:

"KD100" by itself
"*KD100,*", either at the beginning or in the middle of a delimited list
"*,KD100" when it is at the end of the delimited list

The issues is that the last instance ("*,KD100") is hitting when I search for "KD1" and "KD10". Is there a way to denote that I want no text after KD100 in the third instance?

 

[Eric W]

Instead of SEARCH, maybe try:

=RIGHT(array,3)="KD1"

or some variation of that.

 

[njkollauf]

Instead of SEARCH, maybe try:

=RIGHT(array,3)="KD1"

or some variation of that.

Good idea!

=MAX(IF(RIGHT($M$2:$M$100,LEN($H4))=$H4,$Z$2:$Z$100,0)) seemed to do the trick where it only looks at the last item in the delimited list. Thanks for the idea!

 

[Eric W]

You're welcome!