# Calculate Median If a range contains one of the values from another range

I would like to calculate Median of values in a column F, if the value in Column I contains one of the NUMBERS in a range (N5:N10).

Currently I am calculating using the following formula :

``````=MEDIAN(
IF(
OR(I4:I466 = N5, I4:I466 = N6,  I4:I466 = N7 ),
F4:F466)
)
``````

Can anyone help me to replace the OR statement in the formula I am using ?

Thanks

Rahul

How it works:

You need to use an array (CSE) formula to compare two data range.

• Formula in cell `E58`:

``````{=IFERROR(IF(MATCH(TRUE,COUNTIF(E\$51:E\$55,F\$51:F\$55)>0,0),MEDIAN(D51:D55),0),"")}
``````

N.B. Finish formula with Ctrl+Shift+Enter.

• Cell `E57` has your formula:

``````{=MEDIAN(IF(OR(E51:E55=F51,E51:E55=F52,E51:E55=F53,E51:E55=F54,E51:E55=F55),D51:D55,0))}
``````

N.B.

• It must be an array (CSE) formula, and need to finish with Ctrl+Shift+Enter.

• Adjust cell references in the formula as needed.

• Thanks Rajesh for such an illustrative example. You saved my day😊 Apr 27, 2020 at 12:59
• Glad to help U @Rahul,,, since now the suggested method is useful to you then, You may accept it as answer & vote too Apr 28, 2020 at 5:44
• @Rajesh...I have voted but its not showing as my reputation is less than 15. Hope it will be shown soon. Thanks again for your efforts May 1, 2020 at 14:46
• @Rahul,, no issue but you may accept it as an `Answer`,, just click on grey tick mark on left top of my post,, will turn to green ! ☺ May 2, 2020 at 6:02
• That's already done 😉👍 May 2, 2020 at 15:19