Solved

# Public Holidays-Dates VLookup Function

Posted on 2011-09-08
523 Views
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
Question by:ceneiqe
• 3
• 2

LVL 50

Expert Comment

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

barry houdini earned 50 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

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

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

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

### Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.