Whats wrong with this formula?

[shinstrike]

Hi, this is the formula am trying to work on:
=IF(VLOOKUP(N9,'National Holiday'!$A$1:$A$1000,1,FALSE),"HOLIDAY",AND(IF(WEEKDAY(N12,2)=1,"Mon",IF(WEEKDAY(N12,2)=2,"Tue",IF(WEEKDAY(N12,2)=3,"Wed",IF(WEEKDAY(N12,2)=4,"Thrs",IF(WEEKDAY(N12,2)=5,"Fri",IF(WEEKDAY(N12,2)=6,"Sat","Sun"))))))))

my aim is that if the date range fall into the vlookup, it will state holiday, but if it doesnt fall into the vlookup date range, it will state the day of that date.

The "HOLIDAY" will come out but the other name of the day won't come out, if it's not holiday, it just has #N/A as the result. I suppose it is something wrong wwith the AND, IF function.

Any help will be much appreciated

 

[Jonmo1]

Try
=IF(ISNUMBER(MATCH(N9,'National Holiday'!$A$1:$A$1000,0)),"Holiday",TEXT(N9,"ddd"))


Note, it didn't help your cause that you were using N9 in the Vlookup, but N12 in all your IF's...

 

[Dave Patton]

possibly

=IF(ISNA(VLOOKUP(N9,'National Holiday'!$A$1:$A$1000,1,FALSE)),TEXT(N9,"ddd"),"Holiday")

 

[shinstrike]

Try
=IF(ISNUMBER(MATCH(N9,'National Holiday'!$A$1:$A$1000,0)),"Holiday",TEXT(N9,"ddd"))


Note, it didn't help your cause that you were using N9 in the Vlookup, but N12 in all your IF's...

It works wonder Thankss alot mate :D
am still wondering why my formula doesnt work tho..

 

[Jonmo1]

Glad to help

I am still wondering why my formula doesnt work tho..

probably because of the reason I stated in my post