Excel: combining averageif and search formula

I have a criteria range and want to check if the cells in that range are substrings of a larger string. Only then do I want to incorporate the corresponding values in the average. However, I cannot make use of the Search function, since it takes two arguments. I also cannot use the "*" method, because the cells in my criteria range are the substrings and not the superstrings. Please help.

1 Answer

You can use a variant of this:

=AVERAGE(IF(NOT(ISERROR(FIND($H$5:$H$10,$I$2,1))),$I$5:$I$10))

This is checking if the values in column H are found within the value in cell I2. If they are, then the value from column I for the corresponding row is returned by IF. We then average all such rows.

The above formula is case-sensitive. Note that TRING is not included but tring is. If you want to make it case insensitive, you can use this:

=AVERAGE(IF(NOT(ISERROR(FIND(LOWER($H$5:$H$10),LOWER($I$2),1))),$I$5:$I$10))