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

Zerorules

New Member
Joined
Jan 17, 2015
Messages
7
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
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
10,970
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jan 17, 2015
Messages
7
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
Joined
Dec 30, 2008
Messages
10,970
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jan 17, 2015
Messages
7
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".
1596183189993.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,970
Office Version
  1. 2019
Platform
  1. Windows
Not sure about that one, as it's a totally different question it would be better to start a new thread for it.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,782
Messages
5,542,493
Members
410,557
Latest member
chidambaramseetha
Top