Array formula not working

=MIN(IF(('large loans'!L6:L9000="Unionville")+('large loans'!L6:L9000="Oakville")+('large loans'!L6:L9000="Thornhill,")+('large loans'!L6:L9000="West Vancouver")+('large loans'!L6:L9000="North Vancouver")+('large loans'!L6:L9000="Richmond")+('large loans'!L6:L9000="Bumaby")+('large loans'!L6:L9000="Victoria")+('large loans'!L6:L9000="Montreal")+('large loans'!L6:L9000="Quebec")+('large loans'!L6:L9000="Vancouver")+('large loans'!L6:L9000="Calgary"),'large loans'!B6:B9000)) ===output is wrong

would anyone know the why an array formula would work and a regular wounldn't?
What does the arry enter mean? if we take this as an example how would be translate the 2 different formulas into words?

{=MIN(IF(('large loans'!L6:L9000="Unionville")+('large loans'!L6:L9000="Oakville")+('large loans'!L6:L9000="Thornhill,")+('large loans'!L6:L9000="West Vancouver")+('large loans'!L6:L9000="North Vancouver")+('large loans'!L6:L9000="Richmond")+('large loans'!L6:L9000="Bumaby")+('large loans'!L6:L9000="Victoria")+('large loans'!L6:L9000="Montreal")+('large loans'!L6:L9000="Quebec")+('large loans'!L6:L9000="Vancouver")+('large loans'!L6:L9000="Calgary"),'large loans'!B6:B9000))} === Output is Right


Now, again in the example below array words and regular doesn't


=MAX(IF(ISNA(MATCH('large loans'!L6:L9000,{"Toronto","Vancouver","Unionville","Oakville","Thornhill","West Vancouver","North Vancouver","Richmond","Bumaby","Victoria","Montreal","Quebec","Calgary"},0)),'large loans'!B6:B9000)) ===output incorrect

{=MAX(IF(ISNA(MATCH('large loans'!L6:L9000,{"Toronto","Vancouver","Unionville","Oakville","Thornhill","West Vancouver","North Vancouver","Richmond","Bumaby","Victoria","Montreal","Quebec","Calgary"},0)),'large loans'!B6:B9000))} ===output correct


BUT in this last example that's VERY simliar to the example above the opposite is true. The reglar works and the array formula wouldn't

=MIN(IF(ISNA(MATCH('large loans'!L6:L9000,{"Toronto","Vancouver","Unionville","Oakville","Thornhill","West Vancouver","North Vancouver","Richmond","Bumaby","Victoria","Montreal","Quebec","Calgary"},0)),'large loans'!B6:B9000)) ===output correct




Can someone please explain to me what does an "array" mean in these cases and why does some formula work with arry and some don't?


Thanks,

Hello there,

See the discussion here

http://www.excelbanter.com/archive/i.../t-148034.html

Thank you for the link but would anyone be able to explain my specific situation posted above?

Thanks