[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Public Holidays-Dates VLookup Function

Posted on 2011-09-08
5
Medium Priority
?
598 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:ceneiqe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 36503509
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 200 total points
ID: 36503525
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
 

Author Comment

by:ceneiqe
ID: 36503577
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 36503849
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
 

Author Closing Comment

by:ceneiqe
ID: 36508120
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question