IF Function formula (I think?)

[Rocktaxi]

Hello all. I have a formula that I trying to write to go along with an
employee timesheet that is using Excel to track.

In Column A I have the days of the week listed

In Column B, I have a drop down list of possible values. I have them
labeled as the PayCodes. "Regular Hours", "Vacation", "Suspension",
"FMLA Unpaid", "Military Leave", Unpaid Personal Leave" ... and a few
others.

In Column C, I want to return a value of either "8" or "0" for paid or
unpaid time earned for the day based on the code in column B.

So basically, it's a simple, "If column B = Regular Hours, show a
value of 8 but if column B = Funeral Leave, show a value of 0.

I have a total of 13 different "Pay types" that I have listed in two
different ranges and I've tried naming those ranges and referencing
each, i.e, IF(B1="Unpaid time off categories",8,0) but the simplest of
formula's does not seem to work. I've even tried super complex
formulas that are a mile long listing out each individual pay category
and they don't work either.

Of course, if I just do {IF (B1="Vacation",8,0)} Then that will return
my value of 8 but then if I change the drop down to any of the other
12 categories, I still get 8 returned so that formula is not correct
either.

Any thoughts from anyone?

 

[cm]

i would use a vlookup from a small table containing the paytypes and
corresponding values.
assuming the table of 12 paycodes in columns g and h: (paycode and value)
your formula in column c would be =vlookup(b1,g1:h12,2,false)
this should return the 8 or 0 or whatever corresponds to the value you chose
in column b.

 

[Bernie Deitrick]

If you have two named ranges, you could use something like

=IF(ISERROR(MATCH(B1,UnpaidTimeOffCategories,False)),8,0)

Here I am assuming that the range with the valid unpaid time off categories is named
UnpaidTimeOffCategories.

HTH,
Bernie
MS Excel MVP

 

[ryguy7272]

I believe this will work for you:
=LOOKUP(A1,{"Regular Hours","Funeral Leave"},{8,0})

Of course, that's just a small sample. You have to add in the rest by
yourself.

HTH,
Ryan---

 

[Rocktaxi]

If you have two named ranges, you could use something like

=IF(ISERROR(MATCH(B1,UnpaidTimeOffCategories,False)),8,0)

Here I am assuming that the range with the valid unpaid time off categories is named
UnpaidTimeOffCategories.

HTH,
Bernie
MS Excel MVP













- Show quoted text -

Thanks Bernie! Your formula worked! (Oh, and thanks everyone else too,
I tried all of yours, but for some reason this solution did work!