Listing Options in IF formula

I've looked at the forums and tried as many variations in the formula as I can think of, so I need some help. 

My two columns (both drop downs):

Payee Name

Payee Position *(location of formula)

I'm trying to get Payee Position to automate the position title for each payee. Here's what I've got to work for one position title:

IF(OR([Payee Name]2 = "John Smith", [Payee Name]2 = "Jane Doe", [Payee Name]2 = "Sally Sue"), "Benefit Strategist")   yes

But I've got other Payee Names that are not just Benefit Strategists...I've also got Account Executives and Production Partners. How would I include those?? I've thought:

IF(OR([Payee Name]2 = "John Smith", [Payee Name]2 = "Jane Doe", [Payee Name]2 = "Sally Sue"), "Benefit Strategist"), IF(OR([Payee Name]2 = "Fred Flinstone", [Payee Name]2 = "Mike Miller", [Payee Name]2 = "Samwise Gamgee"), "Account Executive")  no

IF(OR([Payee Name]2 = "John Smith", [Payee Name]2 = "Jane Doe", [Payee Name]2 = "Sally Sue"), "Benefit Strategist" OR([Payee Name]2 = "Fred Flinstone", [Payee Name]2 = "Mike Miller", [Payee Name]2 = "Samwise Gamgee"), "Account Executive")  no

IF(OR([Payee Name]2 = "John Smith", [Payee Name]2 = "Jane Doe", [Payee Name]2 = "Sally Sue"), "Benefit Strategist"), =IF(OR([Payee Name]2 = "Fred Flinstone", [Payee Name]2 = "Mike Miller", [Payee Name]2 = "Samwise Gamgee"), "Account Executive")  no

None of those have worked, so I am really needing some help!

Thanks a bunch!

-Stephanie

 

 

Tags:

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 09/26/18

    I would recommend building a table with all of the names in one column and the corresponding titles in another. You could then use an INDEX/MATCH. This will add the benefit of flexibility to add or remove names and/or change titles without having to update tedious, long IF statements. A table like below (column names in bold)(Table column names do NOT have a space in them for this example):

     

    Payee Name    Payee Position                  NameTable          TitleTable

     Mike                 Formula                              John                     CEO

     Jane                 Formula                              Mike                     CFO

     Janet                Formula                              Jane                     Pres

     John                 Formula                              Janet                    Intern

     

    Would use a formula like this:

     

    =INDEX(TitleTable:TitleTable, MATCH([Payee Name]@row, NameTable:NameTable, 0))

     

    What this does is looks at the name you have in the the Payee Name column. It will then search the table for that name and input whatever title is next to it.

     

    Much easier and more flexible than convoluted IF statements.

     

    Hope this helps.

     

    P.S.

    You can also build the table on a separate sheet and use cross sheet references to search the table. Then you can have separate sharing permissions for the sheet and the table (limits people "accidentally" messing something up)

  • laughyes Thank you!! This works PERFECTLY! I ended up doing a separate sheet like you said. Thanks so much for the help!

     

    -Stephanie

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 09/26/18

    Excellent! Happy to help. yeslaugh

     

    Although I don't think I ever saw the part where Samwise ended up in Bedrock... wink

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!