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.
Another thing this formula works for extracting the max value for the given substring. if i use min instead of max, it is returning 0 instead of the value 17 (among all SLS). But if there is a -ve value it returns that. Can you please also check for that. Thank you.
min should give value from cell E7=17 but is returning 0.
if theres a negative value(E8) in the range, min returns that.
Try this then (based on the data in the file I sent earlier):
=MIN(FILTER(F7:F5000,ISNUMBER(FIND($Q$5,C7:C5000))))
Hi Riny
Can you pls help me out here. in cell F2, i want the max out of ULS 1,2,3 (C2:C4) but its returning value from C5 based on the formula i have used. Can you pls suggest required correction in the formula. i'm afraid i'm unable to attach file in reply.
Well, that's inevitable as "ULS" is found in all of these. You'll need to define more concise filtering criteria. In this particular example you could use:
=MIN(FILTER(C7:C5000,(ISNUMBER(FIND("ULS",B7:B5000)))*(LEN(B7:B5000)=5)))
assuming there is a space separating "ULS" and the (single) digit. But it's not very robust and will probably require a fair amount of tweaking to capture any other exceptions.
Thank you it works.