# MEDIAN IF Contains Matching Text

#### frankendoc

##### New Member
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.

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### kweaver

##### Well-known Member
Welcome to Mr. Excel.

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

#### Habtest

##### Board Regular
Hi there, something like:
Excel Formula:
``{=MEDIAN(IF(ISNUMBER(FIND(C12,A1:A10)),B1:B10))}``
and enter with CTRL+SHIFT+ENTER

#### Peter_SSs

##### MrExcel MVP, Moderator
Hi there, something like:
Excel Formula:
``{=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

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

Replies
1
Views
156
Replies
0
Views
447
Replies
2
Views
759
Replies
6
Views
292
Replies
14
Views
552

1,148,193
Messages
5,745,263
Members
423,941
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back