Median If with lookup

[GSSIEGEL]

I am trying to use the median function to return a value only when the result of a vlookup is a specific value. I am using the formula = Median(IF(P4:P51="Commercial",C4:C51))

Where column P contains the vlookup resulting in either "Commercial" or "Consumer" and column C contains the loan amount. I need to find the median of Commercial loan amount only.

The formula above returns an answer of #Value !

Thoughts?

 

[barry houdini]

Hello GSSIEGEL, welcome to MrExcel

Your formula looks OK but it's an "array formula" so you need to confirm with CTRL+SHIFT+ENTER

To do that select cell with formula - press F2 key to select formula then hold down CTRL and SHIFT keys while pressing ENTER. If you do that correctly you will get curly braces like { ans } around the formula and you should get a numeric result

 

[GSSIEGEL]

That worked. Thank you.