IF(AND issues with named range

[Dan02]

Hello all,

I recently updated my MS Office version, and am encountering a new problem using IF(AND with named ranges.

As can be seen in the image below, the named ranges work fine for a simple IF function, and the IF(AND formula works fine if I use the cells. But when I use the named ranges with IF(AND it doesn't work properly.

I'm stumped : (. Please let me know what may be the issue.

Thanks!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you please post the formula that is not working.

 

[Dan02]

Thanks. I just updated my profile. I'm using "Microsoft 365 Apps for enterprise" Version 2201. As mentioned, my whole Office suite was updated yesterday to the newest version, and that's when this weird issue began.

The formula is:

=IF(AND(Make="Mercedes",Built>=2019),1,0)

Both 'Make' and 'Built' are named ranges. As noted, these named ranges work fine with a simple IF function; something is just thrown off with the IF(AND

Thanks again!

 

[Fluff]

I just updated my profile.

Thanks for that.
How are the two named ranges defined?

 

[Dan02]

Make:

=Sheet1!$A$2:$A$100

Built:

=Sheet1!$B$2:$B$100

 

[Fluff]

Ok, you will need to use

=IF((Make="Mercedes")*(Built>=2019),1,0)

or just

=--((Make="Mercedes")*(Built>=2019))

but both of these will spill down to row 100, so you will need to remove the formula from every cell but the 1st.

 

[Dan02]

Indeed, that works

But what's going on? Why does IF(AND not work in the traditional way with a named range?

And how would one do an IF(OR in this scenario?

Thanks again!

 

[Fluff]

For or you would do it like

=IF((Make="Mercedes")+(Built>=2019),1,0)

Because 365 is dynamic aware the formula is an array formula & you therefore need to use the array for of And/Or

 

[Dan02]

Got it. Just so I can better wrap my head around this...

This qualifies as an array formula because it's taking into account lots of cells at once (i.e., the MAKE range and the BUILT range), or because it's going to fill in lots of cells at once?

And how was the prior Excel version able to handle this even without me inputting it as an array (i.e., CTRL + SHFT + ENTR)?

And it seems like another solution would be to use "@" before the named ranges (?). Would that be a decent alternative, or perhaps not as good for some reason?

Thanks again!

 

[Fluff]

In prior versions xl used what's called implicit intersection, where the formula only looks at the row it's on, whereas now it looks at the whole range.
You could use the @ sign which tells xl to use implicit intersection, it's you choice whether you do that, or let the formula spill