MEDIAN IF Contains Matching Text

[frankendoc]

HI,
I'm trying to do the Median equivalent of AVERAGEIF(A1:A10,"*"&C12"&*",B1:B10) where Column C has text some of which is contained within a longer string in Column A and Column B has numerical values that I want to find the median of.
I found other threads where this was possible with the wildcards if the search term was typed in but not if the search term is in another cell (C12 in this case).
Any help would be greatly appreciated.

 

[kweaver]

Welcome to Mr. Excel.

It would help if you show a few examples and use XL2BB to do that.

 

[Habtest]

Hi there, something like:

{=MEDIAN(IF(ISNUMBER(FIND(C12,A1:A10)),B1:B10))}

and enter with CTRL+SHIFT+ENTER

 

[Peter_SSs]

Hi there, something like:

{=MEDIAN(IF(ISNUMBER(FIND(C12,A1:A10)),B1:B10))}

and enter with CTRL+SHIFT+ENTER

Not sure whether this is actually relevant to the OPs data as we don't know much about that, but to be truly equivalent to the AVERAGEIF formula posted, that formula would need to use SEARCH instead of FIND.

Just an aside too: If the OP is using this in their Excel 365 as opposed to Excel 2016, CTRL+SHIFT+ENTER confirmation would not be required.

.. and another 365, but not 2016, option would be

=MEDIAN(FILTER(B1:B10,ISNUMBER(SEARCH(C12,A1:A10))))