Extract Values If Condition is Satisfied

1. Looking for formula (non array preferred) to extract values into column I using column H as the condition for the pull.

2. Also looking for formula (non array preferred) to extract match into column M2 Based on Value in L2.

See attached file.

Thanks

Useless Deleted by me
.

1. use this:

=IFERROR(INDEX(F:F,AGGREGATE(15,6,ROW($F$2:$F$100)/(ISNUMBER(SEARCH(H2,$F$2:$F$100))),COUNTIF($H$2:H2,H2))),"")

2. And this:

=IFERROR(VLOOKUP($L2,$J:$K,2,FALSE),"")

Don't change the range. Put it back the way it was.

using a full column reference in this case wil have NO impact on performance. If you have an uncontrollable urge to change it to match the rest of the formula, then you need to add in more steps:

=IFERROR(INDEX($F$2:$F$100,AGGREGATE(15,6,ROW($F$2:$F$100)-ROW($F$2)+1/(ISNUMBER(SEARCH(H2,$F$2:$F$100))),COUNTIF($H$2:H2,H2))),"")