Search text with array formula and find minimum, if the text contains specified text

[Durlov]

Hello,

I'm still conducting my own search of trying to find the answer to this, but in the meantime thought I'd do a post to see if anyone has a ready answer. Difficulty in conducting the search is the multiple conditions. Basically, I need to search an array for a specific string and then find the minimum of another column if the specific string is in that column. To exemplify:

Search Column E2:E10 for the value in C1 (= "001"), if value exists in any field in Column E, find the minimum of the corresponding values in column G2:G10.

SO, Column E2:E10 can contain values such as E2 = "001", E3 = "001, 002", E4 = "000, 002, 003", E5 = "000, 002, 001"... G2 = 5, G3 = 3, G4 = 5, G5 = 2 etc. Now, I need to evaluate each string to see if it contains "001" and then find the minimum of the values for Column G, which in the above string would be G5 = 2.

So far, I have only come up with the formula:

=MIN(IF($E$2:$E$10=C$1,$G$2:$G$10))

However, the above only looks to see if the whole cells in E2:E10 are equal to "001", i.e. in the above example it is giving the answer, G2 = 5.

Your help is much appreciated

 

[JoeMo]

Untested, but you can try this array formula:

=MIN(IF(ISNUMBER(SEARCH($C$1,$E$2:$E$10)),$G$2:$G$10))

confirmed with ctrl+shift+enter - not just enter.

 

[Durlov]

Hey, awesome! I think that works, let me do a few test and then give a conclusive confirmation.

 

[Durlov]

Well, I'm convinced it works. Thanks much!

 

[Durlov]

[SOLVED] Search text with array formula and find minimum, if the text contains specified text [SOLVED]

[this is solved]