I need help with a formula PLEASE I am going crazy

[Mandy]

I am creating a tracker for my work time and I am getting fusterated at
creating this formula, any help would be apreciated:

PTO=P
Unpaid=U
Tardy Occurrence=T
Exhausted all PTO=EP
Specialized PTO=SP
Bereavement=B
Unscheduled PTO=UP
PTO Manager Override=MO
Floating Holiday=MO
Jury Duty=JD
FMLA=FM
NML=NM

These are the words I am working with, I need it to work like this:

if B2="UPAID" (is choosen from the list)THEN RETURN "U"in B3, IF B2="PTO",
THEN RETURN "P" in B3 .....ECT... This has to work all

 

[Don Guillett]

Lookup LOOKUP in the help index

 

[Mandy]

I did look it up, but it only lets me add one at a time, I need it to include
each of them.

 

[Mandy]

Here is a better explaination:

Can you do this formula?

Blank PTO Unpaid Tardy Occurrence Exhausted all PTO P FALSE
FALSE FALSE

I need the formula to populate the answer, so on this first row the answer
would be “P†because the rest are false.

 

[Pete_UK]

The idea is that you set up a 2-column table somewhere with your
codes, like this:

PTO P
Unpaid U
Tardy Occurrence T
Exhausted all PTO EP
Specialized PTO SP
Bereavement B
Unscheduled PTO UP
PTO Manager Override MO
Floating Holiday MO
Jury Duty JD
FMLA FM
NML NM

Suppose this occupies X1:Y12. You can have a drop-down in B2 which
uses the list in X1:X12 as the source, and then in B3 you would have
the formula:

=VLOOKUP(B2,$X$1:$Y$12,2,0)

and it will return the appropriate abbreviation.

Hope this helps.

Pete

 

[Mandy]

Great that worked perfect, how do I make it say O if the return is #N/A

 

[Fred Smith]

Of course. Use:
=if(iserror(VLOOKUP(B2,$X$1:$Y$12,2,0)),0,VLOOKUP(B2,$X$1:$Y$12,2,0))

Regards,
Fred.

 

[Gord Dibben]

=IF(ISNA(VLOOKUP(B2,$X$1:$Y$12,2,0)),0,VLOOKUP(B2,$X$1:$Y$12,2,0))


Gord Dibben MS Excel MVP

 

[Pete_UK]

You shouldn't be able to get #N/A if you use X1:X12 as the source list
in a data validation drop-down in B2, unless B2 is empty, so you could
use:

=IF(B2="","O",VLOOKUP(B2,$X$1:$Y$12,2,0))

If you are not using that source list, then you could use this to
avoid the #N/A:

=IF(ISNA(VLOOKUP(B2,$X$1:$Y$12,2,0)),"O",VLOOKUP(B2,$X$1:$Y$12,2,0))

It does look to me that you are asking for O (upper case o) to be
returned, rather than 0 (zero), but if you want zero to be returned
then replace "O" with 0 in the formula.

Hope this helps.

Pete