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

#### Zerorules

##### New Member
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

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### jasonb75

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

##### New Member
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

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

Replies
15
Views
113
Replies
1
Views
29
Replies
3
Views
114
Replies
4
Views
86
Replies
2
Views
145