VLookUP formula help!

[Denise]

I created a formula to reference a drop-down menu & then based on the
value...get value from the selected Lookup Table (column #2). Here's the
formula:

=IF(L2="Certified 10 Month",VLOOKUP(L3,Ten, 2), IF(L2="All 12
Month",VLOOKUP(L3,StartDate, 2), IF(L2="Non-Certified 10
Mth",VLOOKUP(L3,NonCert, 2), IF(L2="Certified 11 Month",VLOOKUP(L3,Eleven,
2),IF(L2="Haskell (Year-Rnd)",VLOOKUP(L3,Haskell, 2),IF(L2="Certified
9-Month",VLOOKUP(L3,Nine, 2))))

What I ultimately need this formula to do is to search the chosen lookup
table column based on the entered date and count forward from that date the #
of cells with text in them ("Holiday") and then subtract that from the value
it retrieved. I am trying to get an accurate count of days an employee will
work - holidays.

Any help appreciated~

 

[Don Guillett]

I would have to see a lot more info and/or a sample wb but maybe I can help
your basic formula with this idea

=VLOOKUP(L3,if(l2="Certified 10 Month",Ten,if(l2= "All 12
Month",StartDate)),2)
You could even use CHOOSE or LOOKUP instead of the IF

 

[Shane Devenshire]

Hi,

Please show us some sample data in the lookup table and then explain, with
respect to that sample, what you mean by "count forward from that date the #
of cells with text in them ("Holiday") and then subtract that from the value
it retrieved"

 

[mikebres]

If you aren't locked into the table and just want to calculated the number of
workdays you might want to read up on the NETWORKDAYS function. You will
have to have the Analysis ToolPak add-in added.

Mike

 

[Denise]

Thanks-Okay here it is:

Below is a 2-column sampling of the 11-month-employee table (I have a
9,10,11,12, Haskell & NonCert employee table). What I am needing this formula
to do is; If the employee's start date is 25-Feb-09 going forward calculate
(minus holidays/breaks) the # of days the employee will work. I also need
another cell to record (with a different formula of course) the number of
days from that same start date backwards (minus holidays/breaks) the number
of days that person will not work in that range of days. Each of the 6-lookup
tables has a different range of days, holidays and breaks.

13-Feb-09 90
16-Feb-09 Holiday
17-Feb-09 89
18-Feb-09 88
19-Feb-09 87
20-Feb-09 86
23-Feb-09 85
24-Feb-09 84
25-Feb-09 83
26-Feb-09 82
27-Feb-09 81
02-Mar-09 Holiday
03-Mar-09 80
04-Mar-09 79
05-Mar-09 78
06-Mar-09 77
09-Mar-09 76
10-Mar-09 75
11-Mar-09 74
12-Mar-09 73
13-Mar-09 72
16-Mar-09 71
17-Mar-09 70
18-Mar-09 69
19-Mar-09 68
20-Mar-09 67
23-Mar-09 Break
24-Mar-09 Break
25-Mar-09 Break
26-Mar-09 Break
27-Mar-09 Break
30-Mar-09 66
31-Mar-09 65
01-Apr-09 64
02-Apr-09 63
03-Apr-09 62
06-Apr-09 61
07-Apr-09 60
08-Apr-09 59
09-Apr-09 58
10-Apr-09 Holiday
13-Apr-09 57
14-Apr-09 56

 

[Denise]

Here is my little calculator:

Select Range: Certified 10 Month<<<This is a drop down box to choose the
type of employee I am calculating for.
Enter Start Date: 8/7/2008
Days Worked: 200
Minus Missed Days:







Total Work Days Possible(-holidays/breaks)
ALL 12 Month = 245
Certified 10 Month = 209
Certified 11 Month = 229
Certified 9-Month = 172
Haskell (Year-Rnd)= 212
Non-Certified 10 Mth = 209

Add Snow Days: 1
Minus Holiday/Break 16