Search for a text segment using MAX IF in Excel

user_11067657 0
Viewed 56

I have an Excel book with two columns (text and numbers). The first column has a lot of variables such as 174AXX that repeat themselves.

I'm now trying to create a third column where I want to find the highest value of the second column (the one that has numbers) and that is associated with the numbers 174. Meaning that I don't want it to search for the entire word 174AXX. I want it to go to all variables that start with 174 and search the highest value.

I'm using the Max if function however when I try to use "*174*" in the formula it doesn't recognize it.

{Max(if(K:K="*174*";L:L))}
excel
1 Answers

Unfortunately the Asterisk doesn't work this way in Arrays.

Instead you can use the find function like to end up with this formula: 

=MAX(IF(ISNUMBER(FIND("174",K:K)),L:L))

Then press CTRL + SHIFT + ENTER

user_85644010
Related