VLookUP formula help!

D

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~
 
D

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
 
S

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"
 
M

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
 
D

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top