Public Holidays-Dates VLookup Function

I have done a Vlookp but it doesn't work.
Cell J2 should read "New Year's Day" but there is an error.

What did i do wrongly ?

 Lookup-PublicHolidays-EE.xls
ceneiqeAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
Sorry, there was a superfluous = in that last formula .....and both formulas need to have the M2:N10 range made "absolute2 so make the first one

=VLOOKUP(B2,M$2:N$10,2,FALSE)

and the second

=IF(ISNA(VLOOKUP(B2,M$2:N$10,2,FALSE)),"",VLOOKUP(B2,M$2:N$10,2,FALSE))

regards, barry
0
 
barry houdiniCommented:
Try this version

=VLOOKUP(B2,M2:N10,2,FALSE)

or to avoid errors when the date isn't in the tablle

=IF(ISNA(VLOOKUP(B2,M2:N10,2,FALSE)),"",=VLOOKUP(B2,M2:N10,2,FALSE))

regards, barry
0
 
ceneiqeAuthor Commented:
after trying, I think the code should be :
=VLOOKUP(B2,$M$2:$N$10,2,FALSE)

OR

=IF(ISNA(VLOOKUP(B2,$M$2:$N$10,2,FALSE)),"",VLOOKUP(B2,$M$2:$N$10,2,FALSE))

[to insert $ sign to keep the reference table static.]


am i right to say that :
- for the col_index_no, the no. should always be calculated from the start of the table , ie 1) Date 2) PH so choose 2 for PH instead of 14 (counts from column A the start of the spreadhsheet)  ?
0
 
barry houdiniCommented:
Yes, that's the same as my second post except you have put $ in front of the column letters too.If copying down then that isn't strictly required but it doesn't harm either......

Yes, column index number is relative to the table only....so for any VLOOKUP with a 2 column table that number should always be 2, whatever the location of the table.

barry
0
 
ceneiqeAuthor Commented:
Thanks, I think I was writing as you were posting too.

just thinking: is there a built in holidays (for countries) in excel besides using the above method ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.