Archive of Mr Excel Message Board

Hello Patrick, try this formula

B64=IF(Holidays="Jan","yes","no")

Hi Guys,

I'm sorry to say it, but this is not the way you can use the IF-function. You can't feed a 2 or more cells as you guys do and expect a correct result. If the first cell of the range named Holidays contains Jan, you'll get a Yes. If the first cell does not contain Jan but any other cell does, you'll get No.

What Patrick wants can be done with an array formula (which you enter by hitting CONTROL+SHIFT+ENTER) like the following:

=IF(EXACT(UPPER("Jan"),UPPER(Holidays)),"Yes","No")

Another point: the way Patrick builds up Holidays is also questionable. I'd advise using a similar formula that I just gave.

Aladin

Hi all

Just my two cents worth, but I wouldn't use an array formula unless really necessary. My reasons being, like loops in VBA they seem to become habit forming and too many of them really slow down re-calculation. Here is another way without an array.

=IF(LOOKUP("Jan",Holidays)="Jan","Yes","No")

Dave

OzGrid Business Applications

I think(?) that the formula =IF(LOOKUP("Jan",Holidays)="Jan","Yes","No") will return #N/A if "Jan" does not appear in Holidays.

I think the following structure is required :-
=IF(ISNA(LOOKUP("Jan",Holidays)),"No","Yes")
or an alternative :-
=IF(ISNA(MATCH("Jan",Holidays,0)),"No","Yes")

Stalker

That's because you didn't try it first, isn't it Stalker :o)

Dave

OzGrid Business Applications

I didn't try it first but have now and get #N/A. What am I doing wrong?
Stalker

You aren't doing anything wrong Stalker, It's me. I doing too many things at once my apologies.

Dave
OzGrid Business Applications