Max Value in a given column range based on a partial text string that is in a cell

[Zerorules]

Hi,
I am having trouble trying to get a formula to work that can find the Max Value in a given column range based on a partial text string that is in a cell. The data is sorted in a table, that has 10 columns. First having the name and the other 9 with different bits of info about that item. I want the formula to find all the rows that partially match the text string recorded in a cell using column 1. Then find the max value in columns 4-10 from the remaining rows.
Thanks

 

[jasonb75]

Try something like this, edit names etc to match your actual table.

=AGGREGATE(14,6,Table1[[Column4]:[Column10]/ISNUMBER(SEARCH("text string",Table1[Column1])),1)

 

[Zerorules]

Guessing you change the final number to 2-6 based on what column I need the max value from? It worked which is great thanks!

 

[jasonb75]

No, it should always be 1 for MAX value, that is looking at all columns from 4 to 10. Changing it to 2 will find the second largest number.

If you want to get the MAX from each column individually then just use a single column in the first part of the formula, alternatively you could also use

=MAXIFS( Table1[Column4],Table1[Column1],"*text string*")

but this will only work with one column at a time, not all at once like the first formula.

 

[Zerorules]

Yeah I worked that one out thanks!
Don't suppose you would know how to conditionally format cells using a colour scale, based on a text string? As in all the info in Health to Speed need to be colour scaled from lowest to highest. However the only way I know how to do it is to do this to the entire column at a time, however it would include value from all types instead of only one. As in the rows with "giga" in it all need to be scaled together without those containing "theriz".

 

[jasonb75]

Not sure about that one, as it's a totally different question it would be better to start a new thread for it.