# VLookup and IF statemwnts

Microsoft Excel 2003 (full product)
August 24, 2010 at 08:28:52
Specs: Windows XP
 I have a list of dates when people are on holiday, e.g. Leave Date - Return Date, and I want to see if these dates encompas the date in a specific sell. i.e. is 'A1' >=Leave Date and <=Return Date.=IF(AND(B1>=\$A\$11,B1<=\$B\$11),"Yes","No") This works fine when I am checking one set of dates, but is there a way of checking a range of dates in one go?

See More: VLookup and IF statemwnts

#1
August 24, 2010 at 10:43:44
 What do you mean by a "range of dates"?The example you gave above is prefectly clear. If you could provide as clear an example of what you are really trying to do, we might be able to help.

Report •

#2
August 25, 2010 at 00:43:45
 I have two lists of dates, in Column A I have Start Dates and Column B are Return Dates. I need the 'AND' statement to see if any date in Column A is <= B1 and if any date in column B is >= B1.Hope this is a bit clearer. Thanks in advance for your help.

Report •

#3
August 25, 2010 at 07:31:43
 re: if any date in Column A is <= B1 and if any date in column B is >= B1Are you sure that what you want?This will give you that answer:=IF(AND(B1>=MIN(A2:A4),B1<=MAX(B2:B4)),"Yes","No")If you really want to compare the date in B1 to each pair of dates in columns A & B, that's a different story.

Report •

Related Solutions

#4
August 25, 2010 at 07:56:31
 Thanks for the quick reply but I dont think I was clear enough.I do need to comare if the Date in B1 is included to the pair of dates, (A5-B5, A6-B6 etc,where B1 = 25/08/2010and A5 = 01/08/2010 - B5 = 10/08/2010A6 = 20/08/2010 - B6 = 27/08/2010We currently book our annual leave via a paper solution and I approve it for 5 staff and update a Wall Chart Calender of when each member of staff is on holiday. Due to changes at work I now have 30+ members of staff to do this for and would like to make it a little more automated so they can update their individual spredsheet and that will update the calender in Excel (I created a calender on a spreadsheet with the dates on Row 1 and Staffs names in Column A)So the Calender will then look at all the spreadsheets and if the dates are inclusive of the time they have requested off they will have a 'Yes' in (I will change this to colours with 'Conditional Formatting' to make it easier to look at in a glance) this way I can see at a glance how many staff are off work on a perticular date.Thanks

Report •

#5
August 25, 2010 at 07:59:03
 I guess I should have posted the answer to the "different story".To compare the date in B1 to each pair of dates in columns A & B, try this:=IF(SUMPRODUCT((B1>=(\$A\$2:\$A\$4))*(B1<=(\$B\$2:\$B\$4))),"Yes","No")

Report •

#6
August 25, 2010 at 08:08:33
 At first glance that looks like its sorted the problem.You have been a great help. Thankyou very much.Craig

Report •

#7
August 25, 2010 at 08:16:28
 Glad to have been of assistance.

Report •