Vlookup issues

  • Thread starter Thread starter hager
  • Start date Start date
H

hager

Hello all,

I current have a spreadsheet that does a vlookup from a table that
contains the following:

VD Vacation Day 1
PD Personal Day 1
VH Half Day Vacation Day 0.5
PH Half Day Personal Day 0.5
HO Home Office 0
AS Assignment 0

I have worksheets for every month that that has the days of the month
vertically. The user will enter one of the codes in the table
according to the type of day taken.

I have a vlookup on a totals sheet:

=VLOOKUP(January2008!C5,Codes,3,FALSE)

This works fine and gives me 1 for the output when VD is entered.

Now the issue, I want to total a row based on the value in the table,
that is if the codes for three seperate dates are, PD, PH, VD the
total should be 2.5

I tried:

=VLOOKUP(January2008!C5:AB5,Codes,3,FALSE)

but it errors out, I know I can do =VLOOKUP(January2008!C5,Codes,
3,FALSE) +VLOOKUP(January2008!D5,Codes,3,FALSE)+.......

but that would take be a huge formula.

Is there a way I can sum up the vlookup without a huge formula.

Thanks to all.

Michael Hager
 
Michael,

Try

=COUNTIF(January2008!C5:AB5,"VD") +
COUNTIF(January2008!C5:AB5,"PD") +
COUNTIF(January2008!C5:AB5,"VH")/2 +
COUNTIF(January2008!C5:AB5,"PH")/2

If you want to keep it flexible, then use this:

=COUNTIF(January2008!C5:AB5,"VD")*VLOOKUP("VD",Codes,3,False)+
COUNTIF(January2008!C5:AB5,"PD")*VLOOKUP("PD",Codes,3,False) +
COUNTIF(January2008!C5:AB5,"VH")*VLOOKUP("VH",Codes,3,False) +
COUNTIF(January2008!C5:AB5,"PH")*VLOOKUP("PH",Codes,3,False)
 
Michael,

Try

=COUNTIF(January2008!C5:AB5,"VD") +
COUNTIF(January2008!C5:AB5,"PD") +
COUNTIF(January2008!C5:AB5,"VH")/2 +
COUNTIF(January2008!C5:AB5,"PH")/2

If you want to keep it flexible, then use this:

=COUNTIF(January2008!C5:AB5,"VD")*VLOOKUP("VD",Codes,3,False)+
COUNTIF(January2008!C5:AB5,"PD")*VLOOKUP("PD",Codes,3,False) +
COUNTIF(January2008!C5:AB5,"VH")*VLOOKUP("VH",Codes,3,False) +
COUNTIF(January2008!C5:AB5,"PH")*VLOOKUP("PH",Codes,3,False)
Click to expand...



Works like a charm...thanks much....

Michael Hager
 
Back
Top