Forum Discussion
Extract data using text within set of data
Don't understand why my simple solution shouldn't work. Used Patrick's file to illustrate how it works.
the intent is that F4 should identify from col B which have substring "SLS" and return max value among those cases (SLS1,SLS2,SLS3,SLS4 etc). similarly for ULS
If the data is presented like this then you can roll it up with GROUPBY:
=GROUPBY(REGEXEXTRACT(name,"[a-z]+",1,1),val,MAX)If the data is more nuanced, then you'll need to upload an anonymized .xlsx.
Hi Patrick
Thanks for your reply it sort of works but not exactly what I'm looking for. in your excel you are putting the formula in G3, hit enter and it extracts the substrings from col D and displays the corresponding function (max) in front of it in col H. What i need is to use a formula in cell H3 that reads entries in col D with substring "SLS" and returns the maximum value from col E corresponding to those entries. It need not 'spill'.
I think we may have a difference in philosophy here. I believe in spilling whenever possible - entire results in a single input. Things become more difficult when we're trying to fit a solution in a space.