Lookup value in table (not unique) and check if date is within range, then code from table

[jasonmcasey]

Hi

I am trying to figure out if this is possible. I have attached an example spreadsheet.

I have made the Holiday Tracker on the attached. It can change to display any month and also includes Public/Bank Holidays. What I would like to do is if the day is not a public holiday, lookup by name to see if person has a holiday already booked on the selected date. These are specified in another table on the Calculations sheet. If they have a holiday, then I want to return the value from the Code column of the holidays table on the Calculations sheet.

So on my attached spreadsheet, I want to have the code HH for dates between 30/03/15 and 10/04/15 that are not Public Holidays. HH again for 01/03/15 - 02/03/15, SI for 03/03/15 and AA for 05/05/15, all on the Holiday Tracker.

Is this possible with one formula in a cell?

[Zekraoui]

Not clear to me! was thinking of "=VLOOKUP([vacation day];$S:$W;4;FALSE)" but ??!