What is the difference between + and "OR" in an array formula??

[MIAO]

If I want to write in excel: Check all the cells in Column L if ="Toronto or "Vancouver" then check the corresponding cell in column B and if its less than 1 million and great or equal to 600k "yes" false "No"

=MAX(IF(OR(('large loans'!$L$6:$L$17="Toronto"),('large loans'!L6:L17="Vancouver")),'large loans'!B6:B17,))
is there a difference

=MAX(IF(('large loans'!L7:L18="Toronto")+('large loans'!L7:L18="Vancouver"))'large loans'!B7:B17,))

Why am I getting different outputs? and they are not the right outputs?

[daddylonglegs]

OR doesn't work with arrays because it returns a single value, not an array, try tweaking your other formula like this

=MAX(IF(('large loans'!L7:L18="Toronto")+('large loans'!L7:L18="Vancouver"),'large loans'!B7:B18))

confirm with CTRL+SHIFT+ENTER

That gives the maximum value in column B where one or either condition is met in column L....is that what you need, your description indicates something else?

[Pichingualas]

You might be getting different outputs because you don't have the same ranges selected...

=MAX(IF(OR(('large loans'!$L$6:$L$17="Toronto"),('large loans'!L6:L17="Vancouver")),'large loans'!B6:B17,))

=MAX(IF(('large loans'!L7:L18="Toronto")+('large loans'!L7:L18="Vancouver"))'large loans'!B7:B17,))

Im not saying that this is what's going on, but if you want to compare the results from two formulas at least apply them to the same range... Try changing the second one to this:

Please Login or Register  to view this content.

and then compare the results. You can't compare inconsistent formulas.

[MIAO]

THANKs so much ya I didn't know that OR did not work on arrays!



If I want to know the max in column B for EVERYTHING except the above listed palces "Unionville" "Oakville", "Thornhill" "Toronto" etc. in column L how would I go about doing that?

Thanks so much!